March 18, 2008 at 12:51 pm
In attempting to tune the code for a query I found that one method had 70% of the total cost when running them in a batch together, but noticed that the runtime was only 30% of the total.
A) Is it reasonable to use select convert(varchar(50), getdate(), 141), 'StepName' between steps to estimate how much time executing each portion takes?
B) If the above method does not introduce errors, is it then better to ignore query cost in tuning queries and rely entirely on execution time? (Provided that one can test them without inconsistent competing processes)
March 19, 2008 at 12:58 am
The cost is an estimate created by the optimiser of how expensive the query is to run. It's not just execution time, also CPU, memory and IOs.
My personal preference, when tuning queries is to use the query cost, the execution time and the number of IOs as measures of query improvement.
Bear in mind, if your statistics are off (or you're using remote queries or table variables) the cost will not be accurate as the optimiser doesn't have a good estimate of number of rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2008 at 6:48 am
I'm having a hard time grasping the significance and nature of cost. Would it be accurate to say that the cost is representative of the extent to which it uses resources, and is only related to execution time to the extent that other queries compete with it?
March 19, 2008 at 7:08 am
srienstr (3/19/2008)
I'm having a hard time grasping the significance and nature of cost. Would it be accurate to say that the cost is representative of the extent to which it uses resources, and is only related to execution time to the extent that other queries compete with it?
Something like that. Time is a factor in measuring out units of work and the cost involving said work (which is what this is essentially trying to do). for example - a time estimate is what is used to make a determination as to whether a parallel plan should be used over a serial plan.
It's a strange animal though, since certain "system" things like DBCC don't even figure into the plan.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 19, 2008 at 7:09 am
Partially. Is it related to estimated duration as well as to the resources used by the query execution. Look at it as an aprox measure of how much work SQL has to do to run the query. The cost is what the query optimiser uses to pick a plan from all the possible execution plans
Execution time can be prolonged by locks, waits for available resources, network delays, none of which will factor into the cost.
Also, bear in mind it is an estimate. The cost is calculated before the query is run. There are things that can make the cost inaccurate. (Scalar UDFs, inaccurate or missing stats amoung others)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2008 at 8:51 am
If both queries retrieve the same data, and you ran them in a batch, there is the possibility that the 2nd query was just fetching data from the buffer pool, which was filled by the 1st query.
Try adding DBCC DROPCLEANBUFFERS before the start of each query, so that both queries start with an empty buffer pool.
I agree with Gail: the 3 things to measure whether a query has improved is the cost, timing and reads.
There are definitely times where SQL Server says that Query A is more 'costly' than Query B, or that Query A makes less reads than Query B, but Query B runs faster. In such cases, I have always gone with timings, as that is all users see.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply