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:
Post a Comment