Daniel Tikvicki

How to monitor the SQL Server tempdb database

July 6, 2017 by

When it comes to the monitoring of SQL Server system databases, the tempdb database is one of the most important for consideration, since it holds most of the internally created objects.

Beside some unique characteristics of the tempdb database itself (like version store, e.g.), which will be addressed later separately, its performance and space usage are crucial areas for monitoring (and tuning, as well), because the tempdb is the most active database and common resource in a SQL Server environment.

Generally, configuration and performance of the tempdb database depend heavily on the environment itself (physical resources), workload (amount of parallel operations like creation of temporary objects, e.g.) and applications which use SQL Server resources.

This article will focus only on basic tempdb monitoring techniques, regarding the tempdb disk space usage by its database files, along with some specific information about the database itself (creation date and recovery model), and review the space usage of tempdb specific elements like user objects, internal objects and version store.

Monitor tempdb database files and gather other information

To monitor the SQL Server tempdb database statistics, you can execute the following query. The first part of the query reveals the currently allocated size of data file, log file and space used by log file:


The second part shows exactly when the tempdb was created, and which recovery model it is utilizing

To get the total database size without details, use this query:


Since SQL Server automatically creates the tempdb database from scratch on every system starting, and the fact that its default initial data file size is 8 MB (unless it is configured and tweaked differently per user’s needs), it is easy to review and monitor database files statistics by using the query above. In this case, the default initial size value is set, and the result shows that database expanded by a factor of five times, and this total database size points on light to medium activity in a longer time span of a specified SQL server where tempdb resides.

If needed, as for any other regular database, users can monitor indexes contained in the tempdb database.

Monitor the specific tempdb objects space usage

These objects are:

  • database free space,

and three elements, unique to tempdb:

  • internal objects – created by SQL Server to process queries (online index rebuild, system tables and view retrieval, hash join etc.)
  • user objects – created by users (temporary tables, table variables, etc.)
  • version store – archive of data pages, necessary for supporting applications which use row versioning feature in SQL Server (triggers, e.g.)

Use the following query to obtain information on space usage by specific tempdb objects:


As previously mentioned, the tempdb used for the reference in this article is placed on SQL Server with generally light activity, and currently, none of the row versioning functionalities are used (Version store (KB) column).

Monitor temporary tables space usage

Additionally, if a large amount of data is stored in temporary tables due to the higher SQL server activity, check the number of rows, and used/reserved space of each of temporary tables which had been created in a specific database:


In this case, we used custom Test database. Temporary objects marked above in the screenshot are user-created tables.

Note that all temporary objects will be removed on SQL Server service or machine restart, and information on them can be retrieved only during the active user session.

Daniel Tikvicki
Monitoring

About Daniel Tikvicki

Daniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience. Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.

168 Views