Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 Nov 9 '12 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 '12 at 14:15
    
Hi Max, have a look here sqlblog.com/blogs/adam_machanic/archive/2011/04/21/… –  user9399 Nov 12 '12 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 '13 at 0:41
1  
@maxvernon, I +1 your connect item. Nice one. –  Edward Dortland Jan 2 at 9:14
show 10 more comments

1 Answer 1

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.

share|improve this answer
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 at 9:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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