September 16, 2011 at 12:40 am
Hi guys,
I was just wondering if the Query Cost (relative to the batch) % in the Actual Execution Plan is always 100% Correct?
September 16, 2011 at 1:45 am
The reason for me asking this question..
I have TableA and TableA_V2..approx(900,000 records)
TableA_V2 is an exact copy of TableA except I have Stored all the Values from one of the VARCHAR(100) columns into a reference table and instead storing the Reference ID into the new SMALLINT which is in place of the VARCHAR(100) column
The reference set now has 14,000 unique entries of the data stored in the 900,000 rows
When I ran the following query..
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT New
FROM Table_A_V2
WHERE New = 13474
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT Old
FROM Table_A
WHERE Old = 'value'
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
I got the below result...
the result from STATISTICS IO and STATISTICS TIME for the first query looks to have performed better than the 2nd query.. so why would the exact same data
from TableA converted from a VARCHAR(100) to a SMALLINT with the below results have the exact same query cost?
(1487 row(s) affected)
Table 'TableA_V2'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1487 row(s) affected)
Table 'TableA'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 267 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
September 16, 2011 at 1:48 am
No, far, far from it. The costs are estimates always. They are calculated at compile time (they're used by the optimiser to work out the cheapest plan), not at run time. The cost on an actual plan is exactly the same as the cost on the estimated plan. It's an estimated cost. Always.
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
September 16, 2011 at 2:21 am
Gail is absolutely right. Percentages are always estimates, and should be taken with a grain of salt (or sometimes a whole refinery). As Kimberly tripp once told me: Actual execution plans are estimated execution plans with some actual numbers. Like Actual number of rows, and actual number of executions. All the percentages are based on the estimated number of rows, estimated number of executions and so on.
Differences between actual and estimated execution plan can have different causes. Two of the more common I've seen are statistics being out of date, and function/method calls. SQL Server tends to believe that function/method calls are cheap, they may not be. So, if you have two similar queries, where one use a multi statement table valued function (TF) and one use inline table valued function (IF), SQL Server will tend to say that TF is more efficient. So, you may have a 30/70 relative cost between TF and IF, but if you run them both and check the actual performance (in terms of IO and CPU usage), you may see that the IF is way more efficient.
I'll have a blog post demonstrating this in a few days time.
Short version: Never ever trust the percentages.
September 16, 2011 at 2:27 am
Thank you for your replying guys.. I appreciate it.
Should I be going off the STATISTICS IO(Mainly) AND STATISTICS TIME (Just as reference) Numbers instead if I was to say compare the performance of adding a new index etc
Example:
Run Query a
CREATE INDEX....
Run Query a
September 16, 2011 at 2:29 am
If you want blog posts: http://sqlinthewild.co.za/index.php/2009/02/19/estimated-and-actual-execution-plan-revisited/
http://www.scarydba.com/2009/03/19/execution-plan-estimated-operator-cost/
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
September 16, 2011 at 2:32 am
Use the costs, use statistics time, use statistics IO. Just understand then limitations.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply