Sumanta Roy (8/30/2009)
I got one requirement to divide my tempdb data files into 8 files in sql server 2008. This is to improve the performance.
What's the reasoning behind the split?
1. Is it that all the 8 files will be used simultaneously?
Kinda. It's to reduce contention on the allocation pages. If you have 8 processors, each one will use one TempDB file instead of all using the same file
It used to be necessary on SQL 2000 with heavily used TempDB, it's much less likely to be needed on SQL 2005 and above, due to a lot of changes around how temp tables are created.
Are you seeing contention on the allocation pages in TempDB? If not, it's not necessarily going to give you a performance gain
2. Should I put autogrowth to all the 8 files i.e. one should grow until the disk becomes full?
No. To do this properly, all files must be the same size, always. That can only be guaranteed if autogrow is off. Of course, that allows the chance for problems if TempDB gets full.
4. can anyone please tell me in detail how to divide/create multiple files for tempdb with filegrowth other details?
Look up ALTER DATABASE in SQL's Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability