SQL Server 2008 Tempdb

  • Article

Here are some notes on “SQL Server 2008 Tempdb” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Tempdb

User objects

  • System tables and indexes
  • User-defined temp tables, #table
  • Global temporary tables, ##table, used typically for semaphores. Typically misused.
  • Table variables, return of table-valued functions (not really in memory)
  • Scoped to session or stored procedure
  • LOB-related structures – Temporary large objects, XML
  • Worktable for sp_xml_preparedocument

Internal objects

  • Temporary large objects
  • Cursors operations
  • Spool operations
  • Work files for certain aggregate functions (hash join, hash)
  • Intermediate sort results for indexes, sort by, order by and union (as required)
  • Version stores (RSCSI, MARS, AFTER triggers)
  • See https://msdn.microsoft.com/en-us/library/ms186328.aspx
  • Service Broker (and database mail)
  • DBCC CHECK* - Version store and temporary structures

Demo – comparing temporary tables and table variables

  • Create a table variable
  • Create a temp table
  • Look at sys.dm_db_file_space_usage
  • Drop the table variable
  • Look at sys.dm_db_file_space_usage
  • Looking at sys.columns for table variable column in tempdb
  • Consider using a temp table instead of table variables (can be indexed, have stats)
  • Creating a transaction with table variable and temp table
  • Rollback the transaction
  • Look at the results. Temp table does not roll back.
  • Creating a transaction with table variable and temp table
  • Understanding TempDB, table variables v. temp tables and Improving throughput for TempDB
  • See https://www.sqlskills.com/BLOGS/KIMBERLY/post/Understanding-TempDB-table-variables-v-temp-tables-and-Improving-throughput-for-TempDB.aspx

Size

  • File sizing depending on the app
  • We are trying to avoid autogrow (consider instant file initialization)
  • It’s worse than average, since it will happen again after every restart
  • Recovery model in tempdb is SIMPLE, cannot be changed.
  • Place in a high-performance IO subsystem, with plenty of spindles
  • Striping recommended, separate from user databases.
  • See https://msdn.microsoft.com/en-us/library/ms345368.aspx 
  • SQL Server 2008: CHECKSUM enable by default, not on an upgrade.
  • See https://support.microsoft.com/kb/917047
  • We don’t care about losing the data, but we can’t live without it
  • How to move it. Redefine it! ALTER DATABASE … MODIFY FILE…
  • Number of files. Goal is to reduce content. KB used to say one per CPU.
  • It’s more like half or a quarter of the number of cores, with maximum of 8.
  • Too many files can be a problem as well.
  • Proportional fill. Make all files the same size, round robin.
  • Don’t shrink! If you must, see https://support.microsoft.com/kb/307487
  • Careful – Collation issues in tempdb – set it right when you install, can’t be changed

Typical tempdb issues

  • Out of space – Disruption of service. Look at the alerts: 1101, 1105, 3959, 3967, 3958
  • See https://msdn.microsoft.com/en-us/library/ms176029.aspx
  • Performance counters specific to monitor Tempdb
  • DMVs – find the largest objects in tempdb
  • I/O Bottlenecks – Counters, DMV queries, Look for memory issues masquerading as I/O issues.
  • Careful – Shared spindles leading to slow I/O for tempdb
  • Look at your query plans, minimize use of temp tables

Cannot do on tempdb

  • Remove primary data file or log file.
  • Add, remove or rename filegroup (or set it to READ_ONLY)
  • Drop or rename database (or set it to OFFLINE)
  • Change collation (default is the server collation), change owner (dbo), drop guest user.
  • Back up, restore or create a database snapshot.
  • Participating in database mirroring.
  • Enabling change data capture.
  • Running DBCC CHECKALLOC, DBCC CHECKCATALOG 
  • See https://msdn.microsoft.com/en-us/library/ms190768.aspx