March Madness – SQL Azure – sys.dm_exec_requests

Yesterday we looked at how to view the current connections to your instance of SQL Azure. Today we will look at how to see the current requests by examining the sys.dm_exec_requests system view. The following statement will return all current requests that are running against your SQL Azure instance:

SELECT *
FROM sys.dm_exec_requests

Running that statement itself isn’t all that exciting. But you can do a couple of things such as find all blocked requests:

SELECT session_id, status, blocking_session_id,
wait_type, wait_time, wait_resource, transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended'

There are a few things worth noting about this system view in SQL Azure. First is that if you want to see all requests hitting the database then you need to have the VIEW DATABASE STATE permission for your login. Otherwise you will only be able to see the details for your own connection, which will be less exciting than you may realize.

Second, there is a column in the result set for sys.dm_exec_requests that is named ‘percent complete’. This column has gone from being documented poorly, to documented less poorly, and now back to poorly again. The trouble with the SQL Azure documentation is the same as the SQL 2005 documentation; it doesn’t tell you what requests will display a percent completed. And since many of the SQL 2012 commands aren’t supported in SQL Azure my best guess is that you will only see the percent completed for statements that are currently in a ROLLBACK state. I’ll let you poke around on the other columns to see a few other interesting things worth noting as well.

The past three days we have looked at how to see session, connections, and requests. Tomorrow we will start piecing them together to show you how to get some real details out of your SQL Azure instance.