21 November 2012

Cached pages in TempDB (MB)

by Jonathan Allen

13,658 0 Performance, Storage

This metric helps a DBA analyze how well TempDB is working on their server. Knowing more about what is stored in it, and by which process, will greatly help diagnose performance issues.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

TempDB is a key component in the general performance of a SQL Server. Knowing more about what is stored in it, and by which process, will greatly help diagnose performance issues. This metric helps a DBA analyze how well TempDB is working on their server. Seeing big changes in TempDB commitment between one database and another could be a sign of a wrongly sized TempDB, or indicate that one database is using TempDB in a sub-optimal way.

Metric definition

Name

Cached pages in TempDB (MB)

Description

The amount of space (in MB) consumed by user and internal processes, in TempDB per database. Big changes in allocations (increases or decreases) of space for particular databases can indicate possible problems, such as poorly written T-SQL. Keep a watch on big allocations that are not subsequently de-allocated appropriately. This trace shows the net effect, so increases not followed by decreases show a de-allocation is not taking place, possibly because of a long running transaction using a temporary table. Consider this trace along with the Log size and Log used traces for TempDB and use this information to ensure your TempDB files are sized appropriately.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			WITH    TempUsage
          AS ( SELECT [ddtsu].[session_id] ,
                    [ddtsu].[user_objects_alloc_page_count] ,
                    [ddtsu].[user_objects_dealloc_page_count] ,
                    [ddtsu].[internal_objects_alloc_page_count] ,
                    [ddtsu].[internal_objects_dealloc_page_count]
                FROM [sys].[dm_db_task_space_usage] AS ddtsu
               UNION ALL
               SELECT [ddssu].[session_id] ,
                    [ddssu].[user_objects_alloc_page_count] ,
                    [ddssu].[user_objects_dealloc_page_count] ,
                    [ddssu].[internal_objects_alloc_page_count] ,
                    [ddssu].[internal_objects_dealloc_page_count]
                FROM [sys].[dm_db_session_space_usage] AS ddssu
             )
             
    SELECT SUM(( ( tu.[user_objects_alloc_page_count]			-	tu.[user_objects_dealloc_page_count] )
                 + ( tu.[internal_objects_alloc_page_count]		-	tu.[internal_objects_dealloc_page_count] ) ))
            / 128.0 AS [TempDB_Objects_MB]
        FROM [TempUsage] AS tu 
            INNER JOIN [sys].[sysprocesses] AS s
                ON tu.[session_id] = [s].[spid]
        WHERE [s].[spid] > 50
            AND [s].[dbid] = DB_ID();
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
				
						WITH    TempUsage
          AS ( SELECT [ddtsu].[session_id] ,
                    [ddtsu].[user_objects_alloc_page_count] ,
                    [ddtsu].[user_objects_dealloc_page_count] ,
                    [ddtsu].[internal_objects_alloc_page_count] ,
                    [ddtsu].[internal_objects_dealloc_page_count]
                FROM [sys].[dm_db_task_space_usage] AS ddtsu
               UNION ALL
               SELECT [ddssu].[session_id] ,
                    [ddssu].[user_objects_alloc_page_count] ,
                    [ddssu].[user_objects_dealloc_page_count] ,
                    [ddssu].[internal_objects_alloc_page_count] ,
                    [ddssu].[internal_objects_dealloc_page_count]
                FROM [sys].[dm_db_session_space_usage] AS ddssu
             )
             
    SELECT SUM(( ( tu.[user_objects_alloc_page_count]			-	tu.[user_objects_dealloc_page_count] )
                 + ( tu.[internal_objects_alloc_page_count]		-	tu.[internal_objects_dealloc_page_count] ) ))
            / 128.0 AS [TempDB_Objects_MB]
        FROM [TempUsage] AS tu 
            INNER JOIN [sys].[sysprocesses] AS s
                ON tu.[session_id] = [s].[spid]
        WHERE [s].[spid] > 50
            AND [s].[dbid] = DB_ID();

Instances to collect from

Select all

Databases to collect from

TempDB

Collection frequency

600

Note: If you suspect that there may be an issue with TempDB, increase the collection frequency to every minute so you can analyze more data.

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

An alert is not required

13,658 Rate

Click to rate this post!

[Total: 2 Average: 3.5]

Cached pages in TempDB (MB)

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics