Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - TempDB performance and strategy checklist

29 April,2011 by Jack Vamvas

TEMPDB performance is critical to SQL Server performance.  There are different uses for the SQL Server TEMPDB , and it's important to document the planned SQL activity.  I've seen many SQL Server Instances performance increase just by optimizing the TEMPDB

When thinking and planning TEMPDB  configuration  consider  TempDB purpose and how to maximise TEMPDB.

Different Uses of TEMPDB

User objects e.g tables, indices, table variables. Scope (user session or routine)

Internal objects  .e.g join work files Scope (statement)

Version Stores  to support row versioning

SQL Server Objects Using TEMPDB

Cursors

Databse Mail

DBCC CHECKDB

Index Rebuild with SORT_IN_TEMPDB

Queries

Temp tables

UDF

Version Store

Tactics for Optimizing TEMPDB

1) Create an IO profile of TEMPDB . Understand workload requirements.

2) Placement of TEMPDB .Locate the TEMPDB on a separate disk  to user databases.

3) Place the TEMPDB on the fastest IO subsystem possible in your environment.Read SQL Server – tempdb and solid state drives - SQL Server DBA

4) Sizing . Configure the TEMPDB  size  accurately particularly if performance decrease can’t be tolerated .

5) Recovery Model ,  keep it SIMPLE.

6) Set to Autogrow ,  see comment above re:Sizing. What rate of growth? TEMPDB grows raplidly , setting small growth rate may cause problems. Normally I set the auto-grow rate to 10% of the initial size. That does not mean the growth is set at 10% , it means that that if the initial size is 500 MB , the growth is set at 50 MB

7) Multiple data files for TEMPDB – 1 data file per CPU. In a tightly consolidated environment , 1  tempDB file per CPU may be a luxury.

8) Define the storage IO capability to the CPU processing capacity

9) Split TEMPDB into multiple files. Place the TEMPDB files on different drives. Depending on the disk alignment architecture – each drive is assigned to a separate LUN . If using SVC – there are different considerations. Speak to your SAN administrator about the storage architecture .

10) Configure the split TEMPDB  files as equal sizes.

Related Posts 

SQL Server – TEMPDB error - Could not allocate space for object dbo.SORT temporary run storage

Shrink tempdb without sql server restart

SQL server – 5 things SQL Server developers should know about SQL Server

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer