I was reading
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551"
regarding tempdb contention. It states that there may be an issue with
tempdb contention if the waitresource column of sysprocesses is "2:1:1"
or "2:1:3".

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?

Thanks

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
creation.

--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


<pshroads (AT) gmail (DOT) com> wrote

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.

Thanks

pshroads (AT) gmail (DOT) com wrote in news:1121807326.757305.10400
@f14g2000cwb.googlegroups.com:

What is the size of tempdb and how quickly does it grow? Stop and start
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.

--
Regards
JTC ^..^

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?

Thanks