| Author |
Question about tempdb contention
|
|
| pshroads@gmail.com 2005-07-19, 8:23 pm |
| I was reading
http://support.microsoft.com/defaul...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
| |
| Narayana Vyas Kondreddi 2005-07-19, 8:23 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
creation.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
<pshroads@gmail.com> wrote in message
news:1121806342.579317.49000@g49g2000cwa.googlegroups.com...
>I was reading
> http://support.microsoft.com/defaul...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
>
| |
| pshroads@gmail.com 2005-07-19, 8:23 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.
Thanks
| |
| JTC ^..^ 2005-07-19, 8:23 pm |
| pshroads@gmail.com wrote in news:1121807326.757305.10400
@f14g2000cwb.googlegroups.com:
> 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
>
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 ^..^
| |
| wei xiao [MSFT] 2005-07-20, 7:23 am |
| Do not apply the fix from that KB article unless you are certain you have a
tempdb issue. You may wish to do some research on "dbcc waitstat" to see if
you can identify the top wait resource in your system.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"JTC ^..^" < dave@(nospam)JazzThe
Cat.co.uk> wrote in message
news:Xns9698EC65562A
0daveJTC@213.123.26.234...
> pshroads@gmail.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 ^..^
| |
| pshroads@gmail.com 2005-07-20, 11:23 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?
Thanks
| |
| wei xiao [MSFT] 2005-07-21, 3:23 am |
| Just looking at the output of sysprocess for the current instance is not
enough.
Please see this updated article:
http://blogs.msdn.com/weix/archive/.../02/250983.aspx
More about FIX: Concurrency enhancements for the tempdb database
Many people are wondering if they should apply this tempdb fix:
http://support.microsoft.com/default.aspx/kb/328551
I recommend that you first decide if tempdb is your major concern.
use DBCC SQLPERF(waitstats). Here is an article but you need to be a
subscriber to read it. it seems you can still download the code:
http://www.windowsitpro.com/SQLServ..._40925.htmlThis is another place that talks about this command:http://support.microsoft.com/defaul...;en-us;271509If you observe a lot of wait on "PAGELATCH_UP", then you c
an apply the fix.This is because Q328551 only reduces this type of contention.There is another way of determining if you have a lot of wait in tempdb: dothe following in a loop, once every second or so:select lastwaittype, waitresource from sysprocesses w
here blocked !=0Based on this sampling method, if you see a high percentage of page "2:1:1"or "2:1:3" in your output, then you have a problem. Apply the fix.--Wei Xiao [MSFT]SQL Server Storage Engine Developmenthttp://blogs.msdn.com/weixThis posting is pr
ovided "AS IS" with no warranties, and confers no rights.<pshroads@gmail.com> wrote in messagenews:11218769
31.074482.86680@o13g2000cwo.googlegroups.com...>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 c
ontention? Or does the fact that the waitime is 0 and the> status is 'sleeping' mean that there is no tempdb contention?>> Thanks>
|
|
|
|