{"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.","tsql":"WITH    TempUsage\r\n          AS ( SELECT [ddtsu].[session_id] ,\r\n                    [ddtsu].[user_objects_alloc_page_count] ,\r\n                    [ddtsu].[user_objects_dealloc_page_count] ,\r\n                    [ddtsu].[internal_objects_alloc_page_count] ,\r\n                    [ddtsu].[internal_objects_dealloc_page_count]\r\n                FROM [sys].[dm_db_task_space_usage] AS ddtsu\r\n               UNION ALL\r\n               SELECT [ddssu].[session_id] ,\r\n                    [ddssu].[user_objects_alloc_page_count] ,\r\n                    [ddssu].[user_objects_dealloc_page_count] ,\r\n                    [ddssu].[internal_objects_alloc_page_count] ,\r\n                    [ddssu].[internal_objects_dealloc_page_count]\r\n                FROM [sys].[dm_db_session_space_usage] AS ddssu\r\n             )\r\n             \r\n    SELECT SUM(( ( tu.[user_objects_alloc_page_count]\t\t\t-\ttu.[user_objects_dealloc_page_count] )\r\n                 + ( tu.[internal_objects_alloc_page_count]\t\t-\ttu.[internal_objects_dealloc_page_count] ) ))\r\n            \/ 128.0 AS [TempDB_Objects_MB]\r\n        FROM [TempUsage] AS tu \r\n            INNER JOIN [sys].[sysprocesses] AS s\r\n                ON tu.[session_id] = [s].[spid]\r\n        WHERE [s].[spid] > 50\r\n            AND [s].[dbid] = DB_ID();","instances":true,"frequency":600,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"noalertremark":"An alert is not required","alertdescription":"Alert description goes here","aboveorbelow":"above","collections":3,"alertenabled":true,"_thresholds_high":{"selected":true,"value":800},"_thresholds_medium":{"selected":true,"value":500},"_thresholds_low":{"selected":true,"value":100},"frequency_comment":"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.","targetentitytype":0}