August 29, 2018 at 3:46 pm
All,
As per the requirement that I am working in which seems to be a pretty common scenario that we have re-visited time again where some rogue query fills up the tempDB drive. I've googled for tons of solution that gives me details on how to track tempDB usage but there isn't anything that I could lay my hands on that would provide me details on finding the culprit and killing that sessions obviously excluding the service accounts. I am trying to build a logic around it where in I can look for long running sessions but I need to make sure that I am killing the query that is actually responsible for tempDB growth. Is there any way that I can incorporate that in a code and kill that session.
Thanks
August 29, 2018 at 5:17 pm
In SQL Server 2008 Resource Governor was introduced. It allows you to control CPU, Physical IO and Memory effectively throttling run away queries.
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-2017
August 29, 2018 at 6:23 pm
Joe Torre - Wednesday, August 29, 2018 5:17 PMIn SQL Server 2008 Resource Governor was introduced. It allows you to control CPU, Physical IO and Memory effectively throttling run away queries.
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-2017
I am not sure if you understood my question...but thanks! for your time and suggestion.
August 29, 2018 at 8:55 pm
Feivel - Wednesday, August 29, 2018 3:46 PMAll,
As per the requirement that I am working in which seems to be a pretty common scenario that we have re-visited time again where some rogue query fills up the tempDB drive. I've googled for tons of solution that gives me details on how to track tempDB usage but there isn't anything that I could lay my hands on that would provide me details on finding the culprit and killing that sessions obviously excluding the service accounts. I am trying to build a logic around it where in I can look for long running sessions but I need to make sure that I am killing the query that is actually responsible for tempDB growth. Is there any way that I can incorporate that in a code and kill that session.Thanks
Okay so I got something to work on but the problem for me now is that I need to convert that sql_text into xml so that I get entire sql (it should be similar to how we get the output of sp_whoisactive for sql_text) and dump it into a table before killing the spids. Finally need to loop through the spids to kill the process.
SELECT TOP 1 --Change number accordingly
su.session_id,
ss.login_name,
ss.host_name,
rq.command,
su.task_alloc,
su.task_dealloc,
(
SELECT SUBSTRING( text,
rq.statement_start_offset / 2 + 1,
(CASE
WHEN statement_end_offset = -1 THEN
2147483647
-- LEN(CONVERT(NVARCHAR(MAX), text)) * 2
ELSE
statement_end_offset
END - rq.statement_start_offset
) / 2
)
FROM sys.dm_exec_sql_text(sql_handle)
) AS sql_text
FROM
(
SELECT su.session_id,
su.request_id,
SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS task_alloc,
SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage AS su
GROUP BY session_id,
request_id
) AS su ,
sys.dm_exec_sessions AS ss,
sys.dm_exec_requests AS rq
WHERE su.session_id = rq.session_id
AND (su.request_id = rq.request_id)
AND (ss.session_id = su.session_id)
AND su.session_id > 50 --sessions 50 and below are system sessions and should not be killed
AND su.session_id <>
(
SELECT @@SPID
) --Eliminates current user session from results
AND ss.login_name NOT IN ( 'A', 'B','C') -- Exclude service account
)
AND su.task_alloc >= 50000
ORDER BY su.task_alloc DESC;
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply