What to do when Management Studio hangs?
June 25th, 20098
What to do when Management Studio hangs?
June 25th, 20098

I have had a few occasions where I have been using Management Studio, and am suddenly and bluntly informed by Windows that I am demanding too much of the application.  Windows is probably right; and it is only partially due to the fact that SSMS can be a resource hog at times, and partially due to the fact that I could have 30 or 40 active query windows at any one time.  So what ends up happening is the app stops responding, and "(Not Responding)" gets painted onto the title bar.  The temptation is usually to just kill the application via Task Manager, but this can be bad for a variety of reasons; most importantly:

  1. Do you really know what active queries are running, and what will happen if you forcibly shut the application down?  There can be a big difference between killing an app with idle query windows, and killing an app that is currently in the middle of various transactions on several servers.
  2. While SSMS has adopted nice Office-esque auto-save and recovery features, how much do you trust them?  I am a little too paranoid to take them for granted.

So, instead of blindly killing the application and exposing myself to these risks, I typically open a new SSMS window, and then try to figure out through that instance of the app which (if any) of my queries are making the original instance hang.  In a lot of cases, it is none of them, but I have used this technique to free up SSMS and make it get out of its (Not Responding) state. First, I run this query (making sure to set results to text using Ctrl+T):

     + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
     + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
   ' + t.[text] + '
    */', '
  -- DBCC INPUTBUFFER(' + RTRIM(s.session_id) + ')'
  ) + '
  -- current task : ' 
  + COALESCE(r.[status] + ',' + r.command, '?') + '
  -- % complete : ' 
  + COALESCE(RTRIM(r.percent_complete), 'N/A') + '
  -- KILL ' + RTRIM(s.session_id)
   sys.dm_exec_sessions AS s
   sys.dm_exec_requests AS r
   ON s.session_id = r.session_id
   sys.dm_exec_sql_text(r.[sql_handle]) AS t
   s.[host_name] = '<my workstation name>'
   AND s.session_id <> @@SPID;

Note the OBJECT_SCHEMA_NAME() function and the database_id parameter for the OBJECT_NAME() function were added in SQL Server 2005 SP2. Hopefully you're there or above, right?  🙂

What this gives me is a list of sessions that are currently connected from my workstation.  I take these results and paste them into the top pane of a query window.  Now I can scroll through and, for each session, it tells me either:

  1. the exact procedure or function that is being executed;
  2. if 1. is unknown (e.g. ad hoc SQL), it gives the entire text from sys.dm_exec_sql_text;
  3. if 1. and 2. are unknown, it provides a DBCC INPUTBUFFER statement that I can use to try and figure it out myself.

In addition, it provides the percent_complete from sys.dm_exec_requests, if the request is active and the statement is one that is measured, and it provides a KILL statement that I can highlight and execute.  After each kill, I bring focus back to the hanging copy of SSMS to see if responsiveness has been restored.  Typically this is quick work, as most of the sessions are inactive and the hang is within SSMS itself.  In fact in almost every case so far, this has been SQLPrompt or the native IntelliSense being frozen due to obtaining or updating metadata.  The bonus here is that KILL only severs the connection, and your query windows remain intact.  So when you get to the culprit you can go in, save your query windows, and then consider closing SSMS (or even rebooting) and starting over again.

There are three obstacles that can reduce the effectiveness of this approach: (a) you can have query windows and Object Explorer / Object Explorer Details sessions established on many remote servers, so it may take several iterative tries to pinpoint the session(s)/server(s) hanging SSMS; (b) if you are like me, and occasionally let the number of query windows get out of hand, it will be a long process; and, (b) the server could be hanging as well, in which case your new instance of SSMS is not going to be able to do anything either.  This is where using a dedicated Administrator Connection (DAC) may come in handy.

In any case, hopefully this approach will help you at some point, should you ever find yourself in this situation.

Over on my company web site today, I wrote about some general ideas for speeding up Management Studio (when it's not hanging).

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

8 Responses

  1. Comment & Question says:

    Hi, I really appreciate it when knowledgeable people share their wisdom.  Thanks.
    SQL 2005
    I have a production server that has a publication of a large database (17GB).  On a second SQL server I have somehow created a local publication of that same database (while trying to set up subscription that failed), the properties of which note only the first server's name.
    Issue:  after the usual daily restore of a copy of the database on server 2, it went into single user mode and read only/stand by.  I have tried to delete the publication from server 2, but SSMS hung.
    then, from server 1 I tried to delete the publication, but the SSMS session hung again.  I know it's not a good idea to kill the application, but was hoping to get a couple of answers with respect to my situation.
    Question:  is it normal to take an extended period of time (more than 30 minutes) to execute the sort of query described?
    Should i wait till it recovers? (my first choice)
    thanks in advance.

  2. AaronBertrand says:

    >> One could also use host_name() instead of inputting the workstation name, IF the new SSMS window is on the same workstation of course
    Sure, but typically you have SSMS running on the client, and you need the SPIDs you have running on the server.

  3. jerryhung says:

    Haven't had to kill SSMS 2008 too often (once in a week?)
    One could also use host_name() instead of inputting the workstation name, IF the new SSMS window is on the same workstation of course

  4. Linchi Shea says:

    I have been killing SQL2008 SSMS from the OS quite often lately on my laptop, which is my main workstation. The experience is much better on a desktop that I use one day per week. I guess there are some environment-related issues.

  5. __Stephen says:

    This is only 2005 experience for me. I only run SSMS on my local laptop and never on the server.  
    I have had the not responding as well and find that if I leave it alone it comes back.  My queries are all getting performed on the various servers I opened.  
    In my case it is always a local resources issue.

  6. cinahcaM madA says:

    Actually, 2008 has only crashed for me a couple of times. 2005 used to crash almost daily. Different environments, though. I suspect a lot of the problems have to do with what else is installed, e.g. A/V packages, etc.

  7. AaronBertrand says:

    See I've only had this case a couple of times, and I don't recall a case where it wasn't a SQLPrompt connection.  The post was actually prompted by an SSMS discussion I had with Linchi earlier in the week.  So I'm not sure what you're doing to crash SSMS so often; I'm a pretty heavy user, so maybe we should compare notes at some point.

  8. cinahcaM madA says:

    I wish the autosave were better. It seems to work for me about 5% of the time that SSMS hangs. In Office, OTOH, it works for me about 99% of the time. Of course, Office crashes a lot less frequently than SSMS so it may not be a fair comparison…