Saturday, February 1, 2014

Best practice to tuning tempdb

Tempdb on sql server is heavily used by:


- Repeated create and drop of temporary tables (local or global).

- Table variables that use tempdb for storage purposes.

- Work tables associated with CURSORS.

- Work tables associated with an ORDER BY clause.

- Work tables associated with an GROUP BY clause.

- Work files associated with HASH PLANS.


Common tuning techniques are:


- create same number of data files as # of physical CPU core to reduce PFS contention

- make then equal size to match internal round-robin algorithm 

- do not rely on auto growth, preallocate space

- using trace flag 1118 to reduce SGAM contention. (Refer to http://support.microsoft.com/kb/328551)