TempDB contention:
TempDB plays very crucial in well performing SQL Server engine. These are few simple design decisions , which helps to avoid the performance bottlenecks in the long run.
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.
There are two types of contention that tends to plague tempDB’s, especially when the tempDB is not configured to best practices (multiple, equally sized data files, located on a dedicated, high-speed drive, etc.).
What are allocation pages?
Allocation pages are special pages in the data files that track and mange extent allocations. There are 3 types of allocation pages that can experience contention and bring a server to a slow crawl.
Global Allocation Map (GAM): Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.
Shared Global Allocation Map (SGAM): Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.
Page Free Space (PFS): Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages.
Finding Latch Contention on Allocation Pages You can use the dynamic management view (DMV) sys.dm_os_waiting_tasks to find tasks that are waiting on a resource. Tasks waiting on PageIOLatch or PageLatch wait types are experiencing contention. The resource description points to the page that is experiencing contention, and you can easily parse the resource description to get the page number. Then it’s just a math problem to determine if it is an allocation page.
The Resource Description (sample): The resource description will be in the form of ::. The tempDB is always database ID of 2.
A sample resource description may look like 2:3:18070499. We want to focus on the page ID of 18070499. The formula for determining the page type is as follows: GAM: (Page ID – 2) % 511232 SGAM: (Page ID – 3) % 511232 PFS: (Page ID – 1) % 8088 If one of these formulas equates to 0, then the contention is on the allocation pages.
Query to check for allocation page contention in tempDB.
Select session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
ResourceType = Case
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description,3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description,3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description,3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
Else 'Is Not PFS, GAM, or SGAM page'
End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%'
References:
SQL Central Link:
http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/
Microsoft KB Article on TempDB Contention:
https://support.microsoft.com/en-us/kb/2154845
Loading...