Thursday, July 19, 2007

SQL Server 2005 tempdb tip

When upgrading to SQL server 2005 you already know that you have to rebuild your indexes and update the statistics. In addition to that, create 1 data file per CPU core for the TEMPDB (Dual core counts as 2 CPUs; logical procs (hyperthreading) do not)
This will make a big difference, SQL Server 2005 uses the tempdb different than 2000, by adding more files you will see a dramatic performance improvement for big queries

Our queries on SQL Server 2005 ran slower than on 2000, by adding more files the queries are twice as fast than on the SQL Server 2000 box now

This behaviour is documented here: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

Look at number 7 (Consider configuration of TEMPDB database)
and number 8 (Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads. )

No comments: