Home Uncategorized sp_whoisactive for Azure SQL Database – Attempt #2

sp_whoisactive for Azure SQL Database – Attempt #2

9

Over a year ago now I announced a rough port of sp_whoisactive to Azure SQL Database. This worked thanks to Microsoft finally aligning the Azure DMVs to the box product DMVs…until Microsoft decided to rip out one of the key DMVs, sys.dm_os_sys_info and its ms_ticks column. This column is the key to unlocking various metrics in some of the other SQLOS DMVs.

I tried to get my contacts at Microsoft to listen to me about restoring it, but my efforts were met with nothing but silence and lots of wasted time.

So I set about looking for a workaround. This week I think I’ve finally managed to get something working that approximates the number I need from that view, ms_ticks.

Attached is sp_whoisactive v11.112 — Azure Special Edition v2. Please give it a shot, and I am especially interested in feedback if you use the @get_task_info = 2 option when running sp_whoisactive. That is the main use case that’s impacted by the lack of ms_ticks information and my attempt at a workaround.

Thanks for your patience as I worked this out! Enjoy.

File Attachment: sp_whoisactive_v11_112.zip

SHARE
Previous articleSQLQueryStress: The Source Code
Next articleTemporal Tables: Connect Item Round Up
Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

9 COMMENTS

  1. Thanks for the update Adam!  Are you running this stored procedure within individual databases because I’m unable to install it within the master database due to the following error ‘CREATE PROCEDURE permission denied in database ‘master’

  2. Thanks for the update Adam!  Are you running this stored procedure within individual databases because I’m unable to install it within the master database due to the following error ‘CREATE PROCEDURE permission denied in database ‘master’

  3. Is there a version of sp_whoisactive that would support Azure SQL Data Warehouse edition? We were just trying to build this version (V11.112) on
    ProductVersion  ProductLevel    Edition    EngineEdition
    10.0.9156.4     RTM  SQL Azure  6
    But we got this error:
    Msg 103010, Level 16, State 1, Line 4
    Parse error at line: 531, column: 36: Incorrect syntax near ‘r’.
    It points to the line about "CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t". I was wondering if anyone runs into this issue as well. Thanks!

  4. @Sharon
    No, sorry. SQL DW does not currently support APPLY (as you’ve seen there) and even if it did it doesn’t have a DMV to get the SQL text, nor a DMV to get a query plan.
    It looks like you can get some info by querying sys.dm_exec_distributed_sql_requests, but the DMVs in SQL DW appear to be more or less on the level of what we had back in the SQL Server 2000 days, so best of luck!
    –Adam

LEAVE A REPLY

Please enter your comment!
Please enter your name here