Monitoring tempdb Transactions and Space usage
As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any adhoc query by the user can eat all the space available to the tempdb database resulting decrease in the performance of other applications running under the same instance. So it is necessary to keep track the usage of tempdb database by various applications and processes and to take necessary actions when the size falls down below the threshold limit. Monitoring tempdb over time will help in determining the optimal size of the tempdb
Use the below query to check the current tempdb size:
The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb
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;
Determining the Amount Space Used by the Version Store
The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
Determining the Longest Running Transaction
If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.
SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;
Tempdb session File usage
sys.dm_db_session_space_usage : Returns the number of pages allocated and deallocated by each session for the database.
sys.dm_exec_sessions: Gives details about the sessions.
SELECT
sys.dm_exec_sessions.session_id AS [SESSION ID],
DB_NAME(database_id) AS [DATABASE Name],
HOST_NAME AS [System Name],
program_name AS [Program Name],
login_name AS [USER Name],
status,
cpu_time AS [CPU TIME (in milisec)],
total_scheduled_time AS [Total Scheduled TIME (in milisec)],
total_elapsed_time AS [Elapsed TIME (in milisec)],
(memory_usage * 8) AS [Memory USAGE (in KB)],
(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)],
(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)],
(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)],
(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)],
CASE is_user_process
WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END AS [SESSION Type], row_count AS [ROW COUNT]
FROM sys.dm_db_session_space_usage
INNER join
sys.dm_exec_sessions
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.
SELECT
transaction_id AS [Transacton ID],
[name] AS [TRANSACTION Name],
transaction_begin_time AS [TRANSACTION BEGIN TIME],
DATEDIFF(mi, transaction_begin_time, GETDATE()) AS [Elapsed TIME (in MIN)],
CASE transaction_type
WHEN 1 THEN 'Read/write'
WHEN 2 THEN 'Read-only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
END AS [TRANSACTION Type],
CASE transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active.'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.'
END AS [TRANSACTION Description]
FROM sys.dm_tran_active_transactions
Long running Queries
sys.dm_exec_requests : Returns information regarding the requests made to the database server.
SELECT
HOST_NAME AS [System Name],
program_name AS [Application Name],
DB_NAME(database_id) AS [DATABASE Name],
USER_NAME(USER_ID) AS [USER Name],
connection_id AS [CONNECTION ID],
sys.dm_exec_requests.session_id AS [CURRENT SESSION ID],
blocking_session_id AS [Blocking SESSION ID],
start_time AS [Request START TIME],
sys.dm_exec_requests.status AS [Status],
command AS [Command Type],
(SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS [Query TEXT],
wait_type AS [Waiting Type],
wait_time AS [Waiting Duration],
wait_resource AS [Waiting FOR Resource],
sys.dm_exec_requests.transaction_id AS [TRANSACTION ID],
percent_complete AS [PERCENT Completed],
estimated_completion_time AS [Estimated COMPLETION TIME (in mili sec)],
sys.dm_exec_requests.cpu_time AS [CPU TIME used (in mili sec)],
(memory_usage * 8) AS [Memory USAGE (in KB)],
sys.dm_exec_requests.total_elapsed_time AS [Elapsed TIME (in mili sec)]
FROM sys.dm_exec_requests
INNER join
sys.dm_exec_sessions
ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
WHERE DB_NAME(database_id) = 'tempdb'