Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can't we rely on execution plan for performance !!!


Can't we rely on execution plan for performance !!!

Author
Message
T.Ashish
T.Ashish
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8296
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 at GMail
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17525 Visits: 32252
I just recently wrote a blog post about execution plan costs. 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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
T.Ashish
T.Ashish
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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 !!!!
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17525 Visits: 32252
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


T.Ashish
T.Ashish
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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 !!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17525 Visits: 32252
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search