Question about tempdb contention - 07-19-2005 , 03:52 PM
I was reading
regarding tempdb contention. It states that there may be an issue with
tempdb contention if the waitresource column of sysprocesses is "2:1:1"
I see several SPIDs on my server with those values for waitresource but
the status is always 'sleeping' and the waittime is 0. So does that
mean that I may have a tempdb contention issue or not?
Re: Question about tempdb contention - 07-19-2005 , 04:03 PM
Are you seeing any performance degradation? Are you creating temporary
tables or table variables inside your highly active stored procedures? If
so, you might want to rewrite the stored procedure to avoid temporary object
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
<pshroads (AT) gmail (DOT) com> wrote
Re: Question about tempdb contention - 07-19-2005 , 04:08 PM
I'm not seeing degradation per se. But we do have performance issues
that we are addressing wit the usualy methods (query tuning, increasing
i/o capacity, etc.) but I'm trying to determine if tempdb is a
bottleneck based on what I'm seeing in sysprocesses.
Re: Question about tempdb contention - 07-19-2005 , 05:14 PM
pshroads (AT) gmail (DOT) com wrote in news:1121807326.757305.10400
SQL Server and montior it's growth.
The nature of tempdb can cause performance issues. Consider strategies
that can be implemented for tempdb.
See System Databases and Data and Optimising Tempdb Performance in BOL.
Re: Question about tempdb contention - 07-20-2005 , 11:28 AM
I appreciate your help but I am trying to determine specifically
whether or not what I am seeing in sysprocesses is indicative of the
tempdb contention issue mentioned in the Knowledge Base article.
Meaning if I see a waitresource value of "2:1:1" or "2:1:3" but with
a status of 'sleeping' and a waittime of 0 should I be concerned about
teempdb contention? Or does the fact that the waitime is 0 and the
status is 'sleeping' mean that there is no tempdb contention?