March Madness – SQL Azure – sys.dm_exec_sql_text

Yesterday we were looking at how to find all the current requests that were hitting your SQL Azure instance. We can take the information returned by that system view and use it with a CROSS APPLY to the sys.dm_exec_sql_text table function. You can input either a SQL handle or a plan handle, and you can get those handles from views such as sys.dm_exec_connections or sys.dm_exec_requests.

For example, this query from yesterday is nice:

SELECT *
FROM sys.dm_exec_requests

But this query would be better:

SELECT *
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)

Or even this one:

SELECT *
FROM sys.dm_exec_connections ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)

Or we could just build upon what we did earlier in the week and have this:

SELECT e.connection_id, s.session_id,
s.login_name, s.last_request_end_time, s.cpu_time, text
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections e
ON s.session_id = e.session_id
CROSS APPLY sys.dm_exec_sql_text(e.most_recent_sql_handle)

Now I can see the statement text for the current connections or requests against my SQL Azure instance right from SSMS. The SQL Azure management Portal will also show you the text of your queries but currently I don’t see any details for the login name. In other words, I could use the SQL Azure portal to tell me what had been executed (which is why we use the sys.dm_exec_sql_text table function) but the portal doesn’t tell me who was executing that statement.

So, until the Management Portal starts to include additional details such as a login name we will want to stick with using T-SQL from SSMS to get back all of the pertinent information we can.

Tomorrow we will keep inching forward into the requests and the queries that are being executed against your SQL Azure instance.