If you run a busy SQL server an experience performance issues, there are several things you can do to diagnose issues.  I’ll ignore the obvious ones for now (ensuring your queries are streamlined and using the SQL Profiler) and will provide you with an additional script to run which will give you a view of the queries running or queued at the time the script is run.

Basic SQL query to find queries running in SQL server

This can be as simple or complex as you want, but to start with, let’s keep it simple and just get a list of the current queries from sys.dm_exec_requests and cross reference sys.dm_exec_sql_text to get the query being run/waiting, as shown below:

This will return a view like the below:

Advanced SQL query to find queries running in SQL server

Now whilst the basic query contains good information, we can do better and get information to help with diagnosing issues. Other information I like to include, is:

  1. Server from which the query originates.
  2. Login usernames used.
  3. Query status.
  4. If a another query is blocking it.
  5. Type of query.
  6. CPU time.
  7. Total elapsed time.
  8. Transaction isolation level.

To do this, we’ll just add an additional join to sys.dm_exec_sessions and include some extra columns, as shown below:

This will return an output as shown below:

The columns should be self-explanatory and you could expand on this by adding more fields, but these are the ones I find most useful and I choose to order the results by the elapsed time (descending) so I can see the queries with the most impact first.

LEAVE A REPLY

Please enter your comment!
Please enter your name here