I have a situation where I have a constraint on TempDB to not exceed 350 GB in size. I'm using
autogrowth 10 (percent) on TempDB, as I've read that it is supposed to be best practice on databases exceeding 500 MB.
When I used
dbcc checkdb with estimateonly
on every database, I've noticed that the estimated requirement on TempDB is much lower than I currently have. Summing the values returned by
dbcc checkdb with estimateonly gives a value of about 75 GB.
TempDB's size is currently 300 GB. I can't increase the permitted size of TempDB any further.
I've heard that our Company previously had a SQL job which shrinked TempDB automatically if it exceeded some value, but it is not used in our new environment. I am conflicted however, that being too liberal with shrinking TempDB may cause issues.
Using the information from this post, is there any major risks associated with autoshrinking TempDB to the size of, say, 200 GB if the current size exceeds 200 GB or so, seeing that
dbcc checkdb with estimateonly returned an estimated minimum size of 75 GB which would lead this to a rather high relative buffer?
I've read that is not the optimal practice to shrink TempDB, but may it be more "legimate" in this instance in reference to the estimated minimum size of TempDB?
I've made the following Query which shows, for instance,
internal object MB space,
internal object dealloc MB space,
statement text (the Query),
total elapsed time,
WITH task_space_usage AS ( SELECT dmv_tsu.session_id, dmv_tsu.request_id, SUM(dmv_tsu.internal_objects_alloc_page_count) AS alloc_pages, SUM(dmv_tsu.internal_objects_dealloc_page_count) AS dealloc_pages, dmv_es.login_name, dmv_es.host_name, dmv_es.program_name, dmv_es.is_user_process, dmv_es.total_elapsed_time, er.start_time, er.estimated_completion_time, er.command FROM sys.dm_db_task_space_usage dmv_tsu WITH (NOLOCK) INNER JOIN sys.dm_exec_sessions dmv_es ON (dmv_tsu.session_id = dmv_es.session_id) INNER JOIN sys.dm_exec_requests er ON dmv_tsu.session_id = er.session_id and dmv_tsu.request_id = er.request_id WHERE dmv_tsu.session_id <> @@SPID GROUP BY dmv_tsu.session_id, dmv_tsu.request_id,dmv_es.login_name,dmv_es.host_name,dmv_es.program_name,dmv_es.is_user_process, dmv_es.total_elapsed_time,er.start_time, er.estimated_completion_time, er.command ) 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, 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, TSU.host_name, TSU.login_name, TSU.program_name, TSU.is_user_process, TSU.total_elapsed_time, TSU.start_time, TSU.estimated_completion_time, TSU.command 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 10 DESC;
My question is:
Is it more beneficial to have a Query like this, in combination with a SQL agent job which says: `
If TempDB > maxSize then CheckTheQueryPosted, terminate all queries which is a user process
I know this is not code, but I meant something in the lines of this concept?