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
Really good job Adam, you are a star 🙂
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.
@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
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.
Any progress on this?
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’.
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!
@Bryan
You know just what to say to get me moving 🙂
Watch this space…
Excellent new Adam. I’ll keep my eyes peeled. Thanks!
Hi Adam,
Are there any updates to sp_whoisactive which removes the ref to sys.dm_os_sys_info?
Hi Adam,
Are there any updates to sp_whoisactive which removes the ref to sys.dm_os_sys_info?
@Vin
Sure do! Check it out here: http://sqlblog.com/blogs/adam_machanic/archive/2016/04/14/sp-whoisactive-for-azure-sql-database-attempt-2.aspx
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.
As an aside… if Adam will allow it… i did a PASS session on SPIDS and whoisactive.
https://www.youtube.com/watch?v=rf17jQRcfjI
@Mike
Thanks for the link, will check it out!
–Adam
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’.
@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