SQL TempDB Usage Test

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Since tempdb is shared by multiple users/applications, if tempdb runs out of disk space, it can cause significant disruptions in the SQL Server production environment and can suspend operations of all applications that are using it. To prevent such an eventuality, you can use the SQL TempDB Usage test to continuously track tempDB usage, capture potential space contentions, and initiate measures to avert the contention.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL server being monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. ssl – If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No.
  5. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor an Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter.
  6. USER – If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server.

  7. password - The password of the specified user.
  8. confirm password - Confirm the password by retyping it.
  9. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the DOMAIN text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server.
  10. isntlmv2 - In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host.
  11. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Total size:

Indicates the total size of tempdb.

MB

 

User objects:

Indicates the amount of space allocated from uniform extents to user objects in the tempdb database.

MB

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:

Indicates the amount of space allocated  from uniform extents to internal objects in the tempdb database.

MB

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 store:

Indicates the amount of space allocated from uniform extents for the version store.

MB

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.

Mixed extent:

Indicates the amount of space that is used by objects of multiple types (user objects, internal objects, version store, Index Allocation Map (IAM) pages, etc.)

MB

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.

Free space:

Indicates the amount of unused space in the tempdb database.

MB

A high value is desired for this measure.

Usage of allocated space:

Indicates the percentage of allocated tempdb space that is currently in use.

 

Percent

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.

Is auto-growth enabled?

Indicates whether/not auto-growth is enabled for th tempdb database.

Boolean

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:

Measure Value Numeric Value
Yes 100
No 0

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.

Max file size:

Indicates the maximum size up to which the database files (for which the Auto-growth setting has been enabled) in the tempdb database can grow. This measure will be reported only if the ‘Is auto-growth enabled?’ measure returns the value ‘Yes’.

 

 

MB

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.

Free disk space:

Indicates the amount of disk space that is currently available for use for tempdb database.

MB

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. 

Usage as % of max size:

Indicates the percentage of Max file size utilized on tempdb database.

This measure will be reported only if the ‘Is auto-growth enabled?’ measure returns the value ‘Yes’.

 

 

Percent

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.