Off late my love for TempDB and writing on topics of tempDB has been a great learning experience. The more I work with tempDB, the more fascinated I am. TempDb is being used by a number of operations inside SQL Server, let me list some of them here:
- Temporary user objects like temp tables, table variables
- Cursors
- Internal worktables for spool and sorting
- Row Versioning for snapshot isolation
- Online Index rebuild operations
- MARS (Multiple Active Resultsets)
- AFTER Triggers and more
These are some of the ways in which tempdb in our servers get used. When I was talking about this to one of my DBA friends, he always asks some interesting questions. He said the previous blogs I wrote helped in understanding how to read temp tables, how to see table variable data. Now his requirement was different. He wanted to know if there was any script which will let him know who was consuming tempDB resources. If tempDB is used by many inside the SQL Server context, it is important that we know how to get this information from DMVs. There are a number of DMVs we can use:
- dm_db_file_space_usage – Returns space usage information for each file in tempdb
- dm_db_session_space_usage – Returns the number of pages allocated and deallocated by each session
- dm_db_task_space_usage – Returns page allocation and deallocation activity by task
- We can join these DMV’s with various other DMV’s like sys.dm_exec_sessions, sys.dm_exec_requests, etc and get to the actual TSQL statement and plan responsible for these allocations.
Here is a simple script that will outline the sessions which are using TempDB currently.
SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC
Have you ever had such requirements to monitor and troubleshoot tempDB in your environments? What have you been using to monitor your tempDB usage? What is the typical output you are getting in your environments? Do let me know as we can learn together.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
28 Comments. Leave new
I use WhoisActive for that. then sort by tempdb space used . it store in a table for analysis use
That’s a nice script
I run your script and the result is empty! it should not be true as this is one of the busiest MS SQL server.
I have some filters there. Can you remove them and check?
Me too I am getting empty result set
when I ran the below query I am getting 5 results but I cant sense the impact on temp db
use tempdb
SELECT
sys.dm_exec_sessions.session_id AS [SESSION ID]
,DB_NAME(database_id) AS [DATABASE Name]
,HOST_NAME AS [System Name]
,program_name AS [Program Name]
,login_name AS [USER Name]
,status
,cpu_time AS [CPU TIME (in milisec)]
,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
,total_elapsed_time AS [Elapsed TIME (in milisec)]
,(memory_usage * 8) AS [Memory USAGE (in KB)]
,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
,CASE is_user_process
WHEN 1 THEN ‘user session’
WHEN 0 THEN ‘system session’
END AS [SESSION Type], row_count AS [ROW COUNT]
FROM
sys.dm_db_session_space_usage
INNER join
sys.dm_exec_sessions
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
where status = ‘running’
yah pinal ! I removed the where filter and it works . how to say exactly this program or service has caused the temb deb to grow by this much . Why I am insisting is because we use sql cluster 2008 having lot of databases and our admins wants to which program is exactly consuming tempdb. from your query we see 4 columns reads, writes, logical reads and granted query memory along with cpu time and query elapsed time what exactly will be the first criteria which you can say definitely if this column is high, temp db usage will definitely be very high .
thanks pinal for this brave script helped me alott !!
divya sharma – I am glad that it helped.
Hi Pinal,
Can you remove the filters and send the script to me..
You can remove the Where clause section and run the script Sudheer.
Hi Pinal,
I am running into one of the issue where my tempDB is growing in exponentially. Last week its size was 100 GB and we have increased to 130 GB and now that also consumed and getting error to increase the space.
I run your query without where clause but it is not giving me any details through which I can identify who is taking so much space on tempDB?
I run
select Top 5 * from tempdb.sys.dm_db_task_space_usage
order by internal_objects_alloc_page_count desc
query and output of query reported internal_object_alloc_page_count for two session id is:
14594600
3268792
I suspect this is the cause which is eating up the space of tempDB but not able to extract who is using this session and what query is taking this space?
If you have any idea then please let me know.
Have you tried looking up session_id in Activity Monitor processes?
I think this script is for at the time you run it. Not historical / which session is causing it.
Hi, Could you pls explain the output fields that come as a result of this query as well.
is there any way to get the query consumption in MB ?
Dear Sir,
If we execute a query that are taking longer time. So i want to know this is using tempDB or our selected user DB ?
Thank you it works! Great!
May I know which part of the columns we can tell a query is consuming much space? Is there any?
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END – dmv_er.statement_start_offset)/2) AS Query_Text,
Thank you Pinal. The query is very helpful.
I am glad it helped you @Guruprasad
In my query for XML PATH statement is using lot of tempDB. Is there any way to reduce use of tempDB ? I used for XML PATH for concatenating multiple rows into one
I too facing the same problem.. SET STATISTICS XML OFF is using lot of tempdb. Why is this causing an issue ?
Amazing man you are always super.
Does CDC implement effect tempdb effect and any performance issue ??
Hi Pinal,
Thanks for your help. I have modify this bit further. It may help some:
SELECT
SPU.session_id AS SESSION_ID
,DB_NAME(SPU.database_id) AS DATABASE_Name
,S.HOST_NAME AS HOSTNAME
,S.program_name AS ProgramName
,S.login_name AS Login_name
,S.status AS Status
,(S.cpu_time/1000) AS CPU_TIME_In_Seconds
,(S.total_scheduled_time/1000) AS Total_Scheduled_TIME_In_Seconds
,(S.total_elapsed_time/1000) AS Elapsed_TIME_In_Seconds
,CASE is_user_process
WHEN 1 THEN ‘user session’
WHEN 0 THEN ‘system session’
END AS SESSION_Type
, S.row_count AS Row_Count
, ((Sum(S.memory_usage) * 8)/1024) AS Memory_USAGE_In_MB
,(((Sum(SPU.user_objects_alloc_page_count) – Sum(SPU.user_objects_dealloc_page_count)) * 8)/1024) AS SPACE_Allocated_USER_Objects_MB
,(((Sum(SPU.internal_objects_alloc_page_count) – Sum(SPU.internal_objects_dealloc_page_count)) * 8)/1024) AS SPACE_Allocated_FOR_Internal_Objects_MB
, IsNUll(( SELECT TEXT FROM sys.dm_exec_sql_text(SP.sql_handle)),”) As SQLCommand
FROM sys.dm_db_session_space_usage SPU
LEFT JOIN sys.dm_exec_sessions S ON SPU.session_id = S.session_id
LEFT JOIN master..sysprocesses SP ON SP.spid = S.session_id
Group by
SPU.session_id
,DB_NAME(SPU.database_id)
,S.HOST_NAME
,S.program_name
,S.login_name
,S.status
,S.cpu_time
,total_scheduled_time
,total_elapsed_time
,is_user_process
, S.row_count , SP.sql_handle
Order by (
(Sum(SPU.user_objects_alloc_page_count) – Sum(SPU.user_objects_dealloc_page_count) )
+ (Sum(SPU.internal_objects_alloc_page_count) – Sum(SPU.internal_objects_dealloc_page_count))
) DESC
Hi Pinal.
Can you help me understand, this..
sys.dm_db_session_space_usage can tell you how much tempdb (user / Internal) there are requested and deallocated after each request. where dm_db_task_space_usage can give it for each context_id, shoud I then SUM all user_objects_alloc_page_count to get what is allocated, and sum user_objects_dealloc_page_count for how much there are deallocated, but subtract one from the other can give negative values, and don’t tell me
How much store in TempDB is a session using right NOW.
I “only” need the actual allocated TempDB at the given moment
Hi Pinal, what I am seeing in these days is very very strange.. for me
I was looking at a Sql Server 2008 R2 which had page writes/sec almost 3K/sec while page reads and read-ahead page/sec almost zero..
Finding no other way than seeing what db file is accessed for read/write, Tempdb resulted the suspected one.
Briefly, there is someone running a query on a user db, query rather complex and inefficient (costing more than 400), every minute, not one but three times on the same moment.
Apart from this last thing, 3 instead of 1, and apart obviously from optimizing query, my question is where is that I can find that the execution plan uses Tempdb in this way, what is the operator responsible for this such a large read/write (when executing this query page writes/sec often reaches 1M..
I know you are very expert and careful with all what relates to Sql Server and other things.
Probably you have already seen hundreds of similar cases, for me this is a really odd and strange case..
Thank you for every tip and trick and reasoning which you could have about what I wrote above
and many thanks for your good work and sharing
Pietro
PS: of course, I know I should study more and more Sql Server execution plans, and it’s what I’m going to do..
In case a database has active snapshot isolation, tempdb is used as storage for record-snapshots after a transaction began. In this case you won’t find queries actually “using” the tempdb, instead tempdb is cluttered with versions of all records that were touched since the long running transaction begun. Curiously, running the space-used-report in sqlserver looks like there is no space allocated, which definitely is false. Instead, the transaction-log of the affected database is filled (as the transaction is still running), but the record snapshots are not stored there.
So when investigating tempdb, also take a look at running transactions.