DEV Community

Cover image for Identifying Heavy Usage of TempDB In SQLSERVER and Monitoring It
Arvind Toorpu
Arvind Toorpu

Posted on • Edited on

Identifying Heavy Usage of TempDB In SQLSERVER and Monitoring It

Identifying Heavy Usage of TempDB and Monitoring It

Heavy usage of TempDB can occur when queries create excessive temporary objects, large sort operations, or poorly written SQL logic. Monitoring TempDB usage is crucial to identifying the root cause of the issue.


1. Identifying Heavy Usage of TempDB

You can use the Dynamic Management Views (DMVs) to find queries that heavily use TempDB.

Query to Identify Active TempDB Allocations
SELECT
    r.session_id,
    r.command,
    r.status,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_time,
    r.wait_type,
    s.login_name,
    s.host_name,
    t.allocated_extent_page_count * 8 / 1024 AS tempdb_space_used_mb
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
JOIN sys.dm_db_task_space_usage t ON r.request_id = t.request_id
WHERE t.allocated_extent_page_count > 0
ORDER BY tempdb_space_used_mb DESC;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • allocated_extent_page_count: Measures the amount of TempDB space used by a query.
  • Filters out sessions using TempDB and orders them by usage.

Query to Monitor TempDB Usage by Task

This query breaks down TempDB usage at the task level.

SELECT
    session_id,
    request_id,
    internal_objects_alloc_page_count * 8 / 1024 AS internal_objects_mb,
    user_objects_alloc_page_count * 8 / 1024 AS user_objects_mb
FROM sys.dm_db_task_space_usage
WHERE session_id > 50; -- Exclude system sessions
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • internal_objects_alloc_page_count: Memory used for internal TempDB operations (e.g., sorting, hashing).
  • user_objects_alloc_page_count: Memory used for user-defined objects (temporary tables, table variables).

2. Real-Time Monitoring of TempDB Growth

SELECT
    name AS file_name,
    size * 8 / 1024 AS size_mb,
    max_size * 8 / 1024 AS max_size_mb,
    growth * 8 / 1024 AS growth_mb,
    physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
Enter fullscreen mode Exit fullscreen mode

Explanation:
This query checks TempDB file sizes, growth settings, and physical locations.


Best Practices for Sizing and Tuning TempDB

1. Sizing TempDB

Proper sizing of TempDB can prevent performance degradation due to frequent auto-growth events.

  • Scenario: A workload requires ~100GB of TempDB during peak times.

Best Practice:
Pre-size TempDB to 100GB and divide it across multiple data files.

Example:

  USE [master];
  GO
  ALTER DATABASE [tempdb] MODIFY FILE (NAME = 'tempdb_data', SIZE = 20GB);
  ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdb_data2', FILENAME = 'C:\TempDB\tempdb_data2.ndf', SIZE = 20GB);
  ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdb_data3', FILENAME = 'C:\TempDB\tempdb_data3.ndf', SIZE = 20GB);
  ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdb_data4', FILENAME = 'C:\TempDB\tempdb_data4.ndf', SIZE = 20GB);
  GO
Enter fullscreen mode Exit fullscreen mode

2. Configuring Auto-Growth

  • Set Growth in Larger Chunks: Avoid frequent small growth events that can fragment TempDB.

Example:

  ALTER DATABASE [tempdb] MODIFY FILE (NAME = 'tempdb_data', FILEGROWTH = 1GB);
  ALTER DATABASE [tempdb] MODIFY FILE (NAME = 'tempdb_log', FILEGROWTH = 512MB);
Enter fullscreen mode Exit fullscreen mode

3. Balancing TempDB Workload

  • Divide TempDB into Multiple Files: Use multiple equally sized files to reduce contention.

Scenario:
A system with 4 CPUs and high TempDB contention.

Best Practice:
Create 4 equally sized TempDB files.

Example:

  ALTER DATABASE [tempdb] MODIFY FILE (NAME = 'tempdb_data', SIZE = 5GB);
  ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdb_data2', SIZE = 5GB);
  ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdb_data3', SIZE = 5GB);
  ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdb_data4', SIZE = 5GB);
Enter fullscreen mode Exit fullscreen mode
  • Enable Trace Flag 1117 and 1118 (if using older versions of SQL Server): These trace flags ensure uniform growth and prevent mixed extents.

When to Shrink TempDB

Shrinking TempDB is typically not recommended in high-uptime systems, but there are exceptions:

  • Scenario: TempDB has grown excessively due to a one-time operation, and space is urgently needed.

Best Practice:
Shrink TempDB during off-peak hours while ensuring minimal impact.

Steps to Shrink TempDB:

  1. Identify the current size and usage.
  2. Issue the shrink operation.

Example:

  DBCC SHRINKFILE ('tempdb', 10240); -- Shrink TempDB file to 10GB
Enter fullscreen mode Exit fullscreen mode

Note: Shrinking is a temporary solution. Investigate and address the root cause of TempDB growth.


Restart-Free Shrinking in High-Uptime Systems

If a system requires high uptime:

  • Option 1: Shrink unused TempDB files manually.
  • Option 2: Reallocate TempDB space using the ALTER DATABASE command.

Example:

-- Reduce the size of one file without restarting
USE [master];
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = 'tempdb_data2', SIZE = 5GB);
GO
Enter fullscreen mode Exit fullscreen mode

Conclusion

Managing TempDB efficiently requires careful monitoring, proper sizing, and optimization of workloads. Use the provided queries to identify heavy usage, monitor TempDB, and make adjustments as needed. By following best practices, you can minimize contention, avoid unnecessary growth, and ensure high performance and uptime for your SQL Server environment.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay