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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
--First part of the script SELECT instance_name AS 'Database', [Data File(s) Size (KB)]/1024 AS [Data file (MB)], [Log File(s) Size (KB)]/1024 AS [Log file (MB)], [Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)] FROM (SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)', 'Log File(s) Used Size (KB)') AND instance_name = 'tempdb') AS A PIVOT (MAX(cntr_value) FOR counter_name IN ([Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)])) AS B GO -- --Second part of the script SELECT create_date AS [Creation date], recovery_model_desc [Recovery model] FROM sys.databases WHERE name = 'tempdb' GO |
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:
|
1 2 3 4 |
SELECT SUM(size)/128 AS [Total database size (MB)] FROM tempdb.sys.database_files |
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:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT (SUM(unallocated_extent_page_count)/128) AS [Free space (MB)], SUM(internal_object_reserved_page_count)*8 AS [Internal objects (KB)], SUM(user_object_reserved_page_count)*8 AS [User objects (KB)], SUM(version_store_reserved_page_count)*8 AS [Version store (KB)] FROM sys.dm_db_file_space_usage --database_id '2' represents tempdb WHERE database_id = 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE <database_name> SELECT tb.name AS [Temporary table name], stt.row_count AS [Number of rows], stt.used_page_count * 8 AS [Used space (KB)], stt.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS prt INNER JOIN tempdb.sys.dm_db_partition_stats AS stt ON prt.partition_id = stt.partition_id AND prt.partition_number = stt.partition_number INNER JOIN tempdb.sys.tables AS tb ON stt.object_id = tb.object_id ORDER BY tb.name |
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 is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.
- How to monitor the SQL Server tempdb database - July 6, 2017
- How to monitor total SQL Server indexes size - June 12, 2017
- How to set and use encrypted SQL Server connections - May 8, 2017