Solved

execution plan: query cost

Posted on 2012-03-13
17
502 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
  • 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 125 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 125 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 250 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 250 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

685 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