SQL TempDB Usage Test

User objects include both user-defined tables and indexes, and system catalog tables and indexes. User-defined tables include the global temporary tables such as ##t, and local temporary tables such as #t. Both of these objects are session scoped but a global temporary table lives until all sessions that are using it expire or terminate. Local temporary tables, on the other hand, are destroyed when the scope (for example, stored procedure or session) they were created in expires or terminates. Local temporary tables also include table variables such as @t, the return value in table valued functions, and the mapping index for online clustered index build with the SORT_IN_TEMPDB option.

A high value of this measure indicates that a lot of space has been used by user objects. Compare the value of this measure with that of the Internal objects, Version store, and Mixed extent measures to know where the maximum tempdb space has been spent.

Internal objects are created internally by SQL Server. These objects are used:

1. To store intermediate runs for sort.
2. To store intermediate results for hash joins and hash aggregates.
3. To store XML variables or other large object (LOB) data type variables. The LOB data type includes all of the large object types: text, image, ntext,varchar(max), varbinary(max), and all others.
4. By queries that need a spool to store intermediate results.
5. By keyset cursors to store the keys.
6. By static cursors to store a query result.
7. By Service Broker to store messages in transit.
8. By INSTEAD OF triggers to store data for internal processing.

Internal objects are also used by any feature that uses these operations. For example, DBCC CHECK internally uses a query that may need to spool intermediate results. Query notification and event notification use Service Broker, so they need space in tempdb as well.

Compare the value of this measure with that of the User objects, Version store, and Mixed extent measures to know where the maximum tempdb space has been spent. If this measure reports the highest value it implies that query plans are making heavy use of the tempdb.

This is not necessarily a problem, but you may want to look at the query plans to see if alternate query plans can be generated by creating indexes or by re-formulating the queries so as to minimize tempdb  space usage.

Version stores are used to store row versions generated by transactions for features such as snapshot isolation, triggers, MARS (multiple active result sets), and online index build. There are two version stores in tempdb for the whole instance of SQL Server. The online index build version store is for row versions from tables that have online index build operations on them. The common version store is for row versions from all other tables in all databases. 

Compare the value of this measure with that of the User objects, Internal objects, and Mixed extent measures to know where the maximum tempdb space has been spent. If this comparative analysis reveals that this measure reports the highest value, it implies that version store cleanup cannot keep pace with version generation. See if a long-running transaction is preventing version store cleanup. Or, a high transaction throughput might be generating a large number of versions per minute. The background task cleans up versions every minute.

Extents are the basic unit in which space is allocated to tables and indexes. To make its space allocation efficient, SQL Server does not allocate entire extents to tables with small amounts of data. SQL Server has two types of extents - Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owner object. A mixed extent is a single extent that contains multiple tables; it can be shared by up to eight objects.

A high value indicates that a large amount of tempdb space is occupied by mixed extents. Compare the value of this measure with that of the User objects, Internal objects, and Version store measures to know where the maximum tempdb space has been spent.

A high value is desired for this measure.

A consistent rise in the value of this measure could indicate a gradual, but steady erosion of space in the tempdb database. A value close to 100% signals a potential space crunch in the tempdb, which can affect the performance of all the applications using the tempdb. Under such circumstances, it would be good practice to compare the values of the User objects, Internal objects, Version store, and Mixed extents measures to know which object type is consuming the most space in the tempdb. You can then use the DMVs in the SQL server to analyze which Transact-SQL statements are the top consumers of tempdb space. You can kill such tasks, where appropriate, to free space.

Auto-growth is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file grows is based on the settings that you have for the file growth options for your database.

If this setting is enabled for one/more database files in the tempdb database, this measure will report the value Yes. If this setting is not enabled for any of the database files in the tempdb database, then this measure will report the value No.

The numeric values that correspond to the above-mentioned measure values are listed in the table below:

Note:

Typically, this measure reports the Measure Values listed in the table above as the status of the Autogrowth setting. In the graph of this measure however, the status will be represented using the numeric values – i.e., 100 and 0.

There are three different settings you can use to identify how your database files will grow. They can grow by a specific size, a percentage of the current size, or not grow at all. Additionally you can set your files to unrestricted growth, which means they will keep growing as they need more space or you run out of disk space. Or you can restrict the growth of a database file to grow no larger than a specified size. Each one of these different auto-grow settings have defaults, or you can set them for each database file.

The default auto-growth settings for a database are rarely the ideal settings for how your database should grow. If you have an idea of the growth profile of your database when you first build it then you should set your auto-growth properties based on those growth projections. If you don’t have any idea of how fast your database will grow then you should be monitoring for auto-growth events. Knowing how often your database grows will give you some ideas of the growth rate of your database.

A high value implies that the tempdb has adequate disk space for growth.  If the value of this measure is low, then you may have to fine-tune your auto-growth settings accordingly. 

The value of this measure is preferred to be low. A high value indicates that there is very little disk space for the use of tempdb. You may then have to free up some disk space or fine-tune your auto-growth settings.