Most database folks might know the tempdb database in SQL Server is used for temporary tables, but many folks don’t realize all the other things that use this critical system database.
It’s also used by table variables, cursors, aggregations, joins, and sorts. And by memory spills when you don’t have enough memory. And by integrity checks that you run regularly to check for corruption. And more.
Optimal performance of tempdb is vital, as it is involved constantly with all your database queries. So…is your tempdb configured and running optimally? And if it isn’t, can you tell why?
Here at Straight Path Solutions, we’re big fans of community tools like Adam Machanic’s sp_WhoIsActive, Brent Ozar’s First Responder’s Kit, and Erik Darling’s suite of helpful stored procedures. As database administrators who are constantly looking at new clients and new servers, we wished there was a tool to quickly give an overview of potential issues with tempdb configuration, as well as something to look in depth at current tempdb usage. We didn’t find one, so we made one: sp_CheckTempdb.
We want to emphasize that sp_CheckTempdb is easy to use and it’s completely free; you don’t even need to give us your email address. You can download it today from the Straight Path GitHub repository.
What does sp_CheckTempdb do?
As noted, not only does sp_CheckTempdb allow you to quickly review the configuration of your tempdb files (raise your hand if you’ve ever had SSMS throw an error when you tried to look at the file properties), but it also quickly allows you to identify potential issues with your configuration like misconfigured files, problematic settings, and read/write performance. It also lets you look at what is currently using tempdb if needed for immediate troubleshooting. . You can read more details about this on the sp_CheckTempdb page.
If you don’t feel like clicking over to that page, that’s fine. We’ll just note that the key to this flexibility is the use of several different “Modes”, which each return different result sets.
- @Mode = 0: the potential issues we could find related to configuration and performance.
- @Mode = 1: the current configuration of all tempdb files, as you would see when reviewing the Properties of the Files in SSMS.
- @Mode = 2: 1 summary and 2 detail results sets, showing information about the current usage of data and log files.
- @Mode = 3: a check for metadata or allocation contention in tempdb, as originally noted by Haripriya Naidu.
You can get lots of valuable information from sp_CheckTempdb just by executing with a particular mode selected. In fact, because we’ve found it most useful to look at the general information as well as any potential problems, if you execute sp_CheckTempdb without any @Mode selected, you will get the results of both Modes 1 (the overview) and 0 (the potential issues). We typically look at the results of Modes 0 and 1 to find issues proactively, while we troubleshoot immediate issues with Mode 2 (or if contention is suspected, Mode 3).
But there’s even some customization you can do with this tool.
How do I use the tool to check SQL Server tempdb performance?
As noted, there is flexibility in how sp_CheckTempdb can be used. As with other stored procedures we’ve made like sp_CheckSecurity and sp_CheckBackup, we’ve added useful parameters to help you get results tailored to your preferences.
@Help – the default is 0, but setting this to 1 will return some helpful information about sp_CheckTempdb and its usage in case you aren’t able to read this web page.
@Mode – see the previous few paragraphs to decide which Mode you want to use.
@Size– the default is ‘MB’, which will display all size values in megabytes. If you have a larger tempdb, you can set this to ‘GB’ to display size values in gigabytes.
@UsagePercent – the default for this is 50, which means it will check to see if any data or log files have more than 50% usage. Use this to check for unusually high activity in tempdb, or an open transaction that is causing excessive usage of tempdb.
@AvgReadStallMs – the default is 20, which means it will return information for any tempdb file that has an average read stall value greater than 20 milliseconds. Use this to check for excessive tempdb reads and/or possible storge issues.
@AvgWriteStallMs – the default is 20, which means it will return information for any tempdb file that has an average write stall value greater than 20 milliseconds. Use this to check for excessive tempdb writes and/or possible storge issues.
Note that you are not required to use any of these parameters to execute sp_CheckTempdb.
What are the requirements to use sp_CheckTempdb?
There are two requirements.
- You need to have VIEW SERVER STATE permissions. This tool uses several system tables and DMVs to collect information about your SQL Server tempdb database, but VIEW SERVER STATE permissions will allow you to read all necessary information.
- Your SQL Server instance should be using SQL Server 2014 or higher. If you are using an earlier version, execution of the stored procedure will skip some checks because some of the DMVs used don’t exist in earlier versions.
What if I have any questions or feature requests?
Please report any issues on our GitHub sp_CheckTempdb Issues page.
Final Thoughts
We hope this tool is useful in helping you quickly identify both potential and immediate issues with your tempdb database. We’ve been using it for a while now, and it’s been a great time saver. Checking for tempdb ussyes is just one of the parts of our comprehensive health checks that we offer, which also include checks for issues related to security, availability, reliability, integrity, and performance. Regardless of whether or not you are a Straight Path client, we hope sp_CheckTempdb can help you and SQL Server Database Administrators like you to identify and address any obvious or potential tempdb problems before it’s too late.
Hi Jeff
I have a server where the tool delivers an obviously wrong result.
TempDB has 4 data files, 7 MB each. The Logfile is 5 MB
Message is: Logfile is larger than data file.
On another server sp_BlitzFirst shows file stall of 1ms, sp_CheckTempDB shows 1271ms
Any idea?
Best regards
Guenter
Hey Guenter,
Regarding the log file check, that is a check collectively for all log files vs all data files. Is it possible you have more than 1 log file for tempdb? Here is the logic.
SELECT SUM(CAST([size] AS BIGINT))
FROM sys.master_files
WHERE [database_id] = 2
AND [type] = 1
) > (
SELECT SUM(CAST([size] AS BIGINT))
FROM sys.master_files
WHERE [database_id] = 2
AND [type] = 0
For the stall time, I can’t speak to a discrepancy with sp_Blitz, but I can tell you in sp_CheckTempdb that is pulled directly from sys.dm_io_virtual_stats. Here is the logic for that check.
SELECT
f.[file_id]
, f.[name]
, f.[type_desc]
, CAST(s.io_stall_read_ms / ( 1.0 * s.num_of_reads ) AS INT)
, CAST(s.io_stall_write_ms / ( 1.0 * s.num_of_writes ) AS INT)
FROM sys.dm_io_virtual_file_stats(NULL, NULL) s
INNER JOIN sys.master_files f
ON s.file_id = f.file_id
AND s.database_id = f.database_id
WHERE s.num_of_reads > 0
AND s.num_of_writes > 0
AND s.database_id = 2