SSMS Hangs when expanding Views on 1 particular Database

Question

In object explorer, I can normally right click on 'Views' to create 'new view'.
However, in 1 DB, SSMS simply hangs when I r-click on 'views'.

Really no sure where to begin on resolving this.
Only thought I had, was that there are 110,000 tables in the DB, and possibly there is a call to go off and fetch this list (the graphical UI for a new view does have a dialogue listing tables to select from).

Anyone had this before, or ideas on how to get around ?

Thanks

Microsoft SQL Server Management Studio
14.0.17289.0

Answer 1

Hi @bushels ,

Did you use a valid login name?
After Right clicking on the database->properties->files, you can see the file owner.
If it’s the owner’s error, click security->logins->login properties->user Mapping and then you can make some changes.

It may be a timeout problem caused by your large amount of data.
As for clearing cache, use DBCC FREEPROCCACHE to clear the procedure cache and use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.
Get your views:

use [yourdatabse]  
select * from sys.sysobjects where type='v'  

To get the definition and properties of a view and recreate views.
The last method is not recommended and you can try. In the final analysis, you have too much data. You need to manage your big data, otherwise there will be other performance issues.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

First, you are running a very old version of SSMS. I highly suggest you update to a current version. https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

Second, it is likely not SSMS which is hung, but the query it runs on the server being blocked or taking a very long time. Look for the session and find the wait state. All SSMS does is run TSQL commands to get the data and display it on the screen.

Answer 3

I agree with Tom, install a newer version of SSMS from here: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms. (And since it is a new major version, it will be a side-by-side install.)

As Tom says, blocking can be a reason. On my web site you find beta_lockinfo that shows you current activity including blocking. Here you can see if you are being blocked - or SSMS is simply running a query that runs "forever".

If this database has over 100000 tables, I can see that SSMS gets more than a mouthful when it tries to open the view designer. While it is not my business, I will have to say that 100000 tables sounds like something is wrong to me. In a relational database a table is supposed to model a unique entity, and 100000 entities? That's a whole lot. One system I've worked with for many years has around 1800 tables - and I would call that a lot.

Anyway, you don't have to use the view designer, but you can create a view directly in a query window. I would even recommend that over using the view designer, which sometimes frowns on perfectly valid SQL.