THE SQL Server Blog Spot on the Web

Welcome to - 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.

Analyzing Tempdb Contention (A Month of Activity Monitoring, Part 21 of 30)


Published Thursday, April 21, 2011 11:00 AM by Adam Machanic



Yih Lee said:

I think there is a bug in Sp_whoisActive where the "program_Name" is incorrectly displayed.

For some reason, When I run this

EXEC Perf..sp_WhoIsActive

   @get_additional_info = 1,

   @filter_type = 'Session',

   @filter = '435',

   @get_plans = 1,

   @get_task_info = 1

It is displaying program_name as 'Service Broke queue_id: 2075870462' but the query is running from SSMS. this is repeatable on other servers as well.

April 21, 2011 3:37 PM

Adam Machanic said:

Hi Yih,

Can you share some more information? Is Service Broker running in your environment? And can you try again with the most recent beta:



April 21, 2011 7:35 PM

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

mjswart said:

Hi Adam,

I've noticed that sp_whoisactive uses tempdb itself and when there's tempdb contention I've seen sp_whoisactive take a while. So I've learned to do quick and dirty ad hoc queries from dm_exec_requests...

But does this make sense? That sp_whoisactive can struggle to report on  tempdb contention because it needs to use tempdb itself?

September 25, 2012 9:45 AM

Adam Machanic said:

Hi Michael,

First off, which version are you using? v11.11 was specifically tuned to use less tempdb than prior versions.

Second, unfortunately I had to make a choice between [workspace] memory and tempdb [spills]. I chose the latter, as I consider tempdb to be a much cheaper and more expendable resource than memory. Admittedly, I tuned this using a pretty heavy knob and I didn't do a lot of analysis on whether or not I could balance things out a bit more. I hope to do that for a future version.


September 25, 2012 6:49 PM

Centurian said:

Hello Adam,

Just used your SQLQueryStress utility which helped verify my query detected contention straight away. Thanks a bunch.


May 6, 2013 8:47 AM

andrew said:

When u say equally sized. Do you mean exactly same size? E.g if i have a tempdb.mdf as 4045.50mb, to add another files is it ok to around off to 4045mb. Will server still choosing the first file since it is only 0.5mb bigger? Do i have to restart the mssql service after i have added the extra data files?

October 13, 2014 9:59 PM

Adam Machanic said:


Yes, I mean exactly the same size. I would personally upsize that 4045.5 file to 4050 and then create the new files as 4050.

You do not need to restart the service.


October 14, 2014 12:04 AM

Dennis Post said:

Hi Adam,

I have a script to monitor TempDB contention, but it doesn't include DCM or BCM. I want to include them if they are also indicative to TempDB contention. Are they and do they also have a standard formula like the other 3? Also found Index Allocation Map, do I need to include this as well?

GAM: Page ID = 2 or Page ID % 511232

SGAM: Page ID = 3 or (Page ID – 1) % 511232

PFS: Page ID = 1 or Page ID % 8088

I have searched for this but cannot find it.

Sorry about all the questions. I'll post the final script to make up for it. :)

September 17, 2015 8:53 AM

Adam Machanic said:


This is already built in to sp_whoisactive :-)


September 17, 2015 11:44 AM

Rick said:

Hi Adam,

Are incoming queries stripped across multiple tempdb files or are they round robin allocated individually to each file?  I mean the MS documentation even refers to process of temp file use as size weighted, "round robin" but I've seen some bloggers refer to it as striping, and elaborate with an analogy to RAID0.  I've been looking some time for a concise answer and as of yet I got nothing, is it round robin or stripped?  

I've read a few your posts regarding temp use so I'm pretty sure between GAM/SGAM and your posts I got the answer but I wanted to see what you had to say.

December 29, 2016 5:08 PM

Adam Machanic said:


It's a round-robin based allocation across the least-allocated files. The algorithm is known as "proportional fill." RAID0 is not a good metaphor since we're talking about space allocation, which occurs as needed and is more of a serial process.

I believe this video should describe the topic in depth:


December 30, 2016 11:52 AM
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


Privacy Statement