Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can't we rely on execution plan for performance !!! Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 4:13 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:24 AM
Points: 718, Visits: 545
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
Post #1495431
Posted Tuesday, September 17, 2013 10:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 4,351, Visits: 6,167
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
Post #1495574
Posted Tuesday, September 17, 2013 10:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
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

Post #1495588
Posted Wednesday, September 18, 2013 5:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1495872
Posted Wednesday, September 18, 2013 6:44 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:24 AM
Points: 718, Visits: 545
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 !!!!
Post #1495900
Posted Wednesday, September 18, 2013 6:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1495908
Posted Wednesday, September 18, 2013 9:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
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

Post #1495989
Posted Wednesday, September 18, 2013 11:08 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:24 AM
Points: 718, Visits: 545
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 !!

Post #1496213
Posted Thursday, September 19, 2013 4:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
IO stats from STATISTICS IO are correct, they're the actual number of reads done.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1496293
Posted Thursday, September 19, 2013 5:58 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1496341
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse