8

I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not be happening - how would I detect the source of this contention? TempDB and all other databases are on SAN storage via 10 GB Ethernet. The SAN has 46 10k SAS drives configured in one RAID-60 array. This array is shared with several VMWare servers and an Exchange server or two.

Notification from Idera SQL Diagnostic Manager:

11/8/2012 10:49:00 PM, Tempdb Contention (ms) on MGSQL01 is Critical.

Tempdb latch contention has been detected on MGSQL01. The total wait time detected is 1782 milliseconds. This is an indication that performance is being impacted by contention on allocation maps in tempdb. If this is a regular problem it may be alleviated by following best practices with respect to tempdb file count, size, and IO subsystem.

PFS Wait Time: 1782 ms GAM Wait Time: 0 ms SGAM Wait Time: 0 ms

Tempdb Contention (ms): Current wait time for tempdb allocation maps (GAM, SGAM, and PFS), in milliseconds. This alert may only be raised on instances running SQL 2005 or above.

Improve this question
15
  • 1
    Heya Max, just a couple of quick questions. I had to deal with a similar issue very recently. -Is it pagelatch_SH or pageiolatch_SH? I'm pretty sure it's pagelatch_SH in your case but I just want to be certain. -Have you used extended events to track which queries are accumulating the most PAGELATCH_SH wait times? Can you also please post your top 20 wait stats from SELECT * FROM SYS.DM_OS_WAIT_STATS ORDER BY 3 What's perfmon telling you? Is the TempDB drive in fact experiencing any real IO contention? Spike in MS?
    – Ali Razeghi - AWS
    Nov 9, 2012 at 6:19
  • 2
    Are the waits on a PFS page in a particular file or all of them? Are all of the tempdb files of equal size? Do you know what kind/size of objects are being allocated in tempdb? Are temporary tables being explicitly truncated and dropped by app code? Have you thought about implementing trace flag 1118?
    – Darin Strait
    Nov 9, 2012 at 14:15
  • Hi Max, have a look here sqlblog.com/blogs/adam_machanic/archive/2011/04/21/…
    – user9399
    Nov 12, 2012 at 20:31
  • 2
    @MaxVernon: As a rule of thumb, always run T1118. There really is no reason to run without it
    – Thomas Kejser
    Dec 26, 2013 at 0:41
  • 1
    @maxvernon, I +1 your connect item. Nice one.
    – Edward Dortland
    Jan 2, 2014 at 9:14

1 Answer 1

Reset to default
5

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations. The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points. If you have SQL Server 2008 or SQL Server 2005 and the fix applied and are still encountering TEMPDB contention consider trace flag 1118 for further assistance in resolving the contention.

Improve this answer
1
  • 1
    Could you explain, preferably with an example, how changing from single page shared allocation to dedicated extent allocation will solve PFS contention?
    – Edward Dortland
    Jan 2, 2014 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.