3

We have a 60 GB production database in my new organization. We run closely 500 reports overnight from this DB. I notice that all the report scripts create tables in TempDB and then populate the final report. TempDB size is 6 GB. There is no dependency set up for these report scripts, which are called from PowerShell.

Is it a good practice to use TempDB extensively in this manner? Or is it better to create all the staging tables in the production database itself and drop them after the report is generated?

Thanks, Roopesh

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
roopesh parambath
  • 41
  • 6
  • There is nothing wrong with using tempdb (explicitely). Often tempdb is used implicitely (ie outside of your control). – TT. Dec 17 '15 at 12:28

1 Answers1

2

Temporary tables always gets created in TempDb. However, it is not necessary that size of TempDb is only due to temporary tables. TempDb is used in various ways

  • Internal objects (Sort & spool, CTE, index rebuild, hash join etc)
  • User objects (Temporary table, table variables)
  • Version store (AFTER/INSTEAD OF triggers, MARS)

So, as it is clear that it is being use in various SQL operations so size can grow due to other reasons also. However, in your case if your TempDb has sufficient space to operate normally and if your internal process is using TempDb for creating temporary tables and it is not an issue. You can consider TempDb as an toilet for SQL Server.

You can check what is causing TempDb to grow its size with below query

SELECT
 SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
 SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
 SUM (version_store_reserved_page_count)*8  as version_store_kb,
 SUM (unallocated_extent_page_count)*8 as freespace_kb,
 SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

if above query shows,

  • Higher number of user objects then it means that there is more usage of Temp tables , cursors or temp variables
  • Higher number of internal objects indicates that Query plan is using a lot of database. Ex: sorting, Group by etc.
  • Higher number of version stores shows Long running transaction or high transaction throughput

You can monitor TempDb via above script and identify the real cause of its growth first. However, 60 GB is quite a small database with 6GB TempDB size is fairly acceptable.

Part of above answer is copied from my other answer from SO.

Anuj Tripathi
  • 2,251
  • 14
  • 18