TempDB for Performance
Introduction
TempDB plays a very informant role in case of Performance is concern. In this article we are trying to learn something related to TempDB. We are taking the references from Microsoft MSDN for this article.
Hope it will be informative.
When we Use the TempDB
TempDB system database is the global recourses for all users connected with SQL Server Interface. The following objects are stored in the TempDB is mentioned bellow.
User defined Objects is explicitly created by user and the scope of the user object is specific session dependent or in the scope of the routine where it is created. Here the routine means the Stored Procedure (SP), Trigger or User define Function (UDF).
The example of user define objects are mentioned bellow
· User-defined tables and indexes
· System tables and indexes
· Global temporary tables and indexes
· Local temporary tables and indexes
· Table variables
· Tables returned in table-valued functions
Internal Objects are created as necessary by the SQL Server Database Engine to process SQL Server statements. Internal objects are created and dropped within the scope of a statement.
Internal objects can be one of the following:
- Work tables for cursor or spool operations and temporary large object (LOB) storage.
- Work files for hash join or hash aggregate operations.
- Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
Version Stores is a collection of data pages that hold the data rows that are required to support the features that use row versioning. There are two version stores: a common version store and an online-index-build version store.
The version stores contain the following:
· Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.
· Row versions that are generated by data modification transactions for features such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
Managing the TempDB
So we have to take special care of TempDB to maintain performance of Database. Here Microsoft provides us some recommendation that we are going to discuss.
1. Set the recovery model of TempDB to SIMPLE. This model automatically reclaimed log space.
2. Allow for TempDB files to automatically grow as required. This allows for the file to grow until the disk is full. Try to avoid TempDB file to grow with small values as auto grow takes a certain amount of time and it is not tolerable by our application.
Here is the recommendation chart from Microsoft.
TempDB file size
|
FILEGROWTH increment
|
0 to 100 MB
|
10 MB
|
100 to 200 MB
|
20 MB
|
200 MB or more
|
10%*
|
3. Pre allocate space for all TempDB files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents TempDB from expanding too frequently, which can affect performance.
4. Create as many files as needed to maximize disk bandwidth.
5. Put the TempDB database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
6. Put the TempDB database on disks that differ from those that are used by user databases.
How to Measure the TempDB Size and Growth Pattern
SELECT name AS FileName, size*1.0/128 AS FileSizeinMB,
CASE max_size WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
FileName FileSizeinMB (No column name) GrowthValue GrowthIncrement
tempdev 8.000000 Autogrowth is on. 10 Growth value is a percentage.
templog 0.500000 Autogrowth is on. 10 Growth value is a percentage.
Hope you like it.
TempDB for Performance Part-II
Introduction
One of my article is related to TempDB for Performance, published on 28th Feb 2015. Those who are not read it yet can read it from
Now the question came to mid how to change the location of the TempDB from current location to other drive. Here in this article, I am providing the T-SQL command for that. We are not going to discuss about the performance related factors of TempDB. As you can find it form our previous article mentioned above.
In Which Drive My TempDB is Currently Located
USE tempdb
GO
EXEC sp_helpfile;
So we find that the MDF and LDF file of the temp DB is located on
C:\Program Files\Microsoft SQL Server\MSSQL11.JOYDEEPSQL12\MSSQL\DATA
How We Move the TempDB to Another Drive
Suppose we want to move the TempDB from current location to E:\TEMPDB\
USE MASTER
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'E:\TEMPDB\tempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'E:\TEMPDB\templog.ldf')
GO
Hope you like it.
