Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

execution plan: query cost

Posted on 2012-03-13
17
Medium Priority
?
509 Views
Last Modified: 2012-03-20
hi experts, i have this query
-- query 1
select *
from dbo.Data
where DATEADD(day, 1, ADate) > GETDATE()
go

-- query 1
select *
from dbo.Data
where ADate > DATEADD(day, -1, GetDate())
go

this is statistics

(273 row(s) affected)
Table 'Data'. Scan count 1, logical reads 1141, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(273 row(s) affected)
Table 'Data'. Scan count 1, logical reads 847, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

i attached the execution plan, my questios is: what's the mean query cost 34% and 66%
executionPLAN.jpg
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 37714113
SQL Server uses statistics, indexes details to create execution plans, based on this execution plan SQL Server will decide the best way to execute the query.

Here the cost of the query is calculated for a batch having two query, and cost of the first query related to the batch is only 34% and the second is 66%, meaning second query is expensive than the first one.
0
 

Author Comment

by:enrique_aeo
ID: 37714156
but the number of pages read is lower in the second query ... that does not mean that the second query is better?
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 37714234
by any chance, do you have an index on ADate? Because the second query is using the raw value of ADate (and not a transformed value as in the first query), SQL can better use indexes. The proof of this is that you can see in the execution plan that your second query is doing a SEEK instead of a SCAN.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:jogos
ID: 37714323
<<but the number of pages read is lower in the second query ... that does not mean that the second query is better?>>

When first query Sql must read the pages from disk. With second query pages (from same table) are still in buffer cache -> less reads

Just change both from order and the the same will happen.

To have correct statistics you must clean the cache as in next link, but don't do that on production! Only on your local system.
 http://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37714334
jogos, we can see that both queries are performing "logical reads" meaning the data is in memory even when performing the first query.
0
 
LVL 15

Assisted Solution

by:Anuj
Anuj earned 500 total points
ID: 37714343
Personally, i feel
where ADate > DATEADD(day, -1, GetDate())  is efficient than

where DATEADD(day, 1, ADate) > GETDATE()

because in second query you have used a function on ADate column, that makes the where clause un SARGABLE meaning non searchable arguments and even if your ADate is indexed, the optimizer cannot make use of the index and result in SCAN instead of SEEK.

Also for the second query most of the cost is for the lookup operation, means fetching the rest of the columns from clustered index, if you create a covered index in that table you will notice the difference.

Try the following query, this time you will find second is better; this query avoids lookup

select ADate
from dbo.Data
where DATEADD(day, 1, ADate) > GETDATE()
go


select ADate
from dbo.Data
where ADate > DATEADD(day, -1, GetDate())
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1000 total points
ID: 37714353
What we learn from the plan
ADATE is in an index -> don't use DATEADD on it in your WHERE while it will prevent that index being used (in second you see index used -> 1% off that statement)

SELECT *  -> key loockup
If you can limit the columns you select and those columns you add as 'included columns' to the index on ADATE then that costly (99%) key loockup won't be necessarry either.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37714404
<<jogos, we can see that both queries are performing "logical reads" meaning the data is in memory even when performing the first query.>>
Indeed in this case the less reads is caused by using the index, prooves that using the index is more efficient when it would be the slower physical reads.

... and both our second comments are saying the same ... in other words
0
 

Author Comment

by:enrique_aeo
ID: 37714518
this is the code
create table dbo.Data
(
      ID int not null identity(1,1),
      Placeholder char(100) not null
            constraint DEF_Data_Placeholder
            default 'This is placeholder',
      ADate datetime not null
      
      constraint PK_Data
      primary key clustered(ID)      
)
go

create nonclustered index IDX_Data_ADate
on dbo.Data(ADate)
go
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37714532
my comments are still valid. because you have an index on the ADate column, you better use a raw value to use the index in your query
0
 

Author Comment

by:enrique_aeo
ID: 37714641
I understand  this query
select *
from dbo.Data
where ADate > DATEADD(day, -1, GetDate())
go

is best based on the results of the statistics, but I do not understand the difference in % in the execution plan?
what's the mean query cost 34% and 66%

do not understand why the second query is more expensive? when you have less logical reads.

For this case led me to the results of the statistics or the execution plan?
0
 
LVL 25

Expert Comment

by:jogos
ID: 37714661
See here for info about  covered indexes
http://msdn.microsoft.com/en-us/library/ms190806.aspx

Pay attention to the peformance-loss when you do much updates on the column Placeholder, because also the index must be updated then.
0
 

Author Comment

by:enrique_aeo
ID: 37718213
Sorry experts
but I do not understand the difference in % in the execution plan?
what's the mean query cost 34% and 66%
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1000 total points
ID: 37718799
Query cost means  how long that query will take relative to total batch time (most interpretation) but it's a little more complicate than just comparing duration time.

Other example that shows conflicting indicators (but more measurements )
http://stackoverflow.com/questions/564717/measuring-query-performance-execution-plan-query-cost-vs-time-taken

Is it the estimated or actual execution plan?


For your query I repeat query 2 is very fast in finding the records in the index, but 99% of that query cost is there the key lookup. So if you can manage to make the index a covered index then you can have a huge gain.

A link that have a more typed out explenation (especialy under tooltip immage)
http://sqlserverpedia.com/wiki/Examining_Query_Execution_Plans
0
 

Author Comment

by:enrique_aeo
ID: 37724499
it is actual execution plan
0
 
LVL 25

Expert Comment

by:jogos
ID: 37725202
Covered index?
0
 

Author Comment

by:enrique_aeo
ID: 37728827
I understand that covered index solves the problem. But my question was for the percentage batch, I now understand that there are more things you should check and improve on the results, I analyze all the information provided by you to close the question, thank you very much
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question