June 3, 2009 at 11:40 am
I have a stored procedure in production with an estimated cost of 6,921. When I get a copy of that database and put it on a development server, the query plan shows an estimated cost of 899. I can see certain operator costs are exactly the same and others, such as a lazy spool, are 10 times more.
I couldn't find an explanation for this behavior anywhere. Does anyone here have any ideas?
Thanks a lot!
June 3, 2009 at 11:42 am
First, costs in execution plans aren't really all that useful a datum. They are often enough dead-wrong that they can be treated as virtually meaningless.
Second, the probable reason is differences in the IO system.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2009 at 12:19 pm
Costs are an estimated value base on a baseline machine from back in the 90's. They're extremely arbitrary and subject to all sorts of vagaries around the statistics of the data & indexes and all sorts of other stuff. As Gus says, they're not terribly useful for measuring performance.
Better to look to execution speed & I/O as a measure for performance and just use the execution plans as a mechanism for explaining what's going on, not as a way to see what the performance of a query is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2009 at 1:24 pm
I understand that the estimated query costs have little relation to reality. However, in our production environment, the query governor is set to 5,000 during prime time hours (7am - 9pm EDT). Since this stored procedure's estimated cost is over 6,000, it won't run during the day. That's why I care about the difference in estimated query cost between the same query/database on one server vs. another.
My understanding has always been that the estimated cost is based on data distribution, statistics, indexes, etc. - nothing that would change when moving the database to a different server. Does anyone have any information to the contrary?
Thanks again!
June 3, 2009 at 3:40 pm
Statistics & data distribution yes, but CPU, memory and disk I/O are also taken into account (although not I/O speed). You also have to deal with parallelism as a factor.
Are you sure you've got an exact copy of the database and no statistics updates or index rebuilds or defrags were run between one server and the other?
You should look at why the plan was chosen. It's available as part of the properties in the final operator for the query. If it timed out and took the best plan it had at the time, this could be a factor of CPU power & speed differences as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2009 at 4:13 pm
These look like big numbers which vary a lot.
Big numbers which vary a lot usually mean the SQL Server optimiser has difficulty optimising the query.
When I see this level of variation I simplify the query, which invariably provides a more consistent and efficient plan.
Tim
.
June 4, 2009 at 6:46 am
Thanks for the information. I didn't realize hardware was a factor in estimating query costs. That's kind of ironic, because the production server is 64-bit Windows 2003, SQL Server 2005, with 32GB RAM and 4 dual core cpu's. Our development server is a single CPU, virtual image, with 1GB RAM allocated to it.
Anyway, thanks again. I think I'll follow Tim's advice and break up the monster query inside this stored proc. I was headed down this path already, but the huge variation in cost estimates had me baffled.
Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply