Sumanta Roy (8/30/2009)
As you said that if I have 8 processor and I have 8 tempdb datafiles, then each processor will use one file. I just want to know, do I need to do anything to assign one file for one processor or SQL server will automatically assign one processor for one file.
You don't have to do anything.
Bear in mind, as I said, unless you're seeing contention on the allocation pages, it's unlikely that splitting TempDB will get you much, if any, performance gain
My second question is how can I assign the file size if autogrowth is off for all the files. How to measure the size?
You need to know how big your TempDB needs to be. I suggest monitoring for a few weeks, see how large the file can get under regular usage, then make your decision from there.
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