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 for Azure SQL Database - Attempt #2

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. 

Published Thursday, April 14, 2016 3:32 PM by Adam Machanic

Attachment(s): sp_whoisactive_v11_112.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

 

cp said:

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

April 19, 2016 5:51 PM
 

csm said:

Great!!! Thanks for the efforts, Adam. I have a bunch of scenarios in which I'll plan to use it.

April 20, 2016 9:29 AM
 

Bryan said:

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

May 5, 2016 12:22 PM
 

PMF said:

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'

September 29, 2016 10:53 AM
 

PMF said:

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'

September 29, 2016 10:53 AM
 

Adam Machanic said:

@PMF

Yes, sorry, I should have taken that out for the Azure version.

--Adam

September 30, 2016 9:28 AM
 

Sharon Im said:

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!

July 24, 2017 6:52 PM
 

Adam Machanic said:

@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

July 25, 2017 6:34 PM
 

Sharon Im said:

Adam, Thank you for your prompt reply!

July 25, 2017 7:33 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