March 15, 2011 at 10:11 pm
So I had an issue the other morning where the TEMPDB data file on an instance was about to fill. Before I could find the offending SPID, I needed to cycle SQL Server, or it was going down anyways. I have a number of tools in my arsenal for finding such SPIDS and it bugs me that I could not. So, a little practice. Please review/comment on my code as you see fit.
In my current environment I have code that tells me 11.56GB of TEMPDB space is being utilized. I can confirm with the following, that this 11GB is being used by the Version Store. Snapshot isolation is in use FYI
SELECT DB_NAME(database_id) AS [DBName]
,SUM(user_object_reserved_page_count) * 8 / 1024 / 1024 AS [Usr_Objects_GB]
,SUM(internal_object_reserved_page_count) * 8 / 1024 / 1024 AS [Int_Objects_GB]
,SUM(version_store_reserved_page_count) * 8 / 1024 / 1024 AS [VStore_GB]
,SUM(mixed_extent_page_count) * 8 / 1024 / 1024 AS [MixedExtent_GB]
FROM sys.dm_db_file_space_usage
GROUP BY database_id
So, now I want to know which SPID is using the majority of that space. Version store space is categorized as Internal (versus User) within TEMPDB, so I would run something like the following. My goal is to find the SPID with a "large" discrepancy between allocated and deallocated. However, I see no such discrepancy.
So, WHO has the 11GB of TEMPDB space. I must be missing something "obvious"
SELECT
s.host_name
,su.[session_id]
,d.name [DBName]
,su.[database_id]
,su.[internal_objects_alloc_page_count] [Int_Pg_Alloc]
,su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc]
,su.[internal_objects_alloc_page_count] - su.[internal_objects_dealloc_page_count] [Int_AllocDiff]
,( su.[internal_objects_alloc_page_count] * 1.0 / 128 )/1024 [Int_Alloc_GB]
,( su.[internal_objects_dealloc_page_count] * 1.0 / 128 )/1024 [Int_DeAlloc_GB]
,(( su.[internal_objects_alloc_page_count] * 1.0 / 128 )/1024)-(( su.[internal_objects_dealloc_page_count] * 1.0 / 128 )/1024) Int_AllocDiff_GB
FROM [sys].[dm_db_session_space_usage] su
INNER JOIN sys.databases d ON su.database_id = d.database_id
INNER JOIN sys.dm_exec_sessions s ON su.session_id = s.session_id
WHERE ( su.user_objects_alloc_page_count > 0
OR su.internal_objects_alloc_page_count > 0
)
ORDER BY Int_AllocDiff_GB DESC
March 16, 2011 at 4:47 am
I believe (I'm sure someone will correct me if I'm wrong) that version store info is separate to internal objects info (which are more to do with large hash joins etc)
The following query from this blog post can track long running transactions that affect version store.
SELECT qt.TEXT query_name,
dm_tran_active_snapshot_database_transactions.transaction_id,
transaction_sequence_num,
elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions,
sys.dm_tran_session_transactions,
sys.dm_exec_requests
OUTER APPLY sys.Dm_exec_sql_text(sql_handle) qt
WHERE sys.dm_tran_active_snapshot_database_transactions.transaction_id = sys.dm_tran_session_transactions.transaction_id
AND sys.dm_exec_requests.session_id = sys.dm_tran_session_transactions.session_id
ORDER BY elapsed_time_seconds DESC
March 16, 2011 at 4:53 am
This script could be a starting point:
http://www.sqlservercentral.com/scripts/tempdb/72007/
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply