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):

SELECT 
  COALESCE
  (
   QUOTENAME(DB_NAME(t.[dbid])) 
     + '.' + 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)
FROM
   sys.dm_exec_sessions AS s
LEFT OUTER JOIN
   sys.dm_exec_requests AS r
   ON s.session_id = r.session_id
OUTER APPLY
   sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
   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).