TempDB Internals

TempDB Internals:

TempDB is very important DB in sql server, generally sql server automatically takes care of the storage management of the system, and we do not require bothering much about this storage, also now days we are having good/much resources (IO/CPU/Memory) so we don’t need to think much about the TempDB usage. But for the system which has very heavy activity and huge database size may have to consider the usage of tempDB and may be sometime tempDB goes out of disk. Especially after Sql server 2005 were there was a huge improvement at system includes versioning which requires heavy usage of tmepDB.  Hence now a days its very important for everyone to know what is tempDB stores and how it works ,Also what all general recommendation/consideration about tempDB.

TempDB has following properties:

1. This is the System database can only be “Simple” Recovery mode.
2. Every Sql server instance is having only one TempDB Database
3. Auto creates same as Model whenever sql server restarts
4. Initial size of TempDB data file is 8MBand log file is 512KB
5. Does not allow Auto shrink.
6. TempDB will have only one filegroup but can add any number of files.
7. Stores all temporary data means all data will lost once sql server stop/restarts
8. Cannot drop the tempDB database and cannot change the mode of if(readonly/emergency/Offline…)
9. Database snapshot is not supported.
10. Cannot take backup of TempDB.
11. Database checksum is not supported (CHECKSUM is ENABLED from sql server 2008 onwards) .
12. Objects of TempDB can be cached : if we have small temp objects that can be cached due to drop and create of frequent table (size is less then 8mb).
13. Log growth always on disk whereas for small data it keeps on memory for tempdb
14. For tempdb Log file “REDO” operation not required. After value is not logged(eg update x set y=’abc’ where y=’xyz’ here tempdb does not log ‘abc’)
15. Instance file initialization, when autogrowh happens space of growth is “Zeroed” them out. Make autogrowh fast but could cause security issue.
16. A checkpoint is only done for tempdb when the tempdb log file reaches 70% full

TempDB stores:

a)      Temporary objects

b)      Internal Objects

c) Version Stored Objects – New in sql server 2005

a) Temporary objects:

Temporary objects are objects which is created temporary using #, ## or veritable @, @@  etc.

eg. If you create a temp table #abc

create table #abc (a int,b varchar(8000),c varchar(8000))

Irrespective of the database you are using this table will create at tempDB database

select * from tempdb.sys.all_objects where name like ‘%abc%’

You will see the object name something like this

#abc__________________000000000006 (xxx –connection number)tab

So always remember when you create any temporary objects there is a cost of it as they reside at tmepDB database.

b) Internal Objects:

This is very important thing and generally most of the Developers/DBA does not aware of this. Internal objects are not visible but it uses tempDB storage. When we run a query which requires temporary object to be created to perform that operation is created in tempDB.

Eg. Restart the sql services. And check the size of TempDB data files.

Generally it will be 8mb.

Now suppose you have one big table. Here I am using charge table from credit database download from www.sqlskills.com

select * from charge order by category_no

Now once you run above select statement the size of tempDB data file grows to appx 80MB (wow) …. Howz that. As “order by” clause require creating a temp object for ordering the records from charge table because category_no does not have index on it (not ordered).

There are some other operations which generates internal objects are: Hash join, hash aggregates, DBCC check, Order By etc.

c) Version Stored Objects:

Sql server 2005 has improved the engine and enables the row versioning; Following are some of the features which use row versioning.

>> Snapshot Isolation.

>>Triggers:

In previous version of Sql server 2000 and earlier triggers used to store into log file. On sql server 2005 onwards it keeps into TempDB. After trigger store Versioning.

>>Online index Build:

When online index rebuild and at the same time any DML statements happens it stores as versioning.

Recommendations:-

For Sql Server 2005 multiple data files would be good to improve the performance of TempDB.

For 2000 number of data file better to be good as number   of CPU.

Set 80% of estimated growth, and let 20% be handed by auto growth.

TempDB Shrink can be done  at startup parameter –c –f this will start the sql server with minimum tempdb size.

Errors:

1101 or 1105: no space in tempDB

3959: Version store is full (after 1101 or 1105)

3967: Version store is force to shrink

3958 or 3966: Transaction cannot find required version record.

Troubleshooting tempDB issue:

Generally TempDB issues are TempDB FULL and tempDB performance.

Generally Tempdb will have two issues

TempDB Full:

To troubleshoot the tempDB issue we have two dmvs:

Sys.dm_db.file_space_usage

Sys.dm_db.trask_space_usage

Sys.dm_db_session_space_usage

This issue occurs due to:

Query used in application requires heavy internal objects tempdb – to solve that use nested look join instead of hash join/aggregates can be achieve using creating proper indexes on the table or use hints.

Sys,Dm_os_waiting_task – resource like (2:1:%) where % 1:PFS and 3 GAMs contention issue

Also if require don’t use snapshot isolation level, and things which usages tempDB.

Hope this will help someone to understand more about tmepDB.

Thanx.

Vinay

Reference:

Practical Troubleshooting –Ken Henderson.

http://msdn.microsoft.com/en-us/library/ms345368.aspx
http://msdn.microsoft.com/en-us/library/cc966545.aspx
http://msdn.microsoft.com/en-us/library/ms175527.aspx

http://msdn.microsoft.com/en-us/library/ms190768.aspx