You observe severe blocking when the SQL Server is experiencing heavy load. When you examine the Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks], you observe that these requests or tasks are waiting for tempdb resources. You will notice that the wait type and wait resource point to LATCH waits on pages in tempdb. These pages might be of the format 2:1:1, 2:1:3, etc.
When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be unresponsive for short periods of time.
There are several steps you can take to improve the concurrency of tempdb:
As a general
rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
Consider implementing the best practice recommendations in the Technet article titled Working with tempdb in SQL Server 2005
If the previous steps do not significantly reduce the allocation contention and the contention is on SGAM pages, implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages. Note that this trace flag affects every database on the instance of SQL Server. For information about how to determine whether the allocation contention is on SGAM pages, see Monitoring contention caused by DML operations
subtopic under Working with tempdb in SQL Server 2005
page on Technet.
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Collapse this tableExpand this table
Rule software||Rule title||Rule description||Product versions against which the rule is evaluated|
|System Center Advisor||SQL Server database configuration: number of tempdb data files might result in blocking||System Center Advisor checks the number of data files configured for the tempdb database. If it is one and your SQL Server uses more than one processor, then this alert if generated. Review the information in this article and add more data files to the tempdb database.||SQL Server 2008 |
SQL Server 2008 R2
SQL rver 2012