We are using SQL Server 2012 and the tempdb is growing so fast. How do I effectively find what’s causing my tempdb growing. Can I monitor the file size change with Extended Events to find out the ...The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server. tempdb is re-created every time is started so that the system always starts with a clean copy of the database. The database engine uses tempdb for:
- Temporary user objects (temp tables, table variables ...)
- Internal objects (work tables/work files/ intermediate results ...)
- Version store for data modification transactions (Online indexes, After trigges, Snapshot, MARS ...)
- Service Broker, database mail ...
But this is another deep dive topic of tempdb latch contention on allocation pages... :-)
For now I only want to know
What's causing that my tempdb is growing so fast?
I usually look first to the tempdb file size and autogrow settings, you know something like this:
--> SQL Server tempdb file size and autogrow information <--
SET NOCOUNT ON;
GO
USE [master];
GO
-- Check for trace flag 1117/1118 or any other trace flag
DBCC TRACESTATUS (1117) WITH NO_INFOMSGS; -- Grows all data files at once, else it goes in turns.
DBCC TRACESTATUS (1118) WITH NO_INFOMSGS; -- Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent
DBCC TRACESTATUS () WITH NO_INFOMSGS;
SELECT
old.physical_name AS 'FileName',
old.name AS 'LogicalName',
old.size * 8.0 / 1024.0 AS 'OriginalSizeInMB',
new.size * 8.0 / 1024.0 AS 'CurrentSizeInMB',
'GrowthType' =
CASE
WHEN new.growth = 0 THEN 'File is fixed size and will not grow.'
WHEN new.growth > 0 AND new.is_percent_growth = 0
THEN 'Growth increment is in units of 8-KB pages, rounded to the nearest 64 KB'
ELSE 'Growth increment is expressed as a whole number percentage.'
END,
'GrowthIncrement' =
CASE
WHEN new.is_percent_growth = 0 THEN CAST((new.growth * 8 / 1024) AS NVARCHAR(30)) + 'MB'
ELSE CAST (new.growth AS NVARCHAR(30)) + '%'
END,
'MaximumFileSize' =
CASE new.max_size -- file size, in 8-KB pages
WHEN 0 THEN 'No growth is allowed.'
WHEN -1 THEN 'File will grow until the disk is full.'
-- !Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file!
WHEN 268435456 THEN 'Log file will grow to a maximum size of 2 TB.'
ELSE CAST (new.max_size * 8.0 / 1024.0 AS NVARCHAR(30)) + 'MB'
END
FROM
MASTER.sys.master_files old INNER JOIN tempdb.sys.database_files new ON
old.FILE_ID = new.FILE_ID
WHERE
database_id = DB_ID('tempdb')
AND old.size <> new.size
In fact, this is a good starting point to presize tempdb, for peak periods or set autogrow to be rare but "big enough", but I want to know what's causing my tempdb to grow so fast.
So I came out on Extended Events. Extended Events are a new mechanism introduced in SQL Server 2008 to capture SQL Server performance data with low overhead compared to SQL Server profiler, server side tracing, DBCC commands and other utilities. The scope of Extended Events is far beyond this tip and please refer to the articles below for better understanding of this feature. Extended Events allow more granular level tracking that was impossible in prior SQL Server versions. Extended Events in SQL Server 2012, XE in short, allows you capturing performance monitoring data at individual database level or per session level and for this post I will track information at the database level for tempdb.
The code snippet below creates the session to track tempdb file size changed and some informations of client application, session id, client hostname and so on.
--Drop the event if it already exists
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'tempdb_file_size_changed')
DROP EVENT SESSION [tempdb_file_size_changed] ON SERVER;
GO
-- Create event
CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
ACTION
(sqlserver.client_app_name,sqlserver.client_hostname, sqlserver.database_id,sqlserver.session_id,
sqlserver.session_nt_username,sqlserver.username) WHERE ([database_id]=(2)))
ADD TARGET package0.event_file
(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\tempdb_file_size_changed.xel')
WITH (MAX_DISPATCH_LATENCY=1 SECONDS)
GO
Once the script is completed and started I can wait for my tempdb rumbling.
To summarize the file size change, I ran these following queries:
SET NOCOUNT ON;
GO
USE [master];
GO
SELECT
database_name AS DatabaseName
, file_type AS FileType
, SUM (size_change_kb) AS Total_Size_Change_KB
, SUM (duration) AS Total_Duration
, client_app_name AS Client_Application
, session_id AS SessionID
FROM (
SELECT
n.value ('(data[@name="size_change_kb"]/value)[1]', 'int') AS size_change_kb
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
FROM
( SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\tempdb_file_size_changed*.xel',
NULL,
NULL,
NULL)
) AS tab
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
GROUP BY client_app_name, database_name, file_type, session_id
ORDER BY client_app_name
And what was causing my tempdb, it was me by Microsoft SQL Server Management Studio :-( - only for this blog entry.
Feel free to download these scripts:
I wish you a nice day,
tosc
]]>
