CODEWITHSUNDEEP
sqlsql-servertempdb
Shahzad
Shahzad

Reputation: 21

TempDB one physical datafiles per CPU core in SQL Server 2000

I know it is recommended for SQL 2005 onward but does it also apply on SQL Server 2000? Any link for reference will also be appreciated.

Upvotes: 1

Views: 1211

Answers (1)

gbn
gbn

Reputation: 432662

Any thing you read about SQL Server 2000 is probably out of date because of how technology has moved on since then.

However, this appears to be best for SQL Server 2000. But not SQL Server 2005+

  • This says SQL Server 2000 is different to SQL Server 2005+ (my bold)

Only one file group in TempDB is allowed for data and one file group for logs, however you can configure multiple files. With SQL Server 2000 the recommendation is to have one data file per CPU core, however with optimisations in SQL Server 2005/2008 it is now recommend to have 1/2 or 1/4 as many files as CPU cores. This is only a guide and TempDB should be monitored to see if PAGELATCH waits increase or decrease with each change.

On SQL Server 2000, the recommendation was one tempdb data file for each processor core. On 2005 and 2008, that recommendation persists, but because of some optimizations (see my blog post) you may not need one-to-one - you may be ok with the number of tempdb data files equal to 1/4 to 1/2 the number of processor cores.

  • SQL Server Engineers. This is interesting that it is internal MS and appears at odd with the first two articles

Now, I'd go by the first two and decide if you need to actually do anything.

As Paul Randal also says (my bold):

One of the biggest confusion points is that the SQL CAT team recommends 1-to-1, but they're coming from a purely scaling perspective, not from an overall perf perspective, and they're dealing with big customers with top-notch servers and IO subsystems. Most people are not.

Have you demonstrated that:

  • you require this?
  • have a bottleneck
  • you have separate disk arrays per file?
  • you understand TF 1118
  • ...

Upvotes: 2

Related Questions