0

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?

EDIT

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, is_user_process:

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?

  • 2
    The only thing you achieve with regular shrink of tempdb is that next time it has to grow it may fail because disk filled up. – Remus Rusanu May 15 '17 at 7:38
  • @RemusRusanu You put italics on regular. Does that indicate that there are other means of shrinking the TempDB with better results? I'm updating my OP now with a Query followed by a question. – Cenderze May 17 '17 at 7:46
  • There could be extraordinary circumstances when shrink is needed. Say, after a some operation gone haywire tempdb grows to XXX Gb and it needs to be brought back to operational size. – Remus Rusanu May 17 '17 at 8:15
  • @RemusRusanu Sorry for late response. That is exactly the type of situation where I wanted it to be done. If, for instance, some user make a very big Query and leave it to work over night, I'd like to be able to stop the Query, shrink TempDB automatically during the night instead of allowing issues with servers (in my case, there has been problems with analysis server being unable to be connected to due to Little disk space left). – Cenderze May 24 '17 at 12:52
6

There are a lot of common misconceptions when it comes to tempdb and shrinking databases.

For starters, if tempdb grows beyond 350 GB, it's because it needs to. With that kind of size growth, I'd say there's an ETL job or manual batches that aren't behaving well. You could fix that in a number of ways, the best of which are (matter of my opinion)

  • Fix the queries that are eating up your tempdb: look for huge Hash Match or Sort operations without proper indexes or even row versioning if you're doing Read Committed Snapshot Isolation.
  • Add more disk space. In most environments, this is just a matter of giving your SAN admin a call.

Normally, I would recommend putting tempdb on its own disk(s) and size the database from the start, so you'll avoid the autogrow thing entirely. Autogrowing by 10 percent is often really bad practice for two reasons:

  • When the database is small, autogrowth can be very frequent, and also not aligned with your physical storage sectors
  • With larger databases, the files will grow by quite a lot once they do.

Another issue with shrinking tempdb is that SQL Server keeps a lot of stuff in there, preventing it from shrinking. You'll find that even if you try to shrink tempdb, it just won't, unless you pretty much restart the server (either literally or by clearing all sorts of buffers, both of which can have a dramatic impact on your production environment).

In summary

  • Don't autoshrink, make sure there's enough space from the start.
  • Consider sizing tempdb from the start so it won't have to autogrow.
  • See if you can tune those queries that are filling your tempdb. They'll probably also be the ones that take the most time to run.
  • I updated my OP with a different approach. Instead of autoshrinking TempDB, why not become actively terminate queries when the TempDB size is getting out of hands. Do you reckon this is a valid "emergency" approach? – Cenderze May 17 '17 at 7:53
  • In terms of the requirement that TempDB is big in size in my case. I have a SQL agent job which occurs every night. This job is carried out in SSIS, and uses a set of procedures to create the "raw data" in SSIS which is then used for ETL. The reason why it consists of procedures is because the data is dependant on several different databases and linked servers, but it make sense to have it in the same destination table. If I were to, say, avoid using these procedures and/or the temp tables they rely on, would you reckon that would lead to a "permanent" decrease in TempDB size? – Cenderze May 17 '17 at 8:00
  • Yes, avoiding temp tables is one of many ways to keep tempdb size down. I would also look for ways to reduce "spill", i.e. large hash and sort operations, as I've detailed in my answer. – Daniel Hutmacher May 17 '17 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.