October 2, 2014 at 10:24 am
Hello Everybody,
I have the following setup:
- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred).
- These databases are frequently dropped and restored by an application that uses this SQL Server.
- There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unhelpful:-D
This is the problem I have:
- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.
I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:
use [MyDatabase]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]
To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.
Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.
I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature. Or find some other solution. I've pored over all the options in SSMS, and googled until I was blue in the face, but nobody seems to have a solution to this problem that they cared to share. Perhaps I am missing something? Is there a way to mitigate this behavior somehow?
All helpful answers appreciated.
March 5, 2015 at 6:39 am
Hi, I just found that same issue with profiler, I need to open 4500 databases, and the treeview never finished to open, after a while, I saw that management studio is running that query a lot of times, from A to Z, and it's only at letter C after 15 minutes !!! Wow... we really need to be able top "stop" that query from the option somewhere... Did you find a solution? I juste installed CU6, and it's not better.
March 5, 2015 at 9:11 am
If databases are being regularly dropped and restored, won't readcommitted access to their sys.filegroups view be blocked during those operations?
I'm sure your Profiler trace shows that queries on most databases are only taking a few tens of milliseconds at most.
March 5, 2015 at 11:52 am
@gary, on sql profiler, I see that query executed 4500 times when I open the treeview:
use [(all db from A to Z)]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]
Every line takes between 50 and 75 ms to execute, reads between 150 and 250 rows, and that call is from a loop that takes more time to execute, because I see an average of 100-125ms between each query.
So, it can take 100*4500 ms to open my treeview.
There is officially something wrong with that process, as I can open any other version of SQL in 2 seconds.
These are 3 SQL profiler lines:
SQL:BatchCompleteduse [champiXXXX]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]Microsoft SQL Server Management Studiocpu:32read:229write:0duration:57starts:2015-03-05 13:46:27.757end:2015-03-05 13:46:27.813
SQL:BatchCompleteduse [changeXXXX]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]Microsoft SQL Server Management Studiocpu:15read:167write:0duration:42starts:2015-03-05 13:46:27.863end:2015-03-05 13:46:27.907
SQL:BatchCompleteduse [chaXXXX]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]Microsoft SQL Server Management Studiocpu:16read:2410duration:63starts:2015-03-05 13:46:27.953end:2015-03-05 13:46:28.017
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply