I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
-
5See Troubleshooting Insufficient Disk Space in tempdb on MSDN.– David BrabantCommented Jun 25, 2012 at 14:55
Add a comment
|
4 Answers
From http://www.sqlservercentral.com/scripts/tempdb/72007/
;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
THEN 0
ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), ''
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;
EDIT
As Martin pointed out in a comment, this would not find active transactions that are occupying space in tempdb, it will only find active queries that are currently utilizing space there (and likely culprits for current log usage). So there could be an open transaction but the actual query that causes the problem is no longer running.
You could change the inner join on sys.dm_exec_requests to a left outer join, then you will return rows for sessions that aren't currently actively running queries.
The query Martin posted...
SELECT database_transaction_log_bytes_reserved,session_id
FROM sys.dm_tran_database_transactions AS tdt
INNER JOIN sys.dm_tran_session_transactions AS tst
ON tdt.transaction_id = tst.transaction_id
WHERE database_id = 2;
...would identify session_ids with active transactions that are occupying log space, but you wouldn't necessarily be able to determine the actual query that caused the problem, since if it's not running now it won't be captured in the above query for active requests. You may be able to reactively check the most recent query using DBCC INPUTBUFFER but it may not tell you what you want to hear. You can outer join in a similar way to capture those actively running, e.g.:
SELECT tdt.database_transaction_log_bytes_reserved,tst.session_id,
t.[text], [statement] = COALESCE(NULLIF(
SUBSTRING(
t.[text],
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset < r.statement_start_offset
THEN 0
ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
), ''
), t.[text])
FROM sys.dm_tran_database_transactions AS tdt
INNER JOIN sys.dm_tran_session_transactions AS tst
ON tdt.transaction_id = tst.transaction_id
LEFT OUTER JOIN sys.dm_exec_requests AS r
ON tst.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
WHERE tdt.database_id = 2;
You can also use the DMV sys.dm_db_session_space_usage to see overall space utilization by session (but again you may not get back valid results for the query; if the query is not active, what you get back may not be the actual culprit).
;WITH s AS
(
SELECT
s.session_id,
[pages] = SUM(s.user_objects_alloc_page_count
+ s.internal_objects_alloc_page_count)
FROM sys.dm_db_session_space_usage AS s
GROUP BY s.session_id
HAVING SUM(s.user_objects_alloc_page_count
+ s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text],
[statement] = COALESCE(NULLIF(
SUBSTRING(
t.[text],
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset < r.statement_start_offset
THEN 0
ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
), ''
), t.[text])
FROM s
LEFT OUTER JOIN
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;
With all of these queries at your disposal, you should be able to narrow down who is using up tempdb and how, especially if you catch them in the act.
some tips for minimizing tempdb utilization
- use fewer #temp tables and @table variables
- minimize concurrent index maintenance, and avoid the
SORT_IN_TEMPDBoption if it isn't needed - avoid unnecessary cursors; avoid static cursors if you think this may be a bottleneck, since static cursors use work tables in tempdb
- try to avoid spools (e.g. large CTEs that are referenced multiple times in the query)
- don't use MARS
- thoroughly test the use of snapshot / RCSI isolation levels - don't just turn it on for all databases since you've been told it's better than NOLOCK (it is, but it isn't free)
- in some cases, it may sound unintuitive, but use more temp tables. e.g. breaking up a humongous query into parts may be slightly less efficient, but if it can avoid a huge memory spill to tempdb because the single, larger query requires a memory grant too large...
- avoid enabling triggers for bulk operations
- avoid overuse of LOB types (max types, XML, etc) as local variables
- keep transactions short and sweet
- don't set tempdb to be everyone's default database -
You may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over - for example database mail, event notifications, query notifications and service broker all use tempdb in some way. You can stop using these features, but if you're using them you can't dictate how and when they use tempdb.
answered Jun 25, 2012 at 14:55
SELECT tst.[session_id],
s.[login_name] AS [Login Name],
DB_NAME (tdt.database_id) AS [Database],
tdt.[database_transaction_begin_time] AS [Begin Time],
tdt.[database_transaction_log_record_count] AS [Log Records],
tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS statement_text,
st.[text] AS [Last T-SQL Text],
qp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
ON tst.[transaction_id] = tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s
ON s.[session_id] = tst.[session_id]
JOIN sys.dm_exec_connections c
ON c.[session_id] = tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.[session_id] = tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
WHERE DB_NAME (tdt.database_id) = 'tempdb'
ORDER BY [Log Bytes Used] DESC
GO
Thank you for this post, probably the only one of its kind. My test was simple, create a temp table and make sure it shows up when I run any of the queries from this post... only one or two truly succeeded. I corrected it to join to the T-SQL, optimized it for longer runs and made it pretty useful. Let me know if I missed something but so far you got an automated/looped script. It provides a way of assessing which query/SPID is the offender over a period of time by using standard deviation (STDEV) query below.
This runs every 3 minutes for 40 times, so 2 hours. Modify the parameters as you see fit.
There is a WHERE > 50 pages filter below that folks may want to clear just in case you have lots of small tables. Otherwise you will not catch that nuance with the below as it is...
Enjoy!
DECLARE @minutes_apart INT; SET @minutes_apart = 3
DECLARE @how_many_times INT; SET @how_many_times = 40
--DROP TABLE tempdb..TempDBUsage
--SELECT * FROM tempdb..TempDBUsage
--SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC
DECLARE @delay_string NVARCHAR(8); SET @delay_string = '00:' + RIGHT('0'+ISNULL(CAST(@minutes_apart AS NVARCHAR(2)), ''),2) + ':00'
DECLARE @counter INT; SET @counter = 1
SET NOCOUNT ON
if object_id('tempdb..TempDBUsage') is null
begin
CREATE TABLE tempdb..TempDBUsage (
session_id INT, pages INT, num_reads INT, num_writes INT, login_time DATETIME, last_batch DATETIME,
cpu INT, physical_io INT, hostname NVARCHAR(64), program_name NVARCHAR(128), text NVARCHAR (MAX)
)
end
else
begin
PRINT 'To view the results run this:'
PRINT 'SELECT * FROM tempdb..TempDBUsage'
PRINT 'OR'
PRINT 'SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC'
PRINT ''
PRINT ''
PRINT 'Otherwise manually drop the table by running the following, then re-run the script:'
PRINT 'DROP TABLE tempdb..TempDBUsage'
RETURN
end
--GO
TRUNCATE TABLE tempdb..TempDBUsage
PRINT 'To view the results run this:'; PRINT 'SELECT * FROM tempdb..TempDBUsage'
PRINT 'OR'; PRINT 'SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC'
PRINT ''; PRINT ''
while @counter <= @how_many_times
begin
INSERT INTO tempdb..TempDBUsage (session_id,pages,num_reads,num_writes,login_time,last_batch,cpu,physical_io,hostname,program_name,text)
SELECT PAGES.session_id, PAGES.pages, r.num_reads, r.num_writes, sp.login_time, sp.last_batch, sp.cpu, sp.physical_io, sp.hostname, sp.program_name, t.text
FROM sys.dm_exec_connections AS r
LEFT OUTER JOIN master.sys.sysprocesses AS sp on sp.spid=r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.most_recent_sql_handle) AS t
LEFT OUTER JOIN (
SELECT s.session_id, [pages] = SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count)
FROM sys.dm_db_session_space_usage AS s
GROUP BY s.session_id
HAVING SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count) > 0
) PAGES ON PAGES.session_id = r.session_id
WHERE PAGES.session_id IS NOT NULL AND PAGES.pages > 50
ORDER BY PAGES.pages DESC;
PRINT CONVERT(char(10), @counter) + ': Ran at: ' + CONVERT(char(30), GETDATE())
SET @counter = @counter + 1
waitfor delay @delay_string
end
-
Combining this with the accepted answer is a convenient way to track eluding tempdb activity. Running this via a SQL Agent scheduled task will keep this running even if SSMS is closed. Thanks for sharing! Commented Oct 11, 2018 at 15:25
Unfortunately the tempDB log cannot be directly traced back to sessionID's by viewing running processes.
Shrink the tempDB log file to a point where it will grow significantly again. Then create an extended event to capture the log growth. Once it grows again you can expand the extended event and view the package event file. Open the file, add a time filter, file type filter (you don't want the data file results included), and then group it by session id in SSMS. This will help you find the culprit(s) as you are looking for session id's with the most group by's. Of course you need to collect what is running in session id's through another process or tool. Maybe someone knows how to get the query from the query_hash column and will be kind enough to post the solution.
Extended event's results:
Script to create the extended 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.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
