Solved

execution plan: query cost

Posted on 2012-03-13
17
488 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 69

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
 
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 69

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 69

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now