THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

sp_whoisactive and Azure SQL Database

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!

Published Friday, April 10, 2015 11:37 AM by Adam Machanic

Attachment(s): who_is_active_v11_111.sql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nader Sharara said:

Really good job Adam, you are a star :)

April 11, 2015 5:21 PM
 

Komron Nouri said:

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.

July 13, 2015 6:25 PM
 

Adam Machanic said:

@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

July 14, 2015 8:53 AM
 

SAinCA said:

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.

August 24, 2015 5:35 PM
 

Laura Kerr said:

Any progress on this?  

November 27, 2015 10:55 AM
 

Justin Drummonds said:

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'.

February 12, 2016 2:36 PM
 

Bryan said:

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!

February 23, 2016 5:56 PM
 

Adam Machanic said:

@Bryan

You know just what to say to get me moving :-)

Watch this space...

February 24, 2016 1:12 PM
 

Bryan said:

Excellent new Adam. I'll keep my eyes peeled. Thanks!

February 26, 2016 2:49 PM
 

Vin said:

Hi Adam,

Are there any updates to sp_whoisactive which removes the ref to sys.dm_os_sys_info?

June 6, 2016 9:59 AM
 

Vin said:

Hi Adam,

Are there any updates to sp_whoisactive which removes the ref to sys.dm_os_sys_info?

June 6, 2016 9:59 AM
 

Adam Machanic said:

June 6, 2016 2:18 PM
 

Mike West said:

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.

February 2, 2017 12:18 PM
 

Mike West said:

As an aside... if Adam will allow it... i did a PASS session on SPIDS and whoisactive.

https://www.youtube.com/watch?v=rf17jQRcfjI

February 2, 2017 12:23 PM
 

Adam Machanic said:

@Mike

Thanks for the link, will check it out!

--Adam

February 6, 2017 3:49 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Privacy Statement