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

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

Yih Lee — Thu, 21 Apr 2011 19:37:37 GMT

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.

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

Adam Machanic — Thu, 21 Apr 2011 23:35:13 GMT

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:

http://sqlblog.com/files/folders/35046/download.aspx

Thanks!

Twenty Nine Days of Activity Monitoring (A Month of Activity Monitoring, Part 30 of 30)

Adam Machanic — Sat, 30 Apr 2011 15:45:18 GMT

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

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

mjswart — Tue, 25 Sep 2012 13:45:58 GMT

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?

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

Adam Machanic — Tue, 25 Sep 2012 22:49:00 GMT

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.

--Adam

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

Centurian — Mon, 06 May 2013 12:47:08 GMT

Hello Adam,

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

:-)