Inconsistent Tempdb Growth

Tempdb database is a system database that is shared across all databases and all connections in SQL Server.   As a general rule, I configure all my client’s servers to have multiple tempdb files with consistent sizes and consistent auto growth set according to best practices. The number of tempdb files created equals the number of cores or 8, whichever is lower. And the size set for each file depends on each individual SQL instance to accommodate a typical workload.

The Problem

Recently during a routine review of a client’s server, I noticed in the SQL Server Logs that there were messages indicating I/O pressure for the tempdb database.

I also noticed in Windows Explorer that my tempdb files were no longer uniform in size. In fact the first data file had grown way out of proportion compared to all the other data files:

After double-checking the database properties, I confirmed that these files should have grown in consistent amounts:

So what happened to cause this inconsistent growth and how should I fix it?

Tempdb is used (and can grow) when users explicitly create objects like temp tables, variables, cursors, or stored procedures. Tempdb is also used when the database engine creates work tables for sorts or spooling, and row versioning operations.

It’s often difficult to diagnose what happened on a SQL Server after the fact. There are many resources online to determine what is currently using tempdb but not a lot of information from processes that may have run days ago.

What Happened?

First, I want to see what is currently using tempdb to find out if there are any processes using all that space in the database. The TSQL statements in the following links are helpful in determining who is currently using tempdb:

https://littlekendra.com/2009/08/27/whos-using-all-that-space-in-tempdb-and-whats-their-plan/

http://www.sqlservercentral.com/scripts/tempdb/72007/

https://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/

If you find there are plans using the tempdb causing substantial growth it is probably a good opportunity for some performance tuning.

In my situation there was nothing significant currently using tempdb, so my issue happened prior to my discovering the problem. Since I was not able to see any active queries creating temp tables, stored procs, cursors, or variables, I wanted to see if there were row versioning operations occurring.

If snapshot isolation is enabled on a database, performance can improve because it eliminates locking on the underlying tables. However in order to do this SQL puts the data (as it existed at the start of the transaction) into a temp table with row versions for each transaction, so that all queries in the transaction see the same version (or snapshot) of the database. This can take up a large amount of space in tempdb, depending on the size of the tables in the database being queried.

Run this statement to find any user databases with snapshot isolation enabled:

select * from sys.databases

where (snapshot_isolation_state = 1 or is_read_committed_snapshot_on = 1)

and database_id > 4

My client had a few databases with snapshot isolation enabled, so we had a conversation about the reasoning behind these settings. However it was still unclear as to why the tempdb grew and why the files grew out of proportion. As stated earlier, unless you are actively collecting performance data for this type of behavior, it is often very difficult to troubleshoot the cause of the problem after the fact. So what do we do about it?

Solution

When your tempdb files grow beyond the original size set in the properties, and they grow out of proportion, the preferred and best solution is to grow the files to a uniform size, as long as there is room on your disk. This is the preferred solution because if the tempdb files grew, it apparently needed that much room to perform the tasks that caused it to grow. You can adjust the size of your tempdb files using the GUI, or you can run the following statement for each data file:

USE master

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, SIZE = 2000MB)

If there is not enough space on your drive and you need to resolve the issue right now, shrink the tempdb files down to a uniform size. Keeping your tempdb data files the same size helps to avoid page contention issues.

This can be done through the GUI by selecting each individual data file and specifying the desired size, or you can run this statement for each data file:

USE tempdb

GO

DBCC SHRINKFILE (tempdev, 1000)

GO

If for some reason the data files don’t shrink to the size you specified, try running this

DBCC FREEPROCCACHE

then execute the SHRINKFILE statement again. Do this during a maintenance time of course and not during production time while users are connected.

Prevention and Best Practice

Put your tempdb on its own volume if possible. If your tempdb is on the same drive as your user database files and that drive runs out of space, it will be difficult to restart SQL Server. If your tempdb is on its own drive you can utilize all of the space for your tempdb files and also reduce I/O contention.

Create as many data files as you have processors, up to a maximum of 8. (Note that dual-core CPU is the same as 2 CPUs). This will reduce storage contention and is more scalable. There is much debate on the necessity of this practice…this is a general guideline.

Make each data file the same size. This helps to avoid SGAM page contention issues, and will allow for optimal proportional-fill performance. Set the size of your data files large enough to accommodate a typical workload. And if you have tempdb on its own volume, size your files to use the majority of the space on the volume. This way SQL doesn’t have to stop activity in order to grow the files.

Set the autogrowth to a fixed amount (not percentage) and to a reasonable size. If the threshold value is set too low compared to the amount of data written to tempdb, the files will have to expand too often. During autogrowth the database is unavailable, which will cause user transactions to have to wait until the growth process completes. Note – every time SQL server instance is restarted, the tempdb will be recreated to the original size specified in the database properties.

Enable Trace Flag 1117 – If you are not yet using SQL Server 2016 and are on an earlier version, enabling trace flag t1117 will ensure that your tempdb files all grow when any of the files reach the autogrow threshold. (Starting with SQL Server 2016 this flag is unnecessary.) Trace flag 1117 is a global setting, and will impact every database in your instance, not just tempdb. You can enable the trace flag by going to SQL Server Configuration Manager > SQL Server Services > Right click on SQL Server (MSSQLSERVER) > Properties > Startup Parameters > enter –t1117 and click Add. You must restart the service for the change to take effect.

Finally, you can minimize tempdb utilization by practicing the following:

• Avoid SORT_IN_TEMPDB option
• Avoid unnecessary cursors
• Avoid spooling (CTEs referenced multiple times)
• Avoid using MARS
• Don’t enable snapshot isolation unless there is a very compelling reason to do so
• Avoid using triggers for bulk operations
• Avoid using LOBs as local variables

Reference: https://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!