Trace flag 1118
Issue: Trace flag 1118 is not enabled globally. This trace flag is recommended for SQL Server 2014 and earlier.
Problem: Enabling trace flag 1118 tells SQL Server to avoid mixed extents by allocating each 64 KB extent to a single object. Doing this results in slightly more data pages, but reduces the possibility of contention.
Learn More...Trace flag 1117
Issue: Trace flag 1117 is not enabled globally. This trace flag is recommended for SQL Server 2014 and earlier.
Problem: Enabling trace flag 1117 allows all files in a file group to grow at the same time. If you have set your tempdb files to grow equally, this means they will all grow when one grows, which should reduce the possibility of contention.
Learn More...Unequally sized tempdb data files
Issue: The data files in tempdb are not sized equally.
Problem: Since tempdb will use the file with the most empty space, having unevenly sized files can lead to one or more files being used much more than the other files. This can lead to avoidable contention.
Learn More...File growth settings for tempdb
Issue: The files in tempdb have one or more of the following issues: • tempdb data files with uneven growth rates • tempdb database files with % growth • tempdb database files with < 64 MB growth
Problem: If autogrowth is allowed, all tempdb data and log files for tempdb should have growth rates of fixed values of at least 64 MB. Less that that will lead to many costly autogrowth events. Growth rates with percentage values should be avoided due to inconsistencies initially of many small growth rates and later perhaps overly large growth events. Additionally, all data files should have the same size and growth rates to reduce the likelihood of contention.
Learn More...Number of tempdb data files
Issue: This instance does not have the recommended number of tempdb data files.
Problem: Microsoft's recommendation is as follows: • If the number of logical processors is less than or equal to eight, use the same number of data files. • If the number of logical processors is greater than eight, use eight data files.
Learn More...Slow reads or writes in tempdb
Issue: One or more tempdb files has slow storage reads or writes. The default value in sp_CheckTempdb is greater than 20 ms. These values are collected from sys.dm_io_virtual_file_stats.
Problem: There are any number of reasons why you could have "slow" reads or writes, but basically this comes down to either having less queries using tempdb, or improving the performance of storage.
Learn More...Tempdb file with no growth allowed
Issue: At least one tempdb file is configured to not allow for autogrowth.
Problem: If tempdb does not have sufficient space for data pages, queries can stop or fail.
Learn More...There are multiple log files for tempdb
Issue: There is more than 1 log file for the tempdb database.
Problem: SQL Server databases do not benefit from multiple log files unless one has filled up and another is added in an emergency.
Learn More...Memory-optimized tempdb
Issue: The memory-optimized feature for tempdb has been enabled.
Problem: Starting with SQL Server 2019, tempdb metadata can be memory optimized to reduce contention. However, there are significant limitations to this feature that do not make it ideal for all instances.
Learn More...The tempdb log file is larger than the data files
Issue: The tempdb log file is larger than the size of the data files.
Problem: While this isn't necessarily a problem, having a log file larger than a data file can indicate that you have a very large and/or long-running open transaction using tempdb.
Learn More...About sp_checks
This page contains a list of SQL Server configuration checks performed by Straight Path's suite of sp_check tools. For more details about our free tools, select one from the following list: