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
Thanks so much for this! Not being able to run sp_whoisactive in Azure was killing me, so glad that you got it working.
Great!!! Thanks for the efforts, Adam. I have a bunch of scenarios in which I’ll plan to use it.
Thanks Adam, your efforts are much appreciated and this will get a lot of use. You are a rock star!
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’
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’
@PMF
Yes, sorry, I should have taken that out for the Azure version.
–Adam
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!
@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
Adam, Thank you for your prompt reply!