January 13, 2014 at 12:52 pm
I am having a consistent and very frustrating problem with my Management Studio -- SQL Server 2008 R2, SP2.
For a little while after I open up the program it is fine. Then, usually after right-clicking on a table in the Object Explorer and selecting "Select Top 1000 Rows", it will start to hang if I attempt to execute any more tasks using the Object Explorer -- or even if I just type in a query in a new query window.
The first time it hangs, it may be for just 10 - 15 seconds. But as I go on, it starts hanging for as long as 15 minutes (usually I don't have the patience to wait, but I just did a test to see how long it would take to complete the query).
Ssms.exe starts using 25% of the CPU time, and the memory used increases steadily by 7 - 9 MB per second. Eventually it gets as high as 1.5G. Of course, long before this time I have usually killed the process and opened up Management Studio again. At this point I can work for a little while before it hangs up again. On big projects I am constantly having to close the program and open it up again.
Any ideas??? The tables I am attempting to view, by the way, may be very large, but it also hangs on very small ones with perhaps only 10 records. The size of the table does not seem to be a factor. It does often show "disconnected" in the status bar while it is hanging and increasing in memory, so possibly it is having a problem connecting for some reason.
I should add that the sql server instance I am accessing is local, so this is not a network problem. I have the identical problem with a remote instance.
January 13, 2014 at 11:10 pm
Run a Profiler trace before you start SMSS and see what it's doing
January 14, 2014 at 4:15 am
Instead of doing the top 1000 rows gui exploration of data, what if you just write T-SQL and look at the data that way, does the same thing occur? I've never used the GUI in that way, but I run queries all day, every day, so I'm wondering if maybe it's something to do with that approach to the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2014 at 2:22 pm
It makes no difference -- if I type queries and execute them, I get the same behavior (I just tested it). I guess it's not related to the use of the Object Explorer after all. I usually start with tasks from the Object Explorer, so I was assuming that was what it was. The whole thing has been so confusing and annoying -- I'm usually just trying to get something done and not paying much attention to the exact sequence of things I'm doing.
I'm working on the Sql Profiler experiment ... haven't used that tool in a while. I'll report on the results when I get it going.
January 14, 2014 at 2:42 pm
also, can you tell us if you are sysadmin in the connection that SSMS/ object explorer is using ?
there's a lot of code in SSMS that assumes that you are an admin, and doesn't handle things gracefully when you are not;
i'm thinking this might be one of those situations?
select IS_SRVROLEMEMBER ('sysadmin')
Lowell
January 14, 2014 at 3:47 pm
Good idea, in general, to use trace events, but Profiler the GUI is not the best thing to run against a production system. Best of all, since you're in 2008, capturing query metrics using extended events puts the least amount of load on your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2014 at 4:34 pm
I am sysadmin -- I would certainly hope so, seeing as I am a free-lance developer (at this point) using my own laptop for everything.
"capturing query metrics using extended events puts the least amount of load on your system."
Alas, you are assuming a lot more expertise on my part than I actually have. I am basically a .NET developer who has more depth (in certain tasks) than breadth of Sql Server experience.
What exactly do you mean by extended events, and if you could point me to some literature, that would be wonderful!
January 14, 2014 at 4:39 pm
Do you have any "Addins" installed either in Visual Studio or SSMS? (Not naming specific ones, just wondering if it is something we can eliminate by disabling them)
I have had this occasionally myself, (more often than I would like) but usually after I have been hammering it with lots of large result sets brought back the the results window - have you checked the laptop environment when this happens - maybe have a perfmon running showing memory availability, pages/sec, disk queue length and processor utilisation - just to see if this is a resource issue.
SSMS is pretty hefty once you have run a few queries that return data and could easily be paging its memory...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 14, 2014 at 6:04 pm
katrina92886 (1/14/2014)
I am sysadmin -- I would certainly hope so, seeing as I am a free-lance developer (at this point) using my own laptop for everything."capturing query metrics using extended events puts the least amount of load on your system."
Alas, you are assuming a lot more expertise on my part than I actually have. I am basically a .NET developer who has more depth (in certain tasks) than breadth of Sql Server experience.
What exactly do you mean by extended events, and if you could point me to some literature, that would be wonderful!
Sure thing. Introducing Extended Events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2014 at 6:20 am
The "AddIns" check suggested is a good idea.
I have had occasions where an AddIn significantly slowed or froze Management Studio.
If memory serves me correctly, ApexSql Intellisense was one of them.
January 15, 2014 at 9:13 pm
I have not installed any add-ins to Management Studio.
April 10, 2015 at 11:24 am
I believe I was seeing this issue or one much like it myself.
I could reproduce it every time by running a query with a large result set, then switching the focus to anywhere in the results pane (not neccesarily a cell, anything would do it, like the scrollbar) The query would run, and there would be no hanging if just ran another...
When I debugged it, broke it, and stepped out to the last call in the stack, I was in a DLL installed by HP Simplepass, a fingerprint password utility I didn't realize I had installed.
When I uninstalled that and rebooted, the problem went away.
I believe what was happening is that that utility hooked itself into the standard windows controls in such a way as to run some kind of process for each cell in the results, to see if it was a password field...
If you have something like that installed, try uninstalling it, and/or if you have debugger installed, you can try to identify what is actually running the same way I did.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply