tempdb location, size and management

There is a very nice three-part post (123) from several writers, including Glenn Berry, excerpted from a John Wiley book  posted on the Confio website: LogicalRead.

The first part of the post reminds us of the value of separating transction logs, data and tempdb for SQL and then that if we are using a SAN, we probably don’t know if the logical and physical separation will make any difference.  The housekeeping of having a separate volume for logs, making it easier to monitor and manage, still might apply.  If we have a physical server, we might choose to put logs on a local disk, but if we have a cluster, we have to put all the data files on the shared storage.

However, a cluster failover is effectively a restart of the server, including any playforward of logs to update the new server so sharing  tempdb was not logical, since on restart, tempdb is recreated. In MS SQL 2008 and R2,  shared was still the supported configuration.  On MS SQL 2012, we can put tempdb on a local (SSD?) disk to both increase I/O for tempdb and to offload some of that work from the SAN.

The physical paths for the tempdb file(s) and log(s) will have to be identical between machines – same drive ID and path.

the default install of SQL will create an 8MB tempdb file, 1MG log with 10% autogrowth.  Typically not the best sizes, especially the percentage  growth.  A tempdb that is in autogrowth is not usable – your system is not updating, and there can be fragmentation.  If we don’t know the ‘right’ size but doubt is is 8/1, we can change the settings in SSMS.

  1. If tempdb has it own disk, make it very large – almost filling the disk. There will be no contention with other processes
  2. Ideally we should never have to autogrow our database or logs, but as a safety matter, we should leave it on.  Autogrowh should be to a filesize, not to a percentage.  Or else the last time it autogrows will be the very slowest and most painful event.  If it is smaller and fixed, we can  monitor it and see something different then “your disk is full’.
  3.  Instant File Initialization (IFI)  should be on to make tempdb growth faster.  This works if the SQL account is admin or if you have set gpo for the working account to have the right permissions (Manage Volume Maintenance).  It doesn’t help with log files.  Consider autogrowth of 50-500 as a start for tempdg and autogrowth by 10MB for the transaction logs.

Since each database has its own tempdb and since each computer (probably) has mutiple processors and cores, we can be working on different tables at the same time and the problem of one process locking/blocking another process also needing tempdb can easily occur.

We can create new tempdbs and logs from the SSMS GUI or using t-SQL

Multiple tempdb files, as many as one per core, will increase I/O efficiency. One file per core is probably too many – there are formula to get the right number.  The should be the same size, since MS SQL will do a proportional fill algorithm to pick the file to write to.  If they are the same, we get the efficient “round robin” approach.  On a big server, 8 is a good place to start – you can always add more if there is contention.


SQLTips author and blogber Paul Randall also speaks to this issue in the Accidental DBA series posting 27/30.   The word according to Paul is that tempdb contention is not a volume I/O issue nor a general blocking issue, it has to do with multiple threads attempting to access allocation pages in memory. The tempdb database has lots of temporary tables – and each time a new table is created (or an old one destroyed) the PFS page must be accessed to record this.   Only one thread at a time can use the table – therefore it is probable that something is always waiting.  He has a query to monitor this using the sys.dm_os_waiting_tasks DMV.

He talks about (1.) not using tempdb or the effects (all good?) of turning on (2.)  trace flag 1118.  You also can reduce contention by creating (3.) multiple tempdb databases and therefore multiple PFS pages to write to.  Create 8 tempdb, possibly reduce contention to 1/8 of the former.  Bob Ward says make as many pages as you have  logical cores (or up to 8) and then only 8 for bigger servers until you see evidence of contention.

He has a screen shot of increased throughput (transactions/sec)  gained by increasing the number of tempdb databases – one would assume that the dmv would also show fewer pagelatch_xx wait states.


tempdb is used for temporary user objects (temp tables, table variaables, cursors), work tables (sorts, index rebuilds, hash joins, spills, temp LOB storage, CTEs and Table valued expressions) and version storages for modifications  (read-committed snapshots, snapshot isolation, triggers, MARS).  Idera (ref) has a query to sys.dm_db_file_space_usage to get how much in each category.

When a page is needed for a new tempdb table, the Page Free Space (PFS) table needs to be updated (and updated on removal), One PFS page for each 500MB of data file.

Task waiting for PageIOLatch or PageLatch are experiencing contention – the resource descriptor points to the page.  If

page ID = 1 or (Page ID%8088) = 1, contention allocation page (PFS)
page ID = 2 or (Page ID%511232) = 2, contention for allocation page (GAM)
page ID = 3 or ((Page ID -1)%511232= 3, contention for allocation page (SGAM).

Avoid allocation page contention by proper configuration of tempdb table:  create 8 and monitor.  The maximum number of tempdb files than can be used is the number of concurrent processes that need tempdb files which can’t be mroe than one per logical CPU.

Writing to multiple tempdb files involves the size and free space in the files.  All should be the same size and have the same free space.  SQL will not control this – still need to monitor and presize to reduce growth events (autogrowth will occur on the largest file)