September 17, 2013 at 4:13 am
Hi all,
I have a heap table USER_COUNT_MONITOR with a single column ID and DISTINCT values from 1 to 100000 (Total records = 100000).
Now I'm trying to fetch 2nd highest value using these two queries:
1. Select ID from USER_COUNT_MONITOR
order by ID desc
offset 1 row
fetch next 1 rows only
2. SELECT MAX(ID)
FROM USER_COUNT_MONITOR
WHERE ID < (SELECT MAX(ID) FROM USER_COUNT_MONITOR)
I/O Stats are as below:
Query 1. Table 'user_count_monitor'. Scan count 1, logical reads 489.
Query 2. Table 'user_count_monitor'. Scan count 2, logical reads 978.
I/O stats clearly show that 1st query is performing 2 times better then 2nd query.
But execution plan shows that 2nd query is performing 80% better then 1st query.
Now, I create this index on the table :
CREATE NONCLUSTERED INDEX [test_idx] ON [dbo].[user_count_monitor] ([id])
New I/O Stats are as below:
Query 1. Table 'user_count_monitor'. Scan count 1, logical reads 2.
Query 2. Table 'user_count_monitor'. Scan count 2, logical reads 176.
New I/O stats still shows that 1st query is performing much better then 2nd query.
But now, new execution plan show that 1st query is performing 95% better then 2nd query.
In both cases, I/O stats are giving almost same picture, But execution plan is totally different.
Does it mean that we can't depend on execution plan for performance !!!
Regards,
Ashish
September 17, 2013 at 10:18 am
There are SOOOO many things that come into play with the term "query performance". And which one(s) of those things is MOST important for a GIVEN QUERY EXECUTION can literally change from execution to execution (not just from query to query or server to server). Here is just a very short list of things that could be important: locks taken, blocking potential, tempdb useage, CPU usage, RAM usage, total query duration, compilation time. There are definitely more.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 17, 2013 at 10:48 am
Execution plan shows you how the query runs, not its performance characteristics.
But execution plan shows that 2nd query is performing 80% better then 1st query.
No it doesn't. It shows that the estimated cost of one is 80% lower than the estimated cost of the other. That's it.
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 18, 2013 at 5:44 am
I just recently wrote a blog post about execution plan costs[/url]. They are all estimates, not actual measures.
"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
September 18, 2013 at 6:44 am
Grant,
What I have learn is, we have a "estimated plan" and a "actual plan". When we execute a query, SQL gives us the actual plan of what it had to do, to give us the results.
But in your blog you are saying "the actual plan costs are still just estimates".
So, why SQL is not able to give us the exact costing of what it has done !!!!
September 18, 2013 at 6:57 am
T.Ashish (9/18/2013)
Grant,What I have learn is, we have a "estimated plan" and a "actual plan". When we execute a query, SQL gives us the actual plan of what it had to do, to give us the results.
But in your blog you are saying "the actual plan costs are still just estimates".
So, why SQL is not able to give us the exact costing of what it has done !!!!
The "actual" plan is just an estimated plan with a few run-time metrics including things like: number of actual executions, actual rows returned, actual rebinds, actual rewinds, a couple of others. SQL Server doesn't measure the internals of the operations on each and every execution of a query. That would just be too costly.
"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
September 18, 2013 at 9:40 am
T.Ashish (9/18/2013)
But in your blog you are saying "the actual plan costs are still just estimates".
The costs are estimates, they are always estimates. The only thing actual about an actual plan are the actual executions and actual row counts and a couple of other actual counters.
So, why SQL is not able to give us the exact costing of what it has done !!!!
Why would it? The costs are there for the optimiser. They're how the optimiser tells which plan appears to be the best of the ones it found. It's a cost-based optimiser. Once optimisation completes and the query's execution starts, those costs are no longer needed for anything. So why would SQL go to all the expense and effort of calculating run-time costs when the costs are only needed before execution starts?
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 18, 2013 at 11:08 pm
Thanks for your explanation.
So, should I conclude that I do not have to rely on Execution Plan for optimization of my query. Instead, I should check it only for any execution warnings, index usage, look ups, table scan, etc.
And does same applies to I/O stats !!
September 19, 2013 at 4:22 am
IO stats from STATISTICS IO are correct, they're the actual number of reads done.
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 19, 2013 at 5:58 am
T.Ashish (9/18/2013)
Thanks for your explanation.So, should I conclude that I do not have to rely on Execution Plan for optimization of my query. Instead, I should check it only for any execution warnings, index usage, look ups, table scan, etc.
And does same applies to I/O stats !!
The execution plan tells you what is happening within the optimizer. It is not a measure of performance, but is, instead, an explanation of performance. And, as my blog post said, while you can't trust those numbers, they are the only ones you get, so you will use them to understand what is happening within a plan. Just don't assume that they accurately reflect reality in all cases.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply