October 11, 2013 at 9:28 am
Hey guys,
I'm having an issue with TempDB usage. A specific client is massively using it and I probably will deactivate him, but I don't want to do this without a list with what are the queries that is possibly causing this. To do that, I tried to modify a script that I have, inserting an INNER JOIN with dm_exec_request, followed by a CROSS APPLY with dm_exec_sql_text, but when I do this, a lot of rows just disappear.
1. First query without the modifications:
SELECT es.login_name AS 'LoginName',
DB_NAME(ssu.database_id) AS 'DatabaseName',
(es.memory_usage * 8) AS 'MemoryUsage (in KB)',
(ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
(ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
(ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
(ssu.internal_objects_dealloc_page_count * 8) AS 'Spáce Deallocated For Internal Objects (in KB)',
CASE es.is_user_process
WHEN 1 THEN 'User Session'
WHEN 0 THEN 'System Session'
END AS 'SessionType',
es.row_count AS 'RowCount'
FROM sys.dm_db_session_space_usage ssu
INNER JOIN sys.dm_exec_sessions es ON ssu.session_id = es.session_id
2. Query query with the modifications
SELECT es.login_name AS 'LoginName',
DB_NAME(ssu.database_id) AS 'DatabaseName',
(es.memory_usage * 8) AS 'MemoryUsage (in KB)',
(ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
(ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
(ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
(ssu.internal_objects_dealloc_page_count * 8) AS 'Spáce Deallocated For Internal Objects (in KB)',
CASE es.is_user_process
WHEN 1 THEN 'User Session'
WHEN 0 THEN 'System Session'
END AS 'SessionType',
es.row_count AS 'RowCount',
st.text
FROM sys.dm_db_session_space_usage ssu
INNER JOIN sys.dm_exec_sessions es ON ssu.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er ON ssu.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE DB_NAME(ssu.database_id) = 'tempdb'
AND es.session_id <> @@SPID
AND es.login_name <> 'sa'
October 11, 2013 at 9:36 am
Try an outer join, or join to exec_sessions instead. Session space usage is for all current sessions, regardless of whether or not they are currently running queries. exec requests only shows currently running queries
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2013 at 12:59 pm
GilaMonster (10/11/2013)
Try an outer join, or join to exec_sessions instead. Session space usage is for all current sessions, regardless of whether or not they are currently running queries. exec requests only shows currently running queries
When I try to use an outer join, the result is very similar to my problem. About the dm_exec_session, I already use it (first join) but as far as I know I can't get the query from there.
October 11, 2013 at 1:17 pm
You might be able to use the most_recent_sql_handle value from the dm_exec_connections DMV to get what you are after. It provides the SQL_HANDLE associated with the last request executed against SQL Server on a connection.
The exec_requests DMV only supplies a row for each user and system request currently executing within a SQL Server instance. It won't provide any information for sessions or connections that are not actively executing.
Here's your script modified to use the most_recent_sql_handle instead:
SELECT es.login_name AS 'LoginName',
DB_NAME(ssu.database_id) AS 'DatabaseName',
(es.memory_usage * 8) AS 'MemoryUsage (in KB)',
(ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
(ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
(ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
(ssu.internal_objects_dealloc_page_count * 8) AS 'Spáce Deallocated For Internal Objects (in KB)',
CASE es.is_user_process
WHEN 1 THEN 'User Session'
WHEN 0 THEN 'System Session'
END AS 'SessionType',
es.row_count AS 'RowCount',
st.text
FROM sys.dm_db_session_space_usage ssu
INNER JOIN sys.dm_exec_sessions es ON ssu.session_id = es.session_id
INNER JOIN sys.dm_exec_connections er ON ssu.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.most_recent_sql_handle) st
WHERE DB_NAME(ssu.database_id) = 'tempdb'
AND es.session_id <> @@SPID
AND es.login_name <> 'sa'
October 11, 2013 at 1:43 pm
It works George. Thank you very much !!
October 13, 2013 at 4:19 am
Just as a side note for myself, how is your tempdb configured? Is it possible that this person appears to be "abusing" it with bad queries but maybe the problems lies elsewhere....
Just a thought
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply