Zielyn's Sql Blog

This is for SQL Server 2005 and higher.

Whenever I need to get the queries that are currently running on the system, I tend to run this query first.

select
    r.session_id,
    r.start_time,
    r.status,
    r.command,
    db_name(r.database_id) as db,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.percent_complete,
    r.estimated_completion_time,
    r.cpu_time,
    r.total_elapsed_time,
    r.scheduler_id,
    r.reads,
    r.writes,
    r.logical_reads,
    r.row_count,
    r.granted_query_memory,
    case r.statement_end_offset
    when -1 then NULL
    else object_name(s2.objectid, s2.dbid)
    end,
    case r.statement_end_offset
    when -1 then s2.text
    else substring(s2.text, r.statement_start_offset/2, (r.statement_end_offset/2) - (r.statement_start_offset/2))
    end,
    s3.query_plan
from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(r.sql_handle) as s2
    cross apply sys.dm_exec_query_plan (r.plan_handle) as s3
where r.status <> 'background'
    and r.command <> 'task manager'
    and r.session_id <> @@SPID
    and r.database_id <> db_id('msdb')
order by r.cpu_time desc
        

It has a little more information than sp_who2 and gives you a rough idea of what is running at the time you execute this script. Running this script multiple times is probably going to return different results every time besides when nothing is running on the server. It’s light-weight and should not take longer than a second. The only time this script should be longer is when you have a serious problem going on with your system.

Now a lot of useful information is being presented here. Most are self explanatory, like the session_id or ‘Status’. However, this script helps in your initial investigation for blocking spids. The Wait_type/time/resource columns allow you to get an initial idea of the problem, and you can always look a little deeper in the sys.dm_os_waiting_tasks system view to get a better idea of the problem for blocks. Finding block reasons can turn into a whole post in it self, as that is not the only way to diagnose blocking spids. SQL Server provides lots of tools to find the necessary information.

Typically the Percent Complete and Estimated Completion Time are for tasks which provide how along the query is and what it is expected to finish. Usually it will be blank. The best example I can think of that uses those columns is when you are doing a backup for a database or when you are shrinking your database. Unfortunately the estimated completion time is Microsoft time… soo take it with a grain of salt. The percent complete is usually pretty accurate, but can be random on how fast it gets to 100%.

The next few columns are stats on the query you are running. It should be obvious with any of these columns, the higher the number, the worse off you are. The differences between reads and logical reads is the former is for physical HDD reads while the latter is for memory reads. Typically you want more logical reads than physical reads, but as always, the higher the number, the more intensive and a hog of the system resources it is. The highest number I’ve seen for logical reads is in the 100s of billions… That was an interesting day of doom.

Granted Query Memory is a column which shows how much memory has been devoted to this query. Since this is a count of pages for memory, you have to a do a little math to get an idea of how much memory is taken up in KB terms. I didn’t add the calculations to the query since I just know a high number can be doomy, but if you want to know the actually amount in terms of KiloBytes, here is the equation:

    (r.granted_query_memory *8060)/1024 as Granted_Query_Memory

A memory page has 8060 bytes of information after overhead, and dividing it by 1024 gets the number of kilobytes assigned to the memory page.

The next two columns provide some helpful information if they are provided. The first column is the object name (sproc/function) of the outer scope of the script running. The second is the sql script it is currently running to provide all the information. Usually the scope is what single script its running, but some times it pulls back the entire object script for you to figure out where the problem is.

The next column is the Query Plan. This guy is so full of information its great you have the ability to see it Its the actual execution plan the SQL Server Optimizer is using to run the query with. Since it is just an XML data type, there are two ways to view it. When you click it, it behaves differently depending on which version of SQL Server Management Studio you are using. SQL Server Management Studio 2008 and above see it as a SQL Plan and open it as such without any manipulation.

Unfortunately, it is not as easy in SQL Server Management Studio 2005. Upon opening the XML data type, it opens it in the default XML fashion. What you can do to get the visual representation is to save the file on your HDD. When saving, change the file name from XML to ‘.sqlplan’. Then you open the new sqlplan file and you get the graphical representation of the execution plan. This is an extra step for displaying the same information in the XML file, but its a lot smoother in SQL Server Management Studio 2008 and up.

Most of this information is coming from the system view sys.dm_exec_requests, with the sql text and plan coming from the system functions sys.dm_exec_sql_text and sys.dm_exec_query_plan. The where clauses filter out some of the common noise, but play with everything to get a better feel for what you need. This is the script I commonly use when working with a database problem. It’s scope is system wide, so you do not need to worry about being in a specific database.

This isn’t a script that will tell you everything, but gives a great way to start your investigation. I hope it does you well.