What is the cost of a query? Is it a beard?
I used to work on a project where we discussed what
exactly the cost of a query was. You know that figured shown in execution plans.
I knew it was used to age plans in a cache but not sure what unit it actually
was. One of the guys on the project (Mike I think) decided to call them
"Beards". So we could then say that query x cost 54 beards. Its feels better
than saying just 50.
Back to the point the SQL Progamability Team have blogged on what the formula
is for the cost in the following post on changes in SP2 "3.0
Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL
Server 2005 SP2"
This does highlight a warning with the chaneg
between 2000 and 2005
"SQL Server 2005 the cost computed is
directly proportional to the memory pages for the plan, where as in SQL Server
2000 the cost computed is inversely proportional to the memory pages. Therefore
queries that take up a large number of memory pages are not cached in SQL Server
2000, but will be cached in SQL Server 2005. On an upgrade, for a workload with
large queries and limited reuse this will result in dramatic increase in plan
cache size."
I am aware of numerous people reporting such an
issue so be careful.
-