Avatar of enrique_aeo
enrique_aeo asked on

execution plan: query cost

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
Microsoft SQL Server

Avatar of undefined
Last Comment
enrique_aeo

8/22/2022 - Mon
Anuj

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.
ASKER
enrique_aeo

but the number of pages read is lower in the second query ... that does not mean that the second query is better?
ASKER CERTIFIED SOLUTION
Éric Moreau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jogos

<<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/
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Éric Moreau

jogos, we can see that both queries are performing "logical reads" meaning the data is in memory even when performing the first query.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jogos

<<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
ASKER
enrique_aeo

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Éric Moreau

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
ASKER
enrique_aeo

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?
jogos

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
enrique_aeo

Sorry experts
but I do not understand the difference in % in the execution plan?
what's the mean query cost 34% and 66%
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
enrique_aeo

it is actual execution plan
jogos

Covered index?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
enrique_aeo

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