Its all about Database: Performance Tuning

Hey guys, In this blog I am going to explain you about TempDB

TempDB is a workhorse of SQL Server performing a number of functions to support both system and internal operations. With this workload TempDB will process a large number of database writes, which requires low-latency, high throughput underlying storage.

What about tempDB Size?

It depends on below factors...

1. Depend on application, how it using tempDB.
2. Server Configuration like total server memory, installed CPUs.
3. Databases Size.
4. Our DB maintenance plan.

Q - What about number of tempDB files?

There should be one TempDB data file for each thread/core/vCPU on the instance with a maximum of 8.

Q - What about location of tempDB files?

The files can be found by querying sys.sysfiles dmv or the file pane on the database properties window.

SELECT * FROM TempDB.sys.sysfiles;

The default logical filenames are tempdev for data and templog for log. They can be found on disk as tempdb.mdf and templog.ldf respectively. TempDB commonly has many data files.

Q - Why tempDB is different from other databases?

Operations within tempdb are minimally logged so that transactions can be rolled back. tempdb is re-created every time SQL Server is

started so that the system always starts with a clean copy of the database.



Q - What is the use of tempDB?

TempDB is used for many operations, such as user-created temporary objects, internal temporary objects and version stores and

certain features like online re-indexing, multiple active record sets (MARS) and others. Since TempDB is shared across all databases

 and all connections in SQL Server, it might become a point of contention if not configured correctly.

 How to check space used in tempDB by different objects.

SELECT COALESCE(T1.session_id, T2.session_id) [session_id] , T1.request_id ,

COALESCE(T1.database_id, T2.database_id) [database_id],

COALESCE(T1.[Total Allocation User Objects], 0)

+ T2.[Total Allocation User Objects] [Total Allocation User Objects] ,

COALESCE(T1.[Net Allocation User Objects], 0)

+ T2.[Net Allocation User Objects] [Net Allocation User Objects] ,

COALESCE(T1.[Total Allocation Internal Objects], 0)

+ T2.[Total Allocation Internal Objects] [Total Allocation Internal Objects] ,

COALESCE(T1.[Net Allocation Internal Objects], 0)

+ T2.[Net Allocation Internal Objects] [Net Allocation Internal Objects] ,

COALESCE(T1.[Total Allocation], 0) + T2.[Total Allocation] [Total Allocation] ,

COALESCE(T1.[Net Allocation], 0) + T2.[Net Allocation] [Net Allocation] ,

COALESCE(T1.[Query Text], T2.[Query Text]) [Query Text]

FROM ( SELECT TS.session_id ,

TS.request_id ,

TS.database_id ,

CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15,

2)) [Total Allocation User Objects] ,

CAST(( TS.user_objects_alloc_page_count

- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,

2)) [Net Allocation User Objects] ,

CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,

2)) [Total Allocation Internal Objects] ,

CAST(( TS.internal_objects_alloc_page_count

- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,

2)) [Net Allocation Internal Objects] ,

CAST(( TS.user_objects_alloc_page_count

+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,

2)) [Total Allocation] ,

CAST(( TS.user_objects_alloc_page_count

+ TS.internal_objects_alloc_page_count

- TS.internal_objects_dealloc_page_count

- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,

2)) [Net Allocation] ,

T.text [Query Text]

FROM sys.dm_db_task_space_usage TS

INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id

AND ER.session_id = TS.session_id

OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T

) T1

RIGHT JOIN ( SELECT SS.session_id ,

SS.database_id ,

CAST(SS.user_objects_alloc_page_count / 128 AS DECIMAL(15,

2)) [Total Allocation User Objects] ,

CAST(( SS.user_objects_alloc_page_count

- SS.user_objects_dealloc_page_count )

/ 128 AS DECIMAL(15, 2)) [Net Allocation User Objects] ,

CAST(SS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,

2)) [Total Allocation Internal Objects] ,

CAST(( SS.internal_objects_alloc_page_count

- SS.internal_objects_dealloc_page_count )

/ 128 AS DECIMAL(15, 2)) [Net Allocation Internal Objects] ,

CAST(( SS.user_objects_alloc_page_count

+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,

2)) [Total Allocation] ,

CAST(( SS.user_objects_alloc_page_count

+ SS.internal_objects_alloc_page_count

- SS.internal_objects_dealloc_page_count

- SS.user_objects_dealloc_page_count )

/ 128 AS DECIMAL(15, 2)) [Net Allocation] ,

T.text [Query Text]

FROM sys.dm_db_session_space_usage SS

LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = SS.session_id

OUTER APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) T

) T2 ON T1.session_id = T2.session_id

Q - Why we need to configure only 8 tempDB data files if we have host on VM?

The following table lists the initial configuration values of the tempdb data and log files in SQL Server. The values are based on the defaults for the model database. The sizes of these files might vary slightly for different editions of SQL Server.

The number of secondary data files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files. Then if contention continues, increase the number of data files by multiples of four until the contention decreases to acceptable levels, or make changes to the workload/code.

Q - What to do if tempDB get full?

· Firstly, we have to check the space of all data files by using below command: -

Tempdb Data File Full:

Use tempdb

sp_spaceused

SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Then find out which transaction is occupying more space in Tempdb and troubleshoot by using below commands.

Check active transactions: -

select * from sys.sysprocesses where dbid = 2

select * from sys.dm_exec_requests where session_id > 50

Check open transactions through:-

DBCC OPENTRAN

If find any open transaction due to Tempdb getting full so contact with the respective team and inform them about the transaction. Please don’t kill any transaction without the approval of application team.

· Try to shrink the Data File of Tempdb (if no OPEN transactions are in progress).

· Run below three commands after approval. Because there is a risk involved with this, it’s mention below with commands.

1. DBCC FREESYSTEMCACHE('ALL'): -

This command will clear all cached objects, including not only internal objects, but also cached query plans.

2. DBCC FREESYSTEMCACHE ('tempdb'): -

This command will clear the cache for tempdb

3. DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables'):-

· This command will clear all the temp table and variables Increase the Size of data file if it’s restricted or Enable auto growth in MB’s.

· If space is not available in the same drive, then add one more NDF file to another drive where we have space. And when issue will resolve remove that NDF file. Because as per Microsoft recommendation our Tempdb data files should be as per number of Processor.

· The last Option is restart the instance after approval. And it will reset the Tempdb size to last stated value in sys.databases.

TempDB Log File Full

· Firstly verify the current size of log file through:-

DBCC sqlperf(logspace)

· Perform Shrink operation on the log file of tempdb for any free space.

· Verify if Log File is awaiting any operation through: -

select name,log_reuse_wait_desc from sys.databases

Then verify the LOG_REUSE_WAIT_DESC and check the reason of the WAIT. Multiple states are possible and respective action should be taken based on the state.

· Find the transaction which is occupying maximum amount in Tempdb and kill that transaction after approval.

Check active transactions: -

select * from sys.sysprocesses where dbid = 2

select * from sys.dm_exec_requests where session_id > 50

Check open transactions through: -

DBCC OPENTRAN

Run below three commands after approval. Because there is a risk involved with this, it’s mention below with commands.

1. DBCC FREESYSTEMCACHE('ALL'): -

This command will clear all cached objects, including not only internal objects, but also cached query plans.

2. DBCC FREESYSTEMCACHE ('tempdb'): -

This command will clear the cache for tempdb

3. DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables'): -

This command will clear all the temp table and variables.

· After free the cache then perform Shrink operation again.

· Strictly in SQL Server 2005 and lower versions we can run below mention command for truncate the log AFTER APROVAL.

· Add another log file in same drive or different drive as per space availability. When issue will resolve then remove that LDF file. Because as per Microsoft log file should not be more than one.

backup log Tempdb to disk=N’d:\Tempdb.trn' WITH TRUNCATE_ONLY

Move the Tempdb files from one location to another, but it requires instance restart (Need Approval)

· The last Option is restarting the instance after Approval.

Its all about Database

Jul 28, 2024

Network actual bandwidth between servers.

Oct 26, 2022

sp_WhoIsActive

Aug 11, 2022

All about tempDB