Home Uncategorized sp_whoisactive and Azure SQL Database

    sp_whoisactive and Azure SQL Database

    1264
    18

    I’ve received a number of requests over the past couple of years for an Azure-specific version of sp_whoisactive. Given the combination of a lack of interesting DMVs available in Azure and my general lack of interest in all things Azure related, creating a modified version of the proc has not exactly been a top priority.

    Luckily, now I don’t have to. Microsoft made a number of enhancements to the newest version of Azure SQL Database–v12–including adding all of the DMVs. Even the deprecated ones!

    I just tested sp_whoisactive in a new instance, and it appears to work perfectly!

    The only caveat: The proc has a reference to a couple of views in MSDB, which are not available in Azure. (Makes sense, since you don’t have access to Agent and other msdb-type features in Azure.) To get it to compile I had to comment out those lines: All the code between lines 4601 and 4635 of the sp_whoisactive v11.11 script.

    I’ve attached a version of the script to this blog post that has the lines commented out. I’m not sure if there are other issues hiding somewhere, but so far (very thorough testing for all of 5 minutes in an almost completely empty database) everything seems to be fine.

    In other words, there are probably some issues–if you test and see anything awry, please post a comment here and I’ll work on it.

    Enjoy!

    File Attachment: who_is_active_v11_111.zip

    Previous articleRe-Inventing the Recursive CTE
    Next articleSQLQueryStress: The Source Code
    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.

    18 COMMENTS

    1. It appears that Azure has removed sys.dm_os_sys_info I have tried to merely remove that code however, it results in the following error message:
      Msg 535, Level 16, State 0, Line 336
      The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

    2. @Komron
      Ugh. I’ll work on it soon. Not sure why they’d remove that and leave a bunch of other OS-level DMVs that all require that one to make any sense. *sigh*
      –Adam

    3. In the nick of time – thanks!
      Just bumped into the "Deploy Database to Microsoft Azure SQL Database…" roadblock on your script and sp_blitz.  Error due to external references.  Had to drop them with deep sadness.  So glad there’s a near-kosher version.

    4. Yes, following up on progress as well (similar error below).
      Thanks! ~Justin
      Msg 208, Level 16, State 1, Line 331
      Invalid object name ‘sys.dm_os_sys_info’.

    5. If anyone runs into a viable alternative to sp_whoisactive for Azure, please post it here. I will do the same if I find one. Thanks!

    6. The one Adam just posted works without issue.
      By the way… there are no real world alternatives to "whoisactive."
      It’s the greatest free tool on the market. Period.
      I use it every single day.

    7. Hi, I am facing following error by executing the script in azure database. Database is as a service. How to execute it over IaaS ?
      Msg 262, Level 14, State 18, Procedure sp_WhoIsActive, Line 1 [Batch Start Line 0]
      CREATE PROCEDURE permission denied in database ‘master’.
      Msg 208, Level 16, State 6, Procedure sp_WhoIsActive, Line 18 [Batch Start Line 3]
      Invalid object name ‘dbo.sp_WhoIsActive’.

    8. @Nauman:
      You have no master database on Azure SQL Database, so don’t try to create it in master. You have to create it in your own database.
      BTW Azure SQL Database is technically PaaS, not IaaS 🙂
      –Adam

    Comments are closed.