Some Facts about TempDB
The tempdb is a temporary workspace which is normally used for:
• Storage of explicitly created temporary tables.
• Worktables that hold intermediate results created during query processing and sorting.
• Materialized static cursors.
SQL Server records only enough information in the tempdb transaction log to roll back a transaction but not to redo transactions during database recovery. This feature increases the performance of INSERT statements in the tempdb. In addition, there is no need to log information to redo any transactions because the tempdb is re-created every time you restart SQL Server.
Therefore, it does not have any transactions to roll forward or roll back. When SQL Server starts, the tempdb is re-created by using a copy of the model database and is reset to its last configured size.
By default, the tempdb database is configured to autogrow as needed. This database may grow in time to a size larger than desired. A simple restart of SQL Server resets the size of tempdb to its last configured size. The configured size is the last explicit size set with a file size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE statement.
When does the tempdb Log file get Zero Initialized?
It does not get fully Zero initialized at Startup because its log file is not used for recovering database. It just get partly Zero intiliazed.
It gets Zero intilialized when its size is increased.
It gets Zero initialized when its recreated on new drive location.
Shrinking does not include any process of Zero intiliazation.
tempDB contention:
From the outside looking in, tempDB contention may look like any other blocking. 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.).
Lets see more on latch contention on the allocation pages.
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 formula for determining the page type is as follows:
GAM: Page ID = 2 or Page ID % 511232
SGAM: Page ID = 3 or (Page ID – 1) % 511232
PFS: Page ID = 1 or Page ID % 8088
If one of these formulas equates to 0, then the contention is on the allocation pages.
The query
Here is my query to check for allocation page contention on 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:%'
Fixing the problem of contention on this special pages is simple: we need create more tempdb files which are equally sized. SQL Server will automatically balance incoming requests across the multiple files. Since each file maintains its own PFS, GAM, and SGAM information, the contention will be eliminated.
How many Temp db files should we create? Well, that depends on our workload. I take a simple approach: start with a number of files equal to one quarter to one half of the number of logical schedulers. If the contention goes away then fine, otherwise keep increasing the number of files until the contention does go away.
Make sure to keep the Temp Db files equally sized because SQL Server’s algorithm is based on a proportional fill model, which means that if one file is bigger than the others it will be chosen more often.
If you’re seeing PAGELATCH waits on tempdb, then you can mitigate it using trace flag 1118 (fully documented in KB 328551) and creating extra tempdb data files and this trace flag still required in SQL 2005 and 2008