Home Uncategorized sp_whoisactive for Azure SQL Database – Attempt #2

    sp_whoisactive for Azure SQL Database – Attempt #2

    2621
    11

    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

    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.

    11 COMMENTS

    1. Thanks so much for this! Not being able to run sp_whoisactive in Azure was killing me, so glad that you got it working.

    2. Thanks Adam, your efforts are much appreciated and this will get a lot of use. You are a rock star!

    3. 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’

    4. 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’

    5. 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!

    6. @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

    7. Hello Adam,
      is it a requirement to run sp_whoisactive extension in ADS from MASTER database? I typically install it to a DBATools database and run it from there but it doesn’t seem to work until I copy it into the MASTER DB. I’m getting “chart cannot be display with the given data” while it’s running from DBATools.

      Thank you

    8. Hi Adam, Hope you are doing good.

      I compiled and run the SP but getting below error after executing it in Azure.

      Warning: The join order has been enforced because a local join hint is used.
      Warning: The join order has been enforced because a local join hint is used.
      Msg 300, Level 14, State 1, Line 38
      VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
      Msg 297, Level 16, State 1, Line 38
      The user does not have permission to perform this action.
      Msg 300, Level 14, State 1, Line 331
      VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
      Msg 297, Level 16, State 1, Line 331
      The user does not have permission to perform this action.
      Some thing that I am missing? Kindly advice.

      Thanks,

    Comments are closed.