execution plan: query cost

enrique_aeo
enrique_aeo used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AnujSQL Server DBA
Top Expert 2011

Commented:
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.

Author

Commented:
but the number of pages read is lower in the second query ... that does not mean that the second query is better?
Senior .Net Consultant
Top Expert 2016
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
<<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/
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
jogos, we can see that both queries are performing "logical reads" meaning the data is in memory even when performing the first query.
AnujSQL Server DBA
Top Expert 2011
Commented:
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())
Commented:
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.

Commented:
<<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

Author

Commented:
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
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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

Author

Commented:
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?

Commented:
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.

Author

Commented:
Sorry experts
but I do not understand the difference in % in the execution plan?
what's the mean query cost 34% and 66%
Commented:
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

Author

Commented:
it is actual execution plan

Commented:
Covered index?

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial