TempDB

Tempdb is the shared database per instance in SQL Server and it is used to store and manage temporary objects.

In a user database, transactions have the ACID attributes: atomicity, concurrency, isolation, and durability. In tempdb, transactions lose the durability attribute. Tempdb does not persist after SQL Server shuts down.

This database is used by all the other user databases. Most of the internal operations on Tempdb do not generate log records because there is no need to roll back. So, these operations are faster.

TempDB is a system database which is a shared database for the whole SQL Server instance.

There is only one TempDB database per-instance.

Re-created at startup, clone of model database, however, recovery model isn’t copied; tempdb uses simple recovery.

TempDB is used for temporary user objects like local temporary tables, global temporary tables, table variables, cursors, worktables, sorts inside query, index rebuilding operation, online indexing, table valued functions etc.

TempDB is also used for row versioning when used with snapshot isolation level or read-committed snapshot settings.

Operations within tempdb are minimally logged so that transactions on temporary objects can be rolled back.

Worktable caching is improved.

SQL Server 2005 or later caches the temporary table that is created by using a CREATE TABLE or SELECT INTO statement.

 

Recommendations for managing Tempdb

 

The tempdb files must be configured with initial size and auto-growth based on your workloads. Do not let with the default sizes.

The tempdb files must be located on RAID0 (for better performance) or RAID1 (if you need have more writes than reads) or RAID5 (if you have more reads than writes). RAID10 is the best option but no all companies can justify this.

The tempdb files must be located on separated disks to avoid contention issues and improves the performance.

Tempdb database must be created with one data file per physical processor if the quantity of CPUs is eight or less. If there are more then eight CPUs then you can start off with eight data files and after increase the number of files by four in case there was PAGELATCH contentions on one of the allocation bitmap pages (including PFS pages, GAM pages and SGAM pages) until the issue is solved, if not, add four files more, and so on.

Do not forget, Tempdb data files or log file should not grow so much in a short time, if this happens then something is working wrong, so you need to analyze the workloads performance and detect the query that you have to optimize. Please find script to check Tempdb speed below and follow instructions.

If you have any questions or comments about tempdb database please let me know.

Highly recommend using of CHECKSUM for new installations for TempDB. This is enabled from SQL Server 2008 version.

 

 

Performance Enhancements in SQL Server 2005 or later

 

The tempdb logging optimization avoids logging the “after value” in certain log records in tempdb.

Instant data file initialization works by not zeroing out the NTFS file when the file is created or when the size of the file is increased.

There is less use of the UP type page latch when allocating pages and extents in tempdb. Proportional fill has been optimized to reduce UP latch contention.

Proportional fill has been optimized to reduce UP latch contention.

 

 

The following restrictions also apply to tempdb:

The database CHECKSUM option cannot be enabled. Please cross check in recent versions of SQL Server.

A database snapshot cannot be created on tempdb.

DBCC CHECKALLOC and DBCC CHECKCATALOG are not supported.

Only offline checking for DBCC CHECKTABLE is performed.

Some of the database options cannot be modified for tempdb.

Auto shrink is not allowed for tempdb. Database shrink and file shrink capabilities are limited.

Page allocations on internal objects and Updates to internal objects do not generate log records.

Each internal object occupies at least nine pages (one IAM page and eight data pages) in tempdb.

There is now deferred drop in tempdb.

Can’t set OFFLINE, READONLY options neither can you drop tempdb database

Simple recovery model: tempdb’s log is constantly being truncated, so it can never be backed up.

Additional filegroups cannot be created.

We cannot drop the database or create snapshots on this database.

We cannot change the collation or change the ownership of this database from dbo.

The database cannot be set to OFFLINE and filegroups cannot be set to READ_ONLY.

 

How to move Tempdb

If you come across following errors in log file,
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE ‘tempdb’ IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

Get the logical filenames for tempdb and use it in NAME parameter in the below query.

sp_helpdb tempdb

or Just run the below command

Use tempdb;
sp_helpfile

We found that the location of my tempdb database files tempdev and templog is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, which is the default location.

then use this query to move tempdb


use master

go

Alter database tempdb modify file (name = tempdev, filename = 'D:\MSSQL\Data\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'D:\MSSQL\Data\templog.ldf')

go

Then we need to Restart SQL SERVER.

Now your tempdb will be started from the new drive location and you may delete the old files.

Move Tempdb – Don’ts

Don’t try and move tempdb using a backup and restore method as you will receive an error

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Don’t try and detach tempdb using sp_detach_db @dbname=’tempdb’ as you will again see an error

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

/////////////////////////////////////////

How to find Tempdb Contention

Here is the query to check for page contention in TempDB.


Select session_id,
 wait_type,
 wait_duration_ms,
 blocking_session_id,
 resource_description,
 ResourceType = Case
 When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
 When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
 When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
 Else 'Is Not PFS, GAM, or SGAM page'
 End
 From sys.dm_os_waiting_tasks
 Where wait_type Like 'PAGE%LATCH_%'
 And resource_description Like '2:%'

Good Articles

http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/

http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontention

http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/

http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/175/tempdb-page-contention.aspx

/////////////////////////////////////////////////////////////////////////////

 

Query 1 – Script to Check TempDB Speed

There is a dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write and read requests:


SELECT files.physical_name, files.name,
 stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
 stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
 FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
 INNER JOIN master.sys.master_files AS files
 ON stats.database_id = files.database_id
 AND stats.file_id = files.file_id
 WHERE files.type_desc = 'ROWS'

We’re looking for two things:

Are writes being evenly distributed between data files?
Are writes finishing in 20ms or less?
If the answer is no to either of those questions, we’ve got some performance tuning work to do.

Query 2: Code to Find the Size and Growth Percentage of the tempdb Data and Log Files


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

 

 

Unable to Shrink Tempdb Data files have grown large.

You may receive this error message.

File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty

In any case we can restart SQL instance to re-initialize tempdb.

However, its not easy to restart for critical application databases.

Restarting the SQL services is very difficult sometime,

last option that can be tried that you can reinitialize by increasing the data file size to 1 MB and then start shrink activity.

 


USE tempdb;

SELECT
 name,
 size * 8 / 1024
 FROM sys.database_files;

--Find out the size of the data files and add 1 MB.

ALTER DATABASE tempdb
 MODIFY FILE
 (NAME = 'tempdev', SIZE = <current database size + 1 MB>);

ALTER DATABASE tempdb
 MODIFY FILE
 (NAME = 'tempdev2', SIZE = <current database size + 1 MB>);

--After execute the command, DBCC SHRINKFILE should works again without any error message.

DBCC SHRINKFILE ('tempdev', 1024)

Still unable to shrink…?

The next obvious step would be to check for any open transaction on tempdb.

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

No open transactions! Alright, any process holding locks on tempdb?

select * from sys.dm_tran_locks
where resource_database_id= 2

No locks! There is sys.dm_db_session_space_usage DMV which helps to track the number of page allocation and deallocation by each session on the instance.

select * from sys.dm_db_session_space_usage
where user_objects_alloc_page_count<> 0

We can clear the user sleeping session in case we see something here.
The another key thing is, any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there on it. Execute this…

SELECT * FROM tempdb..sys.all_objects where is_ms_shipped = 0

The is_ms_shipped column would be 1 for all the system objects. Hence, we can get the detail of user objects temporarily created…

In case we get objects list with #tablename in name column. We need to run below.

DBCC FREEPROCCACHE

Note: Its not recommended to clear the procedure cache, it will force the SPROCs to be recompiled and thereby effects performance. Anyhow, If tempdb is more of a concern than the stored procedures being recompiled. We can go ahead and clear the Procedure Cache using the above mentioned DBCC.

 

Still if there is no luck

Finally

You can also use DBCC FREESYSTEMCACHE (‘ALL’) to clear the plan cache for the SQL server instance, but it would result decrease in the query performance temporarily.

DBCC FREESYSTEMCACHE (‘ALL’)
GO

You can also use DBCC FREESESSIONCACHE to flush the distributed query connection cache used by distributed queries against the SQL server instance.

DBCC FREESESSIONCACHE
GO
After execute the command, DBCC SHRINKFILE should works again without any error message.

DBCC SHRINKFILE (‘tempdev’, 1024)

 

Thanks
Great Articles

http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

http://support.microsoft.com/kb/2154845

http://www.mssqltips.com/sqlservertip/1432/tempdb-configuration-best-practices-in-sql-server/

http://www.mssqltips.com/sqlservertip/1388/properly-sizing-the-sql-server-tempdb-database/

http://dbadiaries.com/tempdb-best-practices

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: