In many cases, I am finding that SQL Server 2005 generates the same plan as SQL Server 2000, but the SQL 2005 costs are orders of magnitude greater than the SQL 2000. Also, the execution time and cpu time shown by SET STATISTICS IO/TIME on are greater in 2005 than they are in 2000. Are the costs not comparable across the two versions?
Sharon
Sharon,
The cost calculation is different in SQL Server 2005.
Cost is now calculated by the number of ticks (based on memory, io and context switch cost). So that may explain the cost difference.
I'll try to see if I'm getting the same behaviour with statistics io and time.
|||Thanks, Wesley.
I see different patterns in statistics IO and I see significant increases in CPU and elapsed time for SQL Server 2005 vs. SQL Server 2000.
Can you point me to any documentation about a changed costing algorithm?
Sharon
|||Sharon,
That is very odd indeed.
I'm going to try to find some time today because things like this get my attention :-)
This is a great article that holds some information about the cost calculation.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
hth
No comments:
Post a Comment