Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim