Tempdb - What do you need to know?

What you know?

Definition: According to Microsoft, tempdb is a system database which is a global resource to all users connected to the SQL Server Instance and holds:

1. Temporary database objects both system and user database objects like global or local database objects like table, stored procedures or cursors.
2. Internal objects that are created by SQL Instances like work tables to store intermediate result for spooling or sorting.
3. Row versions that are generated by data modifications transactions in a database that uses read-committed using row version isolation or snapshot isolation transactions
4. Row versions that are generated by data modification transactions for features such an online Index operations, multiple active result sets and after trigger.

What you don't know?

Properties: The tempdb definitions states the use of tempdb only. The properties talks about the facts on tempdb which are quite helpful while troubleshooting the issue. Knowing the facts along with the definition will help you isolate the problem.

1. Operations within tempdb are minimally logged
2. Transactions within tempdb can be rolled back
3. Tempdb is re-created every time the SQL Instance is started or restarted
4. Temporary database objects are dropped on disconnect.
5. Backup and restore are not allowed on tempdb
6. Size of tempdb can affect the performance of a db system
7. Tempdb is the most used database in any database

Restrictions: Tempdb comes with a long list of restrictions. Violating these restriction could result in a serious data lost and the Instance failure which might be hard to recover. SQL Server will throw an error and act as safety net when you try to compile these restriction. I am asking you not to use a back door to change these restrictions. This is not a complete list but I have listed few which I think are common. Visit Microsoft's document for a long list of restrictions.

1. Adding file groups
2. Backup or restoring 
3. Changing collation
4. Changing the owner, sa owns it
5. Creating a database snapshot
6. Dropping the database
7. Removing the primary file group, primary data and log file 

Moving Temp db Location: When your SQL Server Instance sends you a Temp db full alert notification, either you move the location of a tempdb to where you have more disk space or add more space to an existing location. We assume that you do not want to add more space to existing temp locations because they are on slower disk.

Company recently added a faster SAN storage therefore we are moving our temp db locations to our faster disk space. First,you need to find out the existing location and number of temporary data files. Most production Instances have more than one tempdb files. Save the query and the result set to a file as you might need it later to compare and verify the change.

Query to check Temp db file location

SELECT name, 
       physical_name AS CurrentLocation 
FROM   sys.master_files 
WHERE  database_id = Db_id(N'tempdb'); 

Output: 













Now, the current location is know but you don't know the configuration of each file. You also need to check the existing configuration of temp files and logs. We will need to ensure the configuration remains the same after moving it to different location. The query below outputs the configurations of each temp files and log. Save the query and the result set in a secure locations as you will need this later again.

USE TempDB
GO
EXEC sp_helpfile
GO

Output:

Verify the location and space of a new drive which is going to be the new home for your tempdb. One you confirm the path and the enough space. To move the locations of tempdb files just alter the location, you don't need to move the existing files physically, see note below! Make sure all of the tempdb data files and log files paths are altered. Most Instances have more than one data files.

Path Alter Syntax

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO

Stop and Restart the Instance of  SQL Server. Restarting will make the change into effect.Verify the location and the configurations of files using the queries we saved earlier. Delete the tempdb .mdf and .ldf files from the original location.

Query Syntax:

SELECT name, physical_name AS CurrentLocation, state_desc
  FROM sys.master_files
      WHERE database_id = DB_ID(N'tempdb');

Note: Why don't you have to move the existing files? Because tempdb is re-created each time the SQL Server service is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted. Until the service is restarted, tempdb continues to use the data and log files in existing location. Once you have restarted the SQL Server service, you may want to delete the old tempdb data and log files from the original location.

Tempdb Size and Configuration

Estimating The Size: You will need to have enough space allocated for tempdb in order for SQL Instance to perform faster. How do you determine the size for temp db so that you can allocated proper disk space? To estimate the correct size, you will need to run DBCC CHECKDB WITH ESTIMATE ONLY on the large user database. This inbuilt stored procedure will return result set for tempdb space needed for CHECKALLOC and CHECKTABLES in KB. The total disk space allocated should be greater or equal to the sum of size of CHECKALLOC and CHECKTABLES.

 How to Configure Temp db files?

1. Pre-size the data file equally to avoid auto growth
2. Use one data file per CPU.
3. Pre-size that data and log files to use 90% of the available disk space.
4. The log file should be twice the size of a single data file.
5. Disable auto growth on the data files.
6. Set auto-growth of the log file to hard values like 512 MB

 Best Practices for Optimization and Performance:

1. Put the tempdb database on a fast I/O
2. Never change collation from SQL Server Instance Collation
3. Sa should always be the database owner
4. Never drop the database
5. Never drop Guest user from tempdb
6. Never change the recovery Model from Simple
7. The Temp db files should reside on the separate disk from users database.
8. Size the tempdb appropriately.
9. Make each data file the same size
10. SQL Server cannot operate without Temp db, therefore it should reside on RAID 1 or more
11. Create as many files as needed to maximize disk bandwidth.Using multiple files reduces tempdb storage contentions. However do not create too many files because this can reduce performance and increase overhead. The general rule is to create one data file for each CPU and adjust the number of files up and down as necessary.
12. Provide plenty of RAM for tempdb to use
13. Mae sure queries are optimized to avoid tempdb spills
14. Monitor tempdb for contention
15. Monitor the version store for long running transactions.

Resources:

1. http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx
2. http://sqlmag.com/site-files/sqlmag.com/files/uploads/2014/01/IderaWP_Demystifyingtempdb.pdf
3. https://msdn.microsoft.com/en-us/library/ms190768.aspx