SQL Server DBA Tips and Tricks

SQL Server has few system databases which was used to store the medata and information related to SQL server which will require to function the SQL server smoothly. Tempdb is one of the important System database.

What is Tempdb?

Tempdb is a system database. It is used to store the temporary objects and intermediate results which created at the time of query executions.

When SQL services stops tempdb gets cleared and when SQL services starts again tempdb always starts as a clean copy.

Best practices for Tempdb

1. Always keep tempdb on fast and dedicated storage.
   Ex. On Azure IaaS server there is drive called as Temporary storage, it is fast dedicated. we can use that drive for tempdb.
2. Use multiple same size dta files to reduce allocation contention.
   Ex. 1 file per CPU core upto 8 files.
3. Pre-size data and log files to avoid frequent autogrowth. Also, keep autogrowth increaments resonable.
   Ex. 512 MB for data files and 256 MB for log files.
4. All databases on the same instance share or use same Tempdb.

Basic but important points about Tempdb

1. Tempdb database is minimally logged.
2. we can not backup tempdb database.
3. If tempdb gets full, queries starts failing or slowdown drastically.

Usage of Tempdb

Tempdb is used to store the user created temporary objects like local temp tables #temp and global temp tables ##temp, table-valued variables and temporary stored procedures.
It is used to perform operations like sorts, hashes, spools, worktables and intermediate result sets when an operation can not fit in a memory.
Data related row versions for features like Read-Committed Snapshot Isolation, Snapshot Isolation, online index rebuilds, MARS and some triggers.
Also, it acts as a temporary storage for certain metadata structures, cursors and some internal objects.

What if Tempdb is getting full?

1. Check why tempdb is not able to grow.
   – Confirm that the tempdb files have autogrowth turned ON.
   – Check and confirm that disks are not full and files are not hitting their maxsize.
   If disks are full, add some space or pre-grow tempdb data and log files to safe size.

2. Identify the queries and sessions which are burning the tempdb space.

3. Take action on culprit.
– If you get long running or runaway query, reconsider killing it carefully.
– If maintainancr job or any other job is the cause, reschedule it outside of peak hours.

4. Once the cause is resolved shrink the tempdb files.

5. If there is emergency then restart the SQL services.

What is Tempdb Contention?

In SQL Server Tempdb contention occurs when multiple sessions and queries complete for access to shared allocation pages like PFS, GAM and SGAM causing latch waits and performance bottlenecks.

When you execute DMV like sys.dm_exec_requests you will see high waits on PAGEIOLATCH_*, PAGELATCH_* or LATCH_*. This occurs especially during heavy temp table or sort operations. Queries slow down under concurrency.

Below are the types of Contention

Allocation Contention

Sessions queue for PFS, GAM, SGAM pages during object creation in tempdb data files.

Metadata Contention

Overloaded access to tempdb system tables(Ex.sys.objects) from many temp table creations.

Solution to Tempdb Contention issue

Add multiple equally sized tempdb data files(1 per logical CPU upto 8, then in multiples of 4) to spread load via round robbin allocation.
For SQL server 2019 and above versions, enable memory optimized tempdb metadata to eliminate metadata issues.

Check all related blogs