Friday, January 28, 2011

How to reduce the allocation resource contention for tempdb

Concurrency enhancements for the tempdb database

To reduce the allocation resource contention for tempdb that is experiencing heavy usage

Solution:
1. In that SQL Server Configuration Server Propertise Add trace flag    ;-T1118
2.Increase the number of tempdb data files to be at least equal to the number of processors. Also, create the files with equal sizing( ( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this one if your sql server really want it)

Always try to test before move on to production.

No comments: