Growth of tempDB in Microsoft SQL server

[#1]

• Introduction
• What is tempDB used for?
• Finding the current tempDB size
• Finding out who/what is using the tempDB
• Unexpected tempDB usage

Original content by Debasish Namata

Introduction

When IdentityIQ is installed with Microsoft SQL Server as the backing database, IdentityIQ requires ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to be enabled. (See Configuring IdentityIQ for use with SQL server mirroring) This is also part of the DDL scripts for SQL Server.

This configuration is also known to cause an increase in tempDB usage. This article discusses how to identify what might be causing the tempDB to grow.

What is tempDB used for?

The tempDB is global resource for all databases on the SQL Server. It is used by SQL Server to:

• Hold temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.
• Internal objects that are created by the database engine. These include:
  • Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
  • Work files for hash join or hash aggregate operations.
  • Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

(See also: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-...)

IdentityIQ does not directly make use of the tempdb. Any operations done on it are controlled by SQL Server. But as the above usage shows, there may be queries run by IdentityIQ that can cause objects to be created in the Tempdb. The ultimate size depends on the query and the data being queried. Normally these objects are not very large.

Finding the current tempDB size

The tempDB will grow depending on need, and shrink again when the queries that needed the space are completed, but by default the data file on disk will not shrink, so just looking at the data file does not tell you how large the tempdb currently is. To find the current actual size, you can use the following query, for example:

USE tempdb

EXEC sp_spaceused

Finding out who/what is using the tempDB

To see which queries are using up tempDB space, you need to check while the space is actually being used. A query that can be used for that is:

SELECT
t1.session_id, 
(t1.internal_objects_alloc_page_count + task_alloc) as allocated, 
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated,
qt.text as [Batch Text],
qp.query_plan as [Execution Plan]
FROM sys.dm_db_session_space_usage as t1,
    (select session_id, 
        sum(internal_objects_alloc_page_count) as task_alloc,
        sum (internal_objects_dealloc_page_count) as task_dealloc
        FROM sys.dm_db_task_space_usage group by session_id) as t2,
    sys.dm_exec_requests t3 
        cross apply sys.dm_exec_sql_text (t3.sql_handle) as qt
        cross apply sys.dm_exec_query_plan (t3.plan_handle) as qp
WHERE t1.session_id = t2.session_id
AND t1.session_id >50
AND t1.database_id = DB_ID('tempdb')
AND t1.session_id = t3.session_id
ORDER BY allocated DESC;

If the tempDB keeps growing but you are not sure when it grows, it may be best to run this and the earlier query in a scheduled job every hour or even minute, to record the usage.

Unexpected tempDB usage

If you are seeing unusual or unexpected tempdb usage, please work with your Database Administrator to see which queries are causing it.