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.

Active Request, Sleeping Session (A Month of Activity Monitoring, Part 8 of 30)

THIS CONTENT HAS BEEN MOVED HERE.

Published Friday, April 8, 2011 11:00 AM by Adam Machanic

Comments

 

Jeff York said:

<time>

 <avg_total>139586</avg_total>

 <avg_min>8166</avg_min>

 <avg_max>44772</avg_max>

 <min_min>0</min_min>

 <max_max>600004882</max_max>

 <total_stats>31387</total_stats>

</time>

April 8, 2011 11:34 AM
 

Chris said:

<time>

 <avg_total>1182174</avg_total>

 <avg_min>20863</avg_min>

 <avg_max>125767</avg_max>

 <min_min>0</min_min>

 <max_max>233555665</max_max>

 <total_stats>35622</total_stats>

</time>

April 8, 2011 11:49 AM
 

Srdjan said:

<time>

 <avg_total>165614</avg_total>

 <avg_min>4068</avg_min>

 <avg_max>11144</avg_max>

 <min_min>4</min_min>

 <max_max>36331573</max_max>

 <total_stats>11014</total_stats>

</time>

April 8, 2011 1:25 PM
 

Sankar Reddy said:

<time>

<avg_total>26534094</avg_total>

<avg_min>48282</avg_min>

<avg_max>379208</avg_max>

<min_min>0</min_min>

<max_max>601375977</max_max>

<total_stats>7134</total_stats>

</time>

<time>

 <avg_total>18814983</avg_total>

 <avg_min>584624</avg_min>

 <avg_max>754379</avg_max>

 <min_min>0</min_min>

 <max_max>941254883</max_max>

 <total_stats>13225</total_stats>

</time>

<time>

 <avg_total>382198</avg_total>

 <avg_min>1251</avg_min>

 <avg_max>34765</avg_max>

 <min_min>0</min_min>

 <max_max>150887696</max_max>

 <total_stats>4874</total_stats>

</time>

<time>

 <avg_total>13825</avg_total>

 <avg_min>768</avg_min>

 <avg_max>12190</avg_max>

 <min_min>0</min_min>

 <max_max>1944336</max_max>

 <total_stats>178</total_stats>

</time>

<time>

 <avg_total>129010</avg_total>

 <avg_min>1662</avg_min>

 <avg_max>87329</avg_max>

 <min_min>0</min_min>

 <max_max>2006836</max_max>

 <total_stats>47</total_stats>

</time>

<time>

 <avg_total>10340820</avg_total>

 <avg_min>0</avg_min>

 <avg_max>2015136</avg_max>

 <min_min>0</min_min>

 <max_max>2015625</max_max>

 <total_stats>2</total_stats>

</time>

<time>

 <avg_total>270290</avg_total>

 <avg_min>3954</avg_min>

 <avg_max>32815</avg_max>

 <min_min>0</min_min>

 <max_max>600723633</max_max>

 <total_stats>42793</total_stats>

</time>

April 8, 2011 2:29 PM
 

Chris said:

<time>

 <avg_total>862925</avg_total>

 <avg_min>2793</avg_min>

 <avg_max>32330</avg_max>

 <min_min>0</min_min>

 <max_max>197649414</max_max>

 <total_stats>9139</total_stats>

</time>

April 8, 2011 4:14 PM
 

Michael said:

Server was restarted yesterday so counts are low:

<time>

 <avg_total>2095945</avg_total>

 <avg_min>1176836</avg_min>

 <avg_max>1333943</avg_max>

 <min_min>0</min_min>

 <max_max>6471953125</max_max>

 <total_stats>15492</total_stats>

</time>

April 8, 2011 5:31 PM
 

namraja said:

<time>

 <avg_total>6869934</avg_total>

 <avg_min>528188</avg_min>

 <avg_max>1831242</avg_max>

 <min_min>3</min_min>

 <max_max>7025659629</max_max>

 <total_stats>12994</total_stats>

</time>

<time>

 <avg_total>5532677</avg_total>

 <avg_min>603139</avg_min>

 <avg_max>792353</avg_max>

 <min_min>0</min_min>

 <max_max>2568274349</max_max>

 <total_stats>57944</total_stats>

</time>

April 9, 2011 2:57 PM
 

George said:

<time>

<avg_total>227330</avg_total>

<avg_min>47312</avg_min>

<avg_max>82609</avg_max>

<min_min>0</min_min>

<max_max>600673829</max_max>

<total_stats>38655</total_stats>

</time>

April 9, 2011 9:44 PM
 

Alvaro said:

<time>

 <avg_total>1792570</avg_total>

 <avg_min>122323</avg_min>

 <avg_max>159202</avg_max>

 <min_min>1</min_min>

 <max_max>1639573662</max_max>

 <total_stats>99944</total_stats>

</time>

April 11, 2011 5:21 AM
 

Fabricio Lima said:

<time>

 <avg_total>9741620</avg_total>

 <avg_min>26870</avg_min>

 <avg_max>414240</avg_max>

 <min_min>1</min_min>

 <max_max>603658341</max_max>

 <total_stats>4663</total_stats>

</time>

April 11, 2011 1:00 PM
 

Calvin said:

<time>

 <avg_total>866008</avg_total>

 <avg_min>10895</avg_min>

 <avg_max>92887</avg_max>

 <min_min>0</min_min>

 <max_max>13201171</max_max>

 <total_stats>591</total_stats>

</time>

<time>

 <avg_total>2767665</avg_total>

 <avg_min>1625</avg_min>

 <avg_max>90274</avg_max>

 <min_min>0</min_min>

 <max_max>599994140</max_max>

 <total_stats>7261</total_stats>

</time>

<time>

 <avg_total>368355456</avg_total>

 <avg_min>2881</avg_min>

 <avg_max>19027</avg_max>

 <min_min>0</min_min>

 <max_max>1768554</max_max>

 <total_stats>161</total_stats>

</time>

April 12, 2011 10:33 AM
 

Clyde Jin said:

<time>

 <avg_total>3020341</avg_total>

 <avg_min>9720</avg_min>

 <avg_max>157467</avg_max>

 <min_min>0</min_min>

 <max_max>7486087179</max_max>

 <total_stats>85928</total_stats>

</time>

April 12, 2011 10:33 AM
 

Patrick said:

<time>

 <avg_total>259316330</avg_total>

 <avg_min>140868</avg_min>

 <avg_max>32967763</avg_max>

 <min_min>0</min_min>

 <max_max>1663169055000</max_max>

 <total_stats>51106</total_stats>

</time>

April 13, 2011 1:18 PM
 

Woodler said:

<time>

 <avg_total>19862</avg_total>

 <avg_min>550</avg_min>

 <avg_max>4759</avg_max>

 <min_min>0</min_min>

 <max_max>405600</max_max>

 <total_stats>12410</total_stats>

</time>

April 14, 2011 5:49 AM
 

Adam Machanic said:

Thanks, all, for taking the time to run the query and for pasting the XML back here.

April 15, 2011 10:47 AM
 

MyChoreBoy said:

<time>

 <avg_total>21367621</avg_total>

 <avg_min>393321</avg_min>

 <avg_max>642318</avg_max>

 <min_min>0</min_min>

 <max_max>721951171</max_max>

 <total_stats>6953</total_stats>

</time>

April 19, 2011 1:31 PM
 

John said:

Thanks for the blog and the tool Adam.  I'm a few days behind, but if you still want more results here is a sampling of mine...kind of all over the board.

<time>

 <avg_total>26474279</avg_total>

 <avg_min>19519832</avg_min>

 <avg_max>21040860</avg_max>

 <min_min>0</min_min>

 <max_max>7501082031</max_max>

 <total_stats>2385</total_stats>

</time>

<time>

 <avg_total>554602</avg_total>

 <avg_min>28025</avg_min>

 <avg_max>96354</avg_max>

 <min_min>0</min_min>

 <max_max>600015625</max_max>

 <total_stats>14287</total_stats>

</time>

<time>

 <avg_total>3372854</avg_total>

 <avg_min>173265</avg_min>

 <avg_max>323275</avg_max>

 <min_min>0</min_min>

 <max_max>1213653320</max_max>

 <total_stats>7463</total_stats>

</time>

<time>

 <avg_total>403215</avg_total>

 <avg_min>107058</avg_min>

 <avg_max>128744</avg_max>

 <min_min>0</min_min>

 <max_max>1377140767</max_max>

 <total_stats>49047</total_stats>

</time>

April 20, 2011 4:14 PM
 

David Howard said:

<time>

 <avg_total>345579</avg_total>

 <avg_min>227361</avg_min>

 <avg_max>258244</avg_max>

 <min_min>0</min_min>

 <max_max>14200316406</max_max>

 <total_stats>69141</total_stats>

</time>

April 21, 2011 2:46 PM
 

Barny said:

<time><avg_total>65137979</avg_total><avg_min>16790002</avg_min><avg_max>23919424</avg_max><min_min>1</min_min><max_max>16592161701</max_max><total_stats>3058</total_stats></time>

April 26, 2011 4:37 AM
 

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM
 

John Stanley said:

<time>

 <avg_total>381860</avg_total>

 <avg_min>6260</avg_min>

 <avg_max>23816</avg_max>

 <min_min>4</min_min>

 <max_max>601124930</max_max>

 <total_stats>58682</total_stats>

</time>

May 17, 2011 9:40 AM
 

Anton said:

<time>

 <avg_total>161106</avg_total>

 <avg_min>16309</avg_min>

 <avg_max>43152</avg_max>

 <min_min>0</min_min>

 <max_max>5181641</max_max>

 <total_stats>1914</total_stats>

</time>

June 15, 2011 5:37 PM
 

reader said:

<time>

 <avg_total>2732887</avg_total>

 <avg_min>11434</avg_min>

 <avg_max>43464</avg_max>

 <min_min>4</min_min>

 <max_max>112866885</max_max>

 <total_stats>35243</total_stats>

</time>

August 5, 2011 1:49 PM
 

Adam W said:

<time>

 <avg_total>17119867</avg_total>

 <avg_min>8973</avg_min>

 <avg_max>321737</avg_max>

 <min_min>3</min_min>

 <max_max>600219818</max_max>

 <total_stats>28339</total_stats>

</time>

November 21, 2011 4:43 PM
 

Nadia said:

<time><avg_total>7129531</avg_total><avg_min>9106</avg_min><avg_max>138603</avg_max><min_min>0</min_min><max_max>603839844</max_max><total_stats>53615</total_stats></time>

February 15, 2012 12:49 PM
 

yazalpizar said:

Prod server:

<time>

 <avg_total>3688008</avg_total>

 <avg_min>19725</avg_min>

 <avg_max>141714</avg_max>

 <min_min>0</min_min>

 <max_max>2114197239</max_max>

 <total_stats>60209</total_stats>

</time>

Test server:

<time>

 <avg_total>1702154</avg_total>

 <avg_min>69230</avg_min>

 <avg_max>308920</avg_max>

 <min_min>0</min_min>

 <max_max>600035156</max_max>

 <total_stats>6462</total_stats>

</time>

March 15, 2013 5:52 AM
 

Tiago said:

Prod Server:

<time>

 <avg_total>5723106</avg_total>

 <avg_min>22759</avg_min>

 <avg_max>122039</avg_max>

 <min_min>2</min_min>

 <max_max>1074079088</max_max>

 <total_stats>31966</total_stats>

</time>

<time>

 <avg_total>3001623</avg_total>

 <avg_min>369102</avg_min>

 <avg_max>1064351</avg_max>

 <min_min>0</min_min>

 <max_max>1441361441</max_max>

 <total_stats>15980</total_stats>

</time>

<time>

 <avg_total>515299</avg_total>

 <avg_min>49183</avg_min>

 <avg_max>75345</avg_max>

 <min_min>2</min_min>

 <max_max>600012033</max_max>

 <total_stats>32961</total_stats>

</time>

April 24, 2013 7:25 AM
 

Nigel Bennington said:

<time>

 <avg_total>5901641</avg_total>

 <avg_min>109583</avg_min>

 <avg_max>125386</avg_max>

 <min_min>6</min_min>

 <max_max>391187292</max_max>

 <total_stats>6258</total_stats>

</time>

October 1, 2015 7:58 AM
 

Phil Barr said:

<time>

 <avg_total>4779471</avg_total>

 <avg_min>3577245</avg_min>

 <avg_max>4038609</avg_max>

 <min_min>5</min_min>

 <max_max>1058245353</max_max>

 <total_stats>5033</total_stats>

</time>

October 2, 2015 8:31 AM
 

Paulo said:

<time>

 <avg_total>16343623</avg_total>

 <avg_min>70359</avg_min>

 <avg_max>96673</avg_max>

 <min_min>0</min_min>

 <max_max>2563259936</max_max>

 <total_stats>249710</total_stats>

</time>

November 2, 2015 7:38 PM
 

Igor Micev said:

<time>

 <avg_total>58531027</avg_total>

 <avg_min>28974</avg_min>

 <avg_max>58913</avg_max>

 <min_min>0</min_min>

 <max_max>362904779</max_max>

 <total_stats>63956</total_stats>

</time>

<time>

 <avg_total>353626</avg_total>

 <avg_min>14590</avg_min>

 <avg_max>35597</avg_max>

 <min_min>0</min_min>

 <max_max>178178877</max_max>

 <total_stats>36997</total_stats>

</time>

<time>

 <avg_total>4685262</avg_total>

 <avg_min>13260</avg_min>

 <avg_max>96995</avg_max>

 <min_min>0</min_min>

 <max_max>292690427</max_max>

 <total_stats>45752</total_stats>

</time>

October 26, 2016 9:36 AM
 

Jay said:

Does whoisactive collect data in two passes in non-delta mode? How long does it wait before it starts the second pass of collection? Thanks.

December 28, 2016 6:38 PM
 

Adam Machanic said:

@Jay

In non-delta mode there is only one pass.

--Adam

December 30, 2016 11:45 AM
 

Jay said:

@Adam

Thanks a lot.

BTW, I tried the procedure and got the following questions:

I was running whoisactive @get_avg_time=1, @get_lock=1, @get_task_info=2;

1) I saw the first duration in days, but the status is runnable (not sleeping). Is the duration supposed to be duration of that request since it was not sleeping?

2) I had not seen a request in status running. is it because at that moment whoisactive is running?

3) I always saw duration (avg) NULL, what does it mean?

4) This is general question. by a request, do we mean a single SQL statement or could be a bunch of statements included in a stored procedure.

Thank you again in advance. I am working on a SQL performance project and I don't like guess work. I like systematic approach. Your procedure looks very promising to me.

-Jay

January 5, 2017 9:51 AM
 

Adam Machanic said:

@Jay

1) Runnable status means that the root task is waiting to be scheduled. It is an active state. If duration is in days, that means that your query has been running (and not sleeping) for days.

2) Based on how the DMVs report the data and the way the time slicing works, it's much more common to see runnable than running. But for the intent of determining active queries they're identical.

3) That indicates that the procedure cache has no aggregate information for your query. You might have some memory pressure issues.

4) A request is generally a single batch.

--Adam

January 5, 2017 10:40 AM
 

Jay said:

@Adam,

Thanks a lot for the quick response.

1) Maybe it was a special case or may be I did not remember it right, but the request was running in minutes, but duration was showing the session level info (in days) instead of request level info

4) Does single batch equal to single statement? I thought a batch could be multiple statements. that means a single stored procedure call is actually considered as one request. Am I right?

-Jay

January 5, 2017 11:38 AM
 

Adam Machanic said:

@Jay

1) There are certain types of queries (usually, but not always, system processes) that don't record proper start times in the DMVs. In those cases the time you see will be the SQL Server startup time.

4) No; a batch can include multiple statements. It can also include multiple stored procedure calls. "Batch" is shorthand for "Batch Request," and that's what "request" means in DMV land. (And, as a result, sp_whoisactive land.)

January 5, 2017 12:03 PM
 

Jay said:

@Adam,

Thank you so much!

-Jay

January 5, 2017 12:07 PM
New Comments to this post are disabled

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