SQL Server 2008 Tempdb

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

• Scratch database, temporary objects
• SQL uses it at times as well, like for spooling
• Only one tempdb for instance, single filegroup for data
• Careful - It is possible for one misbehaving app to create trouble in tempdb
• Does not persist across restart (goes back to dba set state upon restart)
• Careful – Make it big enough for what you need
• Object caching reduces contention
• No checkpoint in tempdb
• See https://msdn.microsoft.com/en-us/library/ms190768.aspx  
• See https://msdn.microsoft.com/en-us/library/ms345368.aspx

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