query assistance: plan

Showing posts with label plan. Show all posts

Wednesday, March 21, 2012

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my test server. By tweaking the query I can make it run fast in production. Why can't I make prod act like test? The difference in execution time is 15 minutes! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should be slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch level as my production server. The data is a replica. The query execution plans are different. The production server has more ram, faster CPUs (dual), and faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated statistics on all tables involved. I ran a server configuration and schema configuration comparison with RedGate software and all items are indenticle (save ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the slow query runs on production it chews one of the CPU's @.50% for all 15 minutes. The problem has slowly gotten worse of the past month. The database is only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other uses for it. Its paired app server is under very low utilization also. As I said, by tweaking the query the query time goes from 15 minutes down to 4 seconds without changing the re
sult set. The main gotcha is that my test server runs the fast execution plan with or without tweaking the query. I need it to run fast without tweaking so my users can run ad hoc queries without me having to get involved. I know it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any thoughts or suggestions for getting MSSQL to run consistant with respect to execution plans between my two servers. Our Oracle DBA is laughing...
There are many explanations for difference in query behaviour and
execution. The same query can behave differently on different boxes
depending on configuration and general environfment specific to a
particular box.
You might want to refer to
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
Things you might want to pay attention to :-
* The tables or views the query touches - are they identical on both the
'good' (Test) and the 'bad' (Production) sql servers? Meaning, do they have
identical indexes (you can check doing a sp_help on the tables) OR do they
have the same kind of data and data distribution?
* How often are statistics updated on the good and bad sql servers? Try
doing an UPDATE STATISTICS myTable WITH FULLSCAN on each of the tables that
are accessed by this query. (You might want to do it off peak hours though).
* run the query through the Index Tuning Wizard - see if it recommends any
additional non clustered or covering indexes.
* General load on the server(s), are they the same?
Hope that helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my te
st server. By tweaking the query I can make it run fast in production. Why c
an't I make prod act like test? The difference in execution time is 15 minut
es! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should b
e slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch lev
el as my production server. The data is a replica. The query execution plans
are different. The production server has more ram, faster CPUs (dual), and
faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated s
tatistics on all tables involved. I ran a server configuration and schema co
nfiguration comparison with RedGate software and all items are indenticle (s
ave ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the
slow query runs on production it chews one of the CPU's @.50% for all 15 minu
tes. The problem has slowly gotten worse of the past month. The database is
only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other use
s for it. Its paired app server is under very low utilization also. As I sai
d, by tweaking the query the query time goes from 15 minutes down to 4 secon
ds without changing the re
sult set. The main gotcha is that my test server runs the fast execution pla
n with or without tweaking the query. I need it to run fast without tweaking
so my users can run ad hoc queries without me having to get involved. I kno
w it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any th
oughts or suggestions for getting MSSQL to run consistant with respect to ex
ecution plans between my two servers. Our Oracle DBA is laughing...There are many explanations for difference in query behaviour and
execution. The same query can behave differently on different boxes
depending on configuration and general environfment specific to a
particular box.
You might want to refer to
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
Things you might want to pay attention to :-
* The tables or views the query touches - are they identical on both the
'good' (Test) and the 'bad' (Production) sql servers? Meaning, do they have
identical indexes (you can check doing a sp_help on the tables) OR do they
have the same kind of data and data distribution?
* How often are statistics updated on the good and bad sql servers? Try
doing an UPDATE STATISTICS myTable WITH FULLSCAN on each of the tables that
are accessed by this query. (You might want to do it off peak hours though).
* run the query through the Index Tuning Wizard - see if it recommends any
additional non clustered or covering indexes.
* General load on the server(s), are they the same?
Hope that helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query execution plan different between production/test - same data

Here are a couple of things to check...
-- Does the production DB/Table(s) has the same amount of
data as the test DB/Table(s) ?
-- The amount of other transactions running at the same
time in Prod/Test.
-- Use Query Analyzer- 'Display Estimated Execution Plan'
and/or Profiler to see where the bottleneck is.

>--Original Message--
>I have a query that runs much much slower on my
production server than my test server. By tweaking the
query I can make it run fast in production. Why can't I
make prod act like test? The difference in execution time
is 15 minutes! (test - 5 seconds/prod - 15 minutes). I
need to make PROD more like TEST, or at least TEST should
be slower than PROD (what is the point of load testing?)
>My test server has the same SQL version/patch level and
OS version/patch level as my production server. The data
is a replica. The query execution plans are different. The
production server has more ram, faster CPUs (dual), and
faster disk. Production is under light utilization (<20%).
It is reindexed each night. I have updated statistics on
all tables involved. I ran a server configuration and
schema configuration comparison with RedGate software and
all items are indenticle (save ram/cpu/names of jobs).
When the slow query runs on production it chews one of the
CPU's @.50% for all 15 minutes. The problem has slowly
gotten worse of the past month. The database is only 120mb
and is the only db so far on this server. The DB server is
dedicated, there are no other uses for it. Its paired app
server is under very low utilization also. As I said, by
tweaking the query the query time goes from 15 minutes
down to 4 seconds without changing the result set. The
main gotcha is that my test server runs the fast execution
plan with or without tweaking the query. I need it to run
fast without tweaking so my users can run ad hoc queries
without me having to get involved. I know it is possible,
look at my test box!!
>I am stumped on this performance problem. Please help if
you can with any thoughts or suggestions for getting MSSQL
to run consistant with respect to execution plans. Our
Oracle DBA is laughing...
>.
>
Thank you for the ideas Dan...
-- The test db is an exact replica of production
-- I've tried the query during a period of zero utilization on the prod server with no improvement in query speed or change in the flawed execution plan.
-- I've compared the actual execution plans, not estimated. My contention is that they should be identical and they are not. In any case the prod server having two processors and more memory and faster disk should be faster. I've tried turning off the 2nd
processor so that the server configurations and databases are the same. No change in the situation.
I've used RedGate software and sp_config to compare the setups for the two machines. Other than physical characteristics and the trivial names of some DTS jobs they are the same.
"Dan" wrote:

> Here are a couple of things to check...
> -- Does the production DB/Table(s) has the same amount of
> data as the test DB/Table(s) ?
> -- The amount of other transactions running at the same
> time in Prod/Test.
> -- Use Query Analyzer- 'Display Estimated Execution Plan'
> and/or Profiler to see where the bottleneck is.
>

Tuesday, March 20, 2012

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my test server. By tweaking the query I can make it run fast in production. Why can't I make prod act like test? The difference in execution time is 15 minutes! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should be slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch level as my production server. The data is a replica. The query execution plans are different. The production server has more ram, faster CPUs (dual), and faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated statistics on all tables involved. I ran a server configuration and schema configuration comparison with RedGate software and all items are indenticle (save ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the slow query runs on production it chews one of the CPU's @.50% for all 15 minutes. The problem has slowly gotten worse of the past month. The database is only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other uses for it. Its paired app server is under very low utilization also. As I said, by tweaking the query the query time goes from 15 minutes down to 4 seconds without changing the re
sult set. The main gotcha is that my test server runs the fast execution plan with or without tweaking the query. I need it to run fast without tweaking so my users can run ad hoc queries without me having to get involved. I know it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any thoughts or suggestions for getting MSSQL to run consistant with respect to execution plans between my two servers. Our Oracle DBA is laughing...
Can you show the query? You say you tweaked it? How did you do that? Is
the data the exact same on both machines? What does the query plan look like
for the slow one compared to the fast one?
Andrew J. Kelly SQL MVP
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:33F423DF-1D9C-4227-B1F4-2CAF5142BD9C@.microsoft.com...
> I have a query that runs much much slower on my production server than my
test server. By tweaking the query I can make it run fast in production. Why
can't I make prod act like test? The difference in execution time is 15
minutes! (test - 5 seconds/prod - 15 minutes). I need to make PROD more like
TEST, or at least TEST should be slower than PROD (what is the point of load
testing?)
> My test server has the same SQL version/patch level and OS version/patch
level as my production server. The data is a replica. The query execution
plans are different. The production server has more ram, faster CPUs (dual),
and faster disk. Production is under light utilization (<20%). It is
reindexed each night. I have updated statistics on all tables involved. I
ran a server configuration and schema configuration comparison with RedGate
software and all items are indenticle (save ram/cpu/names of jobs). The prod
server has 2 cpu so I tried restricting parallelism to 1. When the slow
query runs on production it chews one of the CPU's @.50% for all 15 minutes.
The problem has slowly gotten worse of the past month. The database is only
120mb and is the only db so far on this server. The DB server is dedicated,
there are no other uses for it. Its paired app server is under very low
utilization also. As I said, by tweaking the query the query time goes from
15 minutes down to 4 seconds without changing the result set. The main
gotcha is that my test server runs the fast execution plan with or without
tweaking the query. I need it to run fast without tweaking so my users can
run ad hoc queries without me having to get involved. I know it is possible,
look at my test box!!
> I am stumped on this performance problem. Please help if you can with any
thoughts or suggestions for getting MSSQL to run consistant with respect to
execution plans between my two servers. Our Oracle DBA is laughing...
>

Query Execution Plan

I'm studying the effects of indexes on query statements and trying to understand the underlying processes such as BTree's, etc. For the Northwind table 'Orders', I left the Primary Key index alone, but put an index on the field 'ShipVia'. My question is more focused on the theory and mechanical methods behind the scenes more than specific to tuning a particular SQL statement. I want to know why SQL Server would choose to use the Primary Key clustered index and not use my newly created index to execute the following statement. Wouldn't the leaf nodes of the index point to the records located in the data pages and therefore are best retrievable through the new index?

StmtText
-
SELECT *
FROM Orders
where ShipVia = 3

(1 row(s) affected)

StmtText

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Orders].[ShipVia]=Convert([@.1])))

It does use my index when I change the query as such:

SELECT ShipVia
FROM Orders
where ShipVia = 3

(2 row(s) affected)

StmtText
--
|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[IX_Orders]), SEEK:([Orders].[ShipVia]=Convert([@.1])) ORDERED FORWARD)

(1 row(s) affected)

By the way, I did take into account the size of the table. I do understand that if the table is small enough, the benefits of an index can be lost. I tried a test in which I duplicated the records to generate over a million each. The query execution plan did not change.

The problem is two-fold: that you're issuing a SELECT * & that your predicate is probably not restrictive enough. For SQL Server to use your index in this case, it would have to look up the actual record from the clustered index for each match it finds in the secondary index. Looking up the record in the clustered index involves reading about 2-3 pages (root node and leaf node, with a possible intermediate node). So if enough rows qualify (say more than 100 rows) then it would be much more expensive to use this index because it would involve reading about 300 pages from disk.

You can check these effects yourself by forcing the index (use the with (index='[index name]') hint, see the article on FROM clause in books on-line) and using "set statistics io on". To have SQL Server use the index, either make the filter restrictive enough (very few rows have ShipVia = 3) or modify the query as you have done.

|||

I believe what you just told be totally clicked! Thank you.

I remember reading now that only clustered indexes have the actual record as the leaf node and any other indexes would simply hold an ID of the record (true?). That being said, it makes sense to not use the secondary index in many cases.

Thank you Mostafa

|||My pleasure. And yes what you said is (almost) true: secondary indexes hold the clustering key of the record if it's unique (e.g. when it's the primary key). So in your example if the primary key (and clustering key) of Orders is OrderID, then each record in the ShipVia index would hold (ShipVia, OrderID).

Query Execution Plan

" operations are good and mean an index is used.
- Index S

"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.examnotes.net> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
shows
> in
> any
>

query execution plan

Hi,
Could somebody explain me about the query execution plan in the sql
analyzer?
When i look at the execution plan i see several icons displaying 100% or
sometimes a higher percentage. Should all icons be 0% or does it depends
on the icon?Hi
If you have multiple 100%s then you probably have multiple batches. Within a
batch all the nodes should add up to 100% each percent indicates how much of
the total cost the given node costs.
You may want to read up on the topic "Graphically Displaying the Execution
Plan Using SQL Query Analyzer" in Books online.
John
"Jason" wrote:

> Hi,
> Could somebody explain me about the query execution plan in the sql
> analyzer?
> When i look at the execution plan i see several icons displaying 100% or
> sometimes a higher percentage. Should all icons be 0% or does it depends
> on the icon?
>|||John Bell wrote:
> Hi
> If you have multiple 100%s then you probably have multiple batches. Within
a
> batch all the nodes should add up to 100% each percent indicates how much
of
> the total cost the given node costs.
> You may want to read up on the topic "Graphically Displaying the Execution
> Plan Using SQL Query Analyzer" in Books online.
> John
> "Jason" wrote:
>
Hi John,
I've read BOL, but it doesn't say what's better. Should the total of the
executionplan be 0% or should some of the icons be 0%. I've some 400%
(index scan icon), but is that a good thing?|||Hi
Everything should total to around 100%, so having 400% for a single node is
confusing! Check your indexes are not fragmented and that the statistics are
up-to-date.
You should be looking at lowering the percentage as much as possible, but
having something that takes 50% of 1 minute is better than having something
that is 20% of 5 minutes!
John
"Jason" wrote:

> John Bell wrote:
> Hi John,
> I've read BOL, but it doesn't say what's better. Should the total of the
> executionplan be 0% or should some of the icons be 0%. I've some 400%
> (index scan icon), but is that a good thing?
>

Query Execution Plan

Hi all
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
Thanks
See if this helps
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.codecomments.com> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>
|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"Seek" operations are good and mean an index is used.
- Index Seek
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com
|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.codecomments.com> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
shows
> in
> any
>

Query Execution Plan

Hi all
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
ThanksSee if this helps
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.developersdex.com> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"Seek" operations are good and mean an index is used.
- Index Seek
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.developersdex.com> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> > Hi all
> > Can anyone provide me general description or link about the details
shows
> in
> > query execution plan.
> > How to use it to decide more better performance.
> > Which steps in exectuion plan are not good and need to improve. Is there
> any
> > general description on it?
> >
> >
> > Thanks
> >
> >
> >
> >
>

Query estimate cost and real run time different

I have a query, before I do any change, the estimate execute plan show me th
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?

Wednesday, March 7, 2012

Query cost and execution plan

Amin
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:40A125B3.AB8FF522@.toomuchspamalready.nl...[vbcol=seagreen]
> I assume you posted the estimations below. I would not base conclusions
> on these estimations. Whenever possible I would run the query with I/O
> count and CPU count turned on. In the end, the query (plan) with the
> lowest elapsed time is the winner...
> Gert-Jan
>
> Amin Sobati wrote:
query[vbcol=seagreen]
know
> --
> (Please reply only to the newsgroup)
|||"Amin Sobati" <amins@.morva.net> wrote in message
news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> Hugo,
> Thank you for the tips!
> Actually this is my case:
> I have created "Students" table and "StudentExam" to track the exams that
> each student has taken. I know that I must have an "Exams" table to keep
> exam IDs, but I ignore that for this stage.
You can't ignore that and expect to get any meaningful performance data.
Here's a more complete example with primary keys, referential integrity a
whole lot of students, 90% of whom have taken the SQL exam.
Both queries use exactly the same plan, and have exactly the same cost.
David
create table Students (
SID int not null identity primary key,
SName varchar(10))
create table Exams(EName varchar(10) not null primary key)
create table StudentExam (
SID int not null references Students on delete cascade,
EName varchar(10) not null references Exams on delete cascade,
constraint pk_StudentExam
primary key (SID,EName)
)
create index IX2 on StudentExam(EName)
-- inserting some records for each table
insert Students values('David')
insert Students values('Peter')
insert Students values('Sarah')
-- let the students start breeding
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Exams(EName) values('sql')
insert Exams(EName) values('asp')
insert Exams(EName) values('vb')
insert Exams(EName) values('xml')
insert Exams(EName) values('access')
--add exams for each SID,EName pair
insert StudentExam(SID,EName)
select SID, EName
from Students cross join Exams
--erase the SQL exam for 10% of the students
delete studentExam
where EName = 'sql'
and sid%10=0
-- Now suppose that I need to delete the students who have NOT taken
'sql'exam.
--These are two delete statements that i want to choose the perfect one:
set statistics IO ON
print ' QUERY 1'
begin tran
delete Students where Students.sid not in (select
StudentExam.sid from StudentExam where StudentExam.EName='sql')
rollback tran
print ' QUERY 2'
begin tran
delete Students where 'sql' not in (select
StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
rollback tran
/*
QUERY 1
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
QUERY 2
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
*/
|||David,
Thank you for the great example.
I admit that I must do everything right to get the best result, but I cannot
recognize that why a query might have more IO but lower cost(in my example).
Can I count on the cost(relative to the batch) as a criteria for choosing a
query?
Thanks,
Amin
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
that
> You can't ignore that and expect to get any meaningful performance data.
> Here's a more complete example with primary keys, referential integrity a
> whole lot of students, 90% of whom have taken the SQL exam.
> Both queries use exactly the same plan, and have exactly the same cost.
> David
>
> create table Students (
> SID int not null identity primary key,
> SName varchar(10))
> create table Exams(EName varchar(10) not null primary key)
>
> create table StudentExam (
> SID int not null references Students on delete cascade,
> EName varchar(10) not null references Exams on delete cascade,
> constraint pk_StudentExam
> primary key (SID,EName)
> )
> create index IX2 on StudentExam(EName)
> -- inserting some records for each table
> insert Students values('David')
> insert Students values('Peter')
> insert Students values('Sarah')
> -- let the students start breeding
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
>
> insert Exams(EName) values('sql')
> insert Exams(EName) values('asp')
> insert Exams(EName) values('vb')
> insert Exams(EName) values('xml')
> insert Exams(EName) values('access')
> --add exams for each SID,EName pair
> insert StudentExam(SID,EName)
> select SID, EName
> from Students cross join Exams
> --erase the SQL exam for 10% of the students
> delete studentExam
> where EName = 'sql'
> and sid%10=0
> -- Now suppose that I need to delete the students who have NOT taken
> 'sql'exam.
> --These are two delete statements that i want to choose the perfect one:
> set statistics IO ON
>
> print ' QUERY 1'
> begin tran
> delete Students where Students.sid not in (select
> StudentExam.sid from StudentExam where StudentExam.EName='sql')
> rollback tran
> print ' QUERY 2'
> begin tran
> delete Students where 'sql' not in (select
> StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> rollback tran
> /*
> QUERY 1
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> QUERY 2
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> */
>
|||The query optimizer's effort depends on the estimated cost of the query.
First, the query optimizer will search for any 'obvious' query plans. If
the estimated time of these solutions is no more than 1 or 2 seconds, it
will not look any further, and simply use that plan. These kinds of
query plans can depend on the way the query is written. However, if
there is any performance difference between different query plans, the
difference will be small.
If there is no obvious fast query plan, the full compilation phase is
started. This means (virtually) all possible query plans will be
evaluated. If this phase starts to take too long, then the it is cut
short, and the fastest plan that was found thus far will be used.
This is why you are seeing that in David's example, SQL-Server will use
the same query plan for both queries. I can add another syntax (see
below), which SQL-Server will probably use the same query plan for.
print ' QUERY 3'
begin tran
delete Students where not exists (
select 1
from StudentExam
where Students.sid=StudentExam.sid
and StudentExam.EName='sql'
)
rollback tran
So what I am basically saying, is that the differences you are seeing
are not significant.
Gert-Jan
Amin Sobati wrote:[vbcol=seagreen]
> David,
> Thank you for the great example.
> I admit that I must do everything right to get the best result, but I cannot
> recognize that why a query might have more IO but lower cost(in my example).
> Can I count on the cost(relative to the batch) as a criteria for choosing a
> query?
> Thanks,
> Amin
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> that
> 0,
> 0,
(Please reply only to the newsgroup)

Query cost and execution plan

Hi all,
I have written two queries that provide the same results(using different
types of joins.).
For choosing the better one, I pasted both of them in one script in query
analyzer and executed them.
Here is the summery of the result in execution plan:
First query:
- Query Cost: 55%
- I/O Cost: Scan count 7, logical reads 14
- Execution plan: Looks simple(a few icons..)
Second query:
- Query Cost: 45%
- I/O Cost: Scan count 9, logical reads 34
- Execution plan: Looks more complicated(a lot of icons!)
Which query will have better performance? Should I consider the query
cost(relative to the batch) or the I/O cost is more important? I don't know
that why the second query has lower cost while it has more I/O and more
complicated plan!
Thanks in advance,
AminOn Mon, 10 May 2004 21:03:39 +0430, Amin Sobati wrote:
>Hi all,
>I have written two queries that provide the same results(using different
>types of joins.).
>For choosing the better one, I pasted both of them in one script in query
>analyzer and executed them.
>Here is the summery of the result in execution plan:
>First query:
>- Query Cost: 55%
>- I/O Cost: Scan count 7, logical reads 14
>- Execution plan: Looks simple(a few icons..)
>Second query:
>- Query Cost: 45%
>- I/O Cost: Scan count 9, logical reads 34
>- Execution plan: Looks more complicated(a lot of icons!)
>Which query will have better performance? Should I consider the query
>cost(relative to the batch) or the I/O cost is more important? I don't know
>that why the second query has lower cost while it has more I/O and more
>complicated plan!
>Thanks in advance,
>Amin
Hi Amin,
With only this information to go on, I'd say use the first query. The
reason for the lower query cost might well be that some (or maybe even
all) required data is read from disk for the first query, but still
available in the cache for the second query.
You get a beter test if you issue DBCC DROPCLEANBUFFERS before each
query. This will ensure that the cache is empty.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo!
Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
which is confusing for me too!
Now, should I prefer the second query to the first?
Amin
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7jpv9097l4u4bdnm5blhd4ql72oped6f70@.4ax.com...
> On Mon, 10 May 2004 21:03:39 +0430, Amin Sobati wrote:
> >Hi all,
> >I have written two queries that provide the same results(using different
> >types of joins.).
> >For choosing the better one, I pasted both of them in one script in query
> >analyzer and executed them.
> >Here is the summery of the result in execution plan:
> >
> >First query:
> >- Query Cost: 55%
> >- I/O Cost: Scan count 7, logical reads 14
> >- Execution plan: Looks simple(a few icons..)
> >
> >Second query:
> >- Query Cost: 45%
> >- I/O Cost: Scan count 9, logical reads 34
> >- Execution plan: Looks more complicated(a lot of icons!)
> >
> >Which query will have better performance? Should I consider the query
> >cost(relative to the batch) or the I/O cost is more important? I don't
know
> >that why the second query has lower cost while it has more I/O and more
> >complicated plan!
> >
> >Thanks in advance,
> >
> >Amin
> Hi Amin,
> With only this information to go on, I'd say use the first query. The
> reason for the lower query cost might well be that some (or maybe even
> all) required data is read from disk for the first query, but still
> available in the cache for the second query.
> You get a beter test if you issue DBCC DROPCLEANBUFFERS before each
> query. This will ensure that the cache is empty.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 11 May 2004 11:37:02 +0430, Amin Sobati wrote:
>Thanks Hugo!
>Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
>which is confusing for me too!
>Now, should I prefer the second query to the first?
>Amin
Hi Amin,
Hard to say. Some other things you might try:
1. Reverse the order in which you execute the queries in your test
scripts (should not make any difference with DBCC DROPCLEANBUFFERS,
but you never know).
2. Add "select getdate()" before and after each of the two queries, so
you can compare the actual execution time.
3. Add DBCC FREEPROCCACHE as well (sorry, forgot to mention that when
I suggested DBCC DROPCLEANBUFFERS). This will force recompilation of
your queries, so you can be sure it's not executed with an old
execution plan (that might be based on outdated statistics).
4. Post the queries here. Maybe I (or someone else) can make some
comments based on that. (Do add relevant table definitions, in the
form of CREATE TABLE statements as well!)
I assume you are already aware that you should run your performance
tests on the production system or on a test system with about the same
amount and distribution of data and the same specifications, that you
should try to test when no other users or processes use SQL Server (or
other apps on the same server) and that you should make sure your
table statistics are updated before running your tests.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I assume you posted the estimations below. I would not base conclusions
on these estimations. Whenever possible I would run the query with I/O
count and CPU count turned on. In the end, the query (plan) with the
lowest elapsed time is the winner...
Gert-Jan
Amin Sobati wrote:
> Hi all,
> I have written two queries that provide the same results(using different
> types of joins.).
> For choosing the better one, I pasted both of them in one script in query
> analyzer and executed them.
> Here is the summery of the result in execution plan:
> First query:
> - Query Cost: 55%
> - I/O Cost: Scan count 7, logical reads 14
> - Execution plan: Looks simple(a few icons..)
> Second query:
> - Query Cost: 45%
> - I/O Cost: Scan count 9, logical reads 34
> - Execution plan: Looks more complicated(a lot of icons!)
> Which query will have better performance? Should I consider the query
> cost(relative to the batch) or the I/O cost is more important? I don't know
> that why the second query has lower cost while it has more I/O and more
> complicated plan!
> Thanks in advance,
> Amin
--
(Please reply only to the newsgroup)|||Gert-Jan,
Thank you for your reply. Would you please read my reply to Hugo and write
me your thoughts:)
Amin
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:40A125B3.AB8FF522@.toomuchspamalready.nl...
> I assume you posted the estimations below. I would not base conclusions
> on these estimations. Whenever possible I would run the query with I/O
> count and CPU count turned on. In the end, the query (plan) with the
> lowest elapsed time is the winner...
> Gert-Jan
>
> Amin Sobati wrote:
> >
> > Hi all,
> > I have written two queries that provide the same results(using different
> > types of joins.).
> > For choosing the better one, I pasted both of them in one script in
query
> > analyzer and executed them.
> > Here is the summery of the result in execution plan:
> >
> > First query:
> > - Query Cost: 55%
> > - I/O Cost: Scan count 7, logical reads 14
> > - Execution plan: Looks simple(a few icons..)
> >
> > Second query:
> > - Query Cost: 45%
> > - I/O Cost: Scan count 9, logical reads 34
> > - Execution plan: Looks more complicated(a lot of icons!)
> >
> > Which query will have better performance? Should I consider the query
> > cost(relative to the batch) or the I/O cost is more important? I don't
know
> > that why the second query has lower cost while it has more I/O and more
> > complicated plan!
> >
> > Thanks in advance,
> >
> > Amin
> --
> (Please reply only to the newsgroup)|||Hugo,
Thank you for the tips!
Actually this is my case:
I have created "Students" table and "StudentExam" to track the exams that
each student has taken. I know that I must have an "Exams" table to keep
exam IDs, but I ignore that for this stage.
This is the script:
----
create table Students (
SID int,
SName varchar(10))
create table StudentExam (
SID int,
EName varchar(10))
-- inserting some records for each table
insert Students values(1,'David')
insert Students values(2,'Peter')
insert Students values(3,'Sarah')
insert StudentExam values(1,'sql')
insert StudentExam values(1,'vb')
insert StudentExam values(1,'asp')
insert StudentExam values(2,'vb')
insert StudentExam values(2,'asp')
insert StudentExam values(3,'xml')
insert StudentExam values(3,'access')
--Execute this insert statement for 12 times to have around 30,000 records:
insert StudentExam select * from StudentExam
--Now creating indexes:
create clustered index IX1 on StudentExam(sid)
create index IX2 on StudentExam(EName)
-- Now suppose that I need to delete the students who have NOT taken 'sql'
exam.
--These are two delete statements that i want to choose the perfect one:
set statistics IO ON
begin tran
delete Students from StudentExam where Students.sid not in (select
StudentExam.sid from StudentExam where StudentExam.EName='sql')
rollback tran
begin tran
delete Students from StudentExam where 'sql' not in (select
StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
rollback tran
----
The first delete statement has 12 logical reads, but the second one has 38.
The cost for first one is 54%, that I think it must have lower cost because
of the IO.
If I create the second index as clustered, and the first one as
non-clustered, the logical read for second delete statement will be 14, but
no change in the main cost.
Thanks in advance,
Amin
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:r141a0dp9l5ufrlnen38mgpclkih1l6b7d@.4ax.com...
> On Tue, 11 May 2004 11:37:02 +0430, Amin Sobati wrote:
> >Thanks Hugo!
> >Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
> >which is confusing for me too!
> >Now, should I prefer the second query to the first?
> >Amin
> Hi Amin,
> Hard to say. Some other things you might try:
> 1. Reverse the order in which you execute the queries in your test
> scripts (should not make any difference with DBCC DROPCLEANBUFFERS,
> but you never know).
> 2. Add "select getdate()" before and after each of the two queries, so
> you can compare the actual execution time.
> 3. Add DBCC FREEPROCCACHE as well (sorry, forgot to mention that when
> I suggested DBCC DROPCLEANBUFFERS). This will force recompilation of
> your queries, so you can be sure it's not executed with an old
> execution plan (that might be based on outdated statistics).
> 4. Post the queries here. Maybe I (or someone else) can make some
> comments based on that. (Do add relevant table definitions, in the
> form of CREATE TABLE statements as well!)
> I assume you are already aware that you should run your performance
> tests on the production system or on a test system with about the same
> amount and distribution of data and the same specifications, that you
> should try to test when no other users or processes use SQL Server (or
> other apps on the same server) and that you should make sure your
> table statistics are updated before running your tests.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||"Amin Sobati" <amins@.morva.net> wrote in message
news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> Hugo,
> Thank you for the tips!
> Actually this is my case:
> I have created "Students" table and "StudentExam" to track the exams that
> each student has taken. I know that I must have an "Exams" table to keep
> exam IDs, but I ignore that for this stage.
You can't ignore that and expect to get any meaningful performance data.
Here's a more complete example with primary keys, referential integrity a
whole lot of students, 90% of whom have taken the SQL exam.
Both queries use exactly the same plan, and have exactly the same cost.
David
create table Students (
SID int not null identity primary key,
SName varchar(10))
create table Exams(EName varchar(10) not null primary key)
create table StudentExam (
SID int not null references Students on delete cascade,
EName varchar(10) not null references Exams on delete cascade,
constraint pk_StudentExam
primary key (SID,EName)
)
create index IX2 on StudentExam(EName)
-- inserting some records for each table
insert Students values('David')
insert Students values('Peter')
insert Students values('Sarah')
-- let the students start breeding
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Exams(EName) values('sql')
insert Exams(EName) values('asp')
insert Exams(EName) values('vb')
insert Exams(EName) values('xml')
insert Exams(EName) values('access')
--add exams for each SID,EName pair
insert StudentExam(SID,EName)
select SID, EName
from Students cross join Exams
--erase the SQL exam for 10% of the students
delete studentExam
where EName = 'sql'
and sid%10=0
-- Now suppose that I need to delete the students who have NOT taken
'sql'exam.
--These are two delete statements that i want to choose the perfect one:
set statistics IO ON
print ' QUERY 1'
begin tran
delete Students where Students.sid not in (select
StudentExam.sid from StudentExam where StudentExam.EName='sql')
rollback tran
print ' QUERY 2'
begin tran
delete Students where 'sql' not in (select
StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
rollback tran
/*
QUERY 1
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
QUERY 2
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
*/|||David,
Thank you for the great example.
I admit that I must do everything right to get the best result, but I cannot
recognize that why a query might have more IO but lower cost(in my example).
Can I count on the cost(relative to the batch) as a criteria for choosing a
query?
Thanks,
Amin
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> > Hugo,
> > Thank you for the tips!
> > Actually this is my case:
> > I have created "Students" table and "StudentExam" to track the exams
that
> > each student has taken. I know that I must have an "Exams" table to keep
> > exam IDs, but I ignore that for this stage.
> You can't ignore that and expect to get any meaningful performance data.
> Here's a more complete example with primary keys, referential integrity a
> whole lot of students, 90% of whom have taken the SQL exam.
> Both queries use exactly the same plan, and have exactly the same cost.
> David
>
> create table Students (
> SID int not null identity primary key,
> SName varchar(10))
> create table Exams(EName varchar(10) not null primary key)
>
> create table StudentExam (
> SID int not null references Students on delete cascade,
> EName varchar(10) not null references Exams on delete cascade,
> constraint pk_StudentExam
> primary key (SID,EName)
> )
> create index IX2 on StudentExam(EName)
> -- inserting some records for each table
> insert Students values('David')
> insert Students values('Peter')
> insert Students values('Sarah')
> -- let the students start breeding
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
>
> insert Exams(EName) values('sql')
> insert Exams(EName) values('asp')
> insert Exams(EName) values('vb')
> insert Exams(EName) values('xml')
> insert Exams(EName) values('access')
> --add exams for each SID,EName pair
> insert StudentExam(SID,EName)
> select SID, EName
> from Students cross join Exams
> --erase the SQL exam for 10% of the students
> delete studentExam
> where EName = 'sql'
> and sid%10=0
> -- Now suppose that I need to delete the students who have NOT taken
> 'sql'exam.
> --These are two delete statements that i want to choose the perfect one:
> set statistics IO ON
>
> print ' QUERY 1'
> begin tran
> delete Students where Students.sid not in (select
> StudentExam.sid from StudentExam where StudentExam.EName='sql')
> rollback tran
> print ' QUERY 2'
> begin tran
> delete Students where 'sql' not in (select
> StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> rollback tran
> /*
> QUERY 1
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> QUERY 2
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> */
>|||The query optimizer's effort depends on the estimated cost of the query.
First, the query optimizer will search for any 'obvious' query plans. If
the estimated time of these solutions is no more than 1 or 2 seconds, it
will not look any further, and simply use that plan. These kinds of
query plans can depend on the way the query is written. However, if
there is any performance difference between different query plans, the
difference will be small.
If there is no obvious fast query plan, the full compilation phase is
started. This means (virtually) all possible query plans will be
evaluated. If this phase starts to take too long, then the it is cut
short, and the fastest plan that was found thus far will be used.
This is why you are seeing that in David's example, SQL-Server will use
the same query plan for both queries. I can add another syntax (see
below), which SQL-Server will probably use the same query plan for.
print ' QUERY 3'
begin tran
delete Students where not exists (
select 1
from StudentExam
where Students.sid=StudentExam.sid
and StudentExam.EName='sql'
)
rollback tran
So what I am basically saying, is that the differences you are seeing
are not significant.
Gert-Jan
Amin Sobati wrote:
> David,
> Thank you for the great example.
> I admit that I must do everything right to get the best result, but I cannot
> recognize that why a query might have more IO but lower cost(in my example).
> Can I count on the cost(relative to the batch) as a criteria for choosing a
> query?
> Thanks,
> Amin
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> >
> > "Amin Sobati" <amins@.morva.net> wrote in message
> > news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> > > Hugo,
> > > Thank you for the tips!
> > > Actually this is my case:
> > > I have created "Students" table and "StudentExam" to track the exams
> that
> > > each student has taken. I know that I must have an "Exams" table to keep
> > > exam IDs, but I ignore that for this stage.
> >
> > You can't ignore that and expect to get any meaningful performance data.
> >
> > Here's a more complete example with primary keys, referential integrity a
> > whole lot of students, 90% of whom have taken the SQL exam.
> >
> > Both queries use exactly the same plan, and have exactly the same cost.
> >
> > David
> >
> >
> >
> > create table Students (
> > SID int not null identity primary key,
> > SName varchar(10))
> >
> > create table Exams(EName varchar(10) not null primary key)
> >
> >
> > create table StudentExam (
> > SID int not null references Students on delete cascade,
> > EName varchar(10) not null references Exams on delete cascade,
> > constraint pk_StudentExam
> > primary key (SID,EName)
> > )
> > create index IX2 on StudentExam(EName)
> >
> > -- inserting some records for each table
> > insert Students values('David')
> > insert Students values('Peter')
> > insert Students values('Sarah')
> >
> > -- let the students start breeding
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> >
> >
> > insert Exams(EName) values('sql')
> > insert Exams(EName) values('asp')
> > insert Exams(EName) values('vb')
> > insert Exams(EName) values('xml')
> > insert Exams(EName) values('access')
> >
> > --add exams for each SID,EName pair
> > insert StudentExam(SID,EName)
> > select SID, EName
> > from Students cross join Exams
> >
> > --erase the SQL exam for 10% of the students
> > delete studentExam
> > where EName = 'sql'
> > and sid%10=0
> >
> > -- Now suppose that I need to delete the students who have NOT taken
> > 'sql'exam.
> > --These are two delete statements that i want to choose the perfect one:
> > set statistics IO ON
> >
> >
> > print ' QUERY 1'
> > begin tran
> > delete Students where Students.sid not in (select
> > StudentExam.sid from StudentExam where StudentExam.EName='sql')
> > rollback tran
> >
> > print ' QUERY 2'
> > begin tran
> > delete Students where 'sql' not in (select
> > StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> > rollback tran
> >
> > /*
> > QUERY 1
> >
> > (1228 row(s) affected)
> >
> > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
> 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > read-ahead reads 0.
> > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > read-ahead reads 0.
> > QUERY 2
> >
> > (1228 row(s) affected)
> >
> > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
> 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > read-ahead reads 0.
> > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > read-ahead reads 0.
> > */
> >
> >
--
(Please reply only to the newsgroup)|||Thanks Gert-Jan!
Regardless of this particular scenario, I'd like to find the best way for
comparing the performance of two or more queries.
What I've been doing so far, I executed for example two queries and compared
their cost(relative to the batch). Can this cost be the factor that I must
count on?
What made me to start posting this question was that I encountered with a
query that had more IO cost than the other but less Cost. I need to know
that I must choose the first query(with less IO, more cost) or the second
query(with more IO, less cost).
Thanks,
Amin
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:40A2A554.20A1EDEE@.toomuchspamalready.nl...
> The query optimizer's effort depends on the estimated cost of the query.
> First, the query optimizer will search for any 'obvious' query plans. If
> the estimated time of these solutions is no more than 1 or 2 seconds, it
> will not look any further, and simply use that plan. These kinds of
> query plans can depend on the way the query is written. However, if
> there is any performance difference between different query plans, the
> difference will be small.
> If there is no obvious fast query plan, the full compilation phase is
> started. This means (virtually) all possible query plans will be
> evaluated. If this phase starts to take too long, then the it is cut
> short, and the fastest plan that was found thus far will be used.
> This is why you are seeing that in David's example, SQL-Server will use
> the same query plan for both queries. I can add another syntax (see
> below), which SQL-Server will probably use the same query plan for.
> print ' QUERY 3'
> begin tran
> delete Students where not exists (
> select 1
> from StudentExam
> where Students.sid=StudentExam.sid
> and StudentExam.EName='sql'
> )
> rollback tran
> So what I am basically saying, is that the differences you are seeing
> are not significant.
> Gert-Jan
>
> Amin Sobati wrote:
> >
> > David,
> > Thank you for the great example.
> > I admit that I must do everything right to get the best result, but I
cannot
> > recognize that why a query might have more IO but lower cost(in my
example).
> > Can I count on the cost(relative to the batch) as a criteria for
choosing a
> > query?
> >
> > Thanks,
> > Amin
> >
> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > >
> > > "Amin Sobati" <amins@.morva.net> wrote in message
> > > news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> > > > Hugo,
> > > > Thank you for the tips!
> > > > Actually this is my case:
> > > > I have created "Students" table and "StudentExam" to track the exams
> > that
> > > > each student has taken. I know that I must have an "Exams" table to
keep
> > > > exam IDs, but I ignore that for this stage.
> > >
> > > You can't ignore that and expect to get any meaningful performance
data.
> > >
> > > Here's a more complete example with primary keys, referential
integrity a
> > > whole lot of students, 90% of whom have taken the SQL exam.
> > >
> > > Both queries use exactly the same plan, and have exactly the same
cost.
> > >
> > > David
> > >
> > >
> > >
> > > create table Students (
> > > SID int not null identity primary key,
> > > SName varchar(10))
> > >
> > > create table Exams(EName varchar(10) not null primary key)
> > >
> > >
> > > create table StudentExam (
> > > SID int not null references Students on delete cascade,
> > > EName varchar(10) not null references Exams on delete cascade,
> > > constraint pk_StudentExam
> > > primary key (SID,EName)
> > > )
> > > create index IX2 on StudentExam(EName)
> > >
> > > -- inserting some records for each table
> > > insert Students values('David')
> > > insert Students values('Peter')
> > > insert Students values('Sarah')
> > >
> > > -- let the students start breeding
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > >
> > >
> > > insert Exams(EName) values('sql')
> > > insert Exams(EName) values('asp')
> > > insert Exams(EName) values('vb')
> > > insert Exams(EName) values('xml')
> > > insert Exams(EName) values('access')
> > >
> > > --add exams for each SID,EName pair
> > > insert StudentExam(SID,EName)
> > > select SID, EName
> > > from Students cross join Exams
> > >
> > > --erase the SQL exam for 10% of the students
> > > delete studentExam
> > > where EName = 'sql'
> > > and sid%10=0
> > >
> > > -- Now suppose that I need to delete the students who have NOT taken
> > > 'sql'exam.
> > > --These are two delete statements that i want to choose the perfect
one:
> > > set statistics IO ON
> > >
> > >
> > > print ' QUERY 1'
> > > begin tran
> > > delete Students where Students.sid not in (select
> > > StudentExam.sid from StudentExam where StudentExam.EName='sql')
> > > rollback tran
> > >
> > > print ' QUERY 2'
> > > begin tran
> > > delete Students where 'sql' not in (select
> > > StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> > > rollback tran
> > >
> > > /*
> > > QUERY 1
> > >
> > > (1228 row(s) affected)
> > >
> > > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical
reads
> > 0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads
0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > > read-ahead reads 0.
> > > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > > read-ahead reads 0.
> > > QUERY 2
> > >
> > > (1228 row(s) affected)
> > >
> > > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical
reads
> > 0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads
0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > > read-ahead reads 0.
> > > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > > read-ahead reads 0.
> > > */
> > >
> > >
> --
> (Please reply only to the newsgroup)|||Well, the query plan shows estimates of the execution times. The
estimated subtree cost of the left most element is the estimated time in
seconds for the query. So whichever query plan has the lowest estimated
cost is likely to be the fastest. However, things like locking strategy
can also play a big part on a stressed server. This is not really
expressed or taking into consideration in the query plan.
The I/O count translates into the estimated I/O cost, which is (just
like the estimated CPU cost) just one component in the total estimated
cost.
If you can choose between two query plans with the same estimated cost,
I would choose the plan that uses the least amount of resources.
Gert-Jan
Amin Sobati wrote:
> Thanks Gert-Jan!
> Regardless of this particular scenario, I'd like to find the best way for
> comparing the performance of two or more queries.
> What I've been doing so far, I executed for example two queries and compared
> their cost(relative to the batch). Can this cost be the factor that I must
> count on?
> What made me to start posting this question was that I encountered with a
> query that had more IO cost than the other but less Cost. I need to know
> that I must choose the first query(with less IO, more cost) or the second
> query(with more IO, less cost).
> Thanks,
> Amin

Query cost

In many cases, I am finding that SQL Server 2005 generates the same plan as SQL Server 2000, but the SQL 2005 costs are orders of magnitude greater than the SQL 2000. Also, the execution time and cpu time shown by SET STATISTICS IO/TIME on are greater in 2005 than they are in 2000. Are the costs not comparable across the two versions?

Sharon

Sharon,

The cost calculation is different in SQL Server 2005.
Cost is now calculated by the number of ticks (based on memory, io and context switch cost). So that may explain the cost difference.

I'll try to see if I'm getting the same behaviour with statistics io and time.

|||

Thanks, Wesley.

I see different patterns in statistics IO and I see significant increases in CPU and elapsed time for SQL Server 2005 vs. SQL Server 2000.

Can you point me to any documentation about a changed costing algorithm?

Sharon

|||

Sharon,

That is very odd indeed.
I'm going to try to find some time today because things like this get my attention :-)

This is a great article that holds some information about the cost calculation.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

hth

Query compiling time

In SQL2005 is there a way to find out just how much time CPU is using on
compiling a plan for a query and how much on executing that plan?
There is quite a lot of compiles/second (200-400/second) while running some
poorly written batches in our application. During that time, CPU is at 100%
and I would like to know if most of that CPU time is spent on compiling so I
can estimate if we can gain some speed by rewriting those queries.
Thanks.
Tom
Hi Tom,
use this statement before running your batch:
SET STATISTICS TIME ON
after executing your commands, consider the message tab.
HTH,
Leila
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>
|||BTW, you may use: DBCC FreeProcCache to clean the procedure cache in order
to get your commands recompiled each time
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>
|||Do you have a lot of cursors, dynamic sql or ADO code? Each of those can
cause problems with compiles, especially the latter two.
TheSQLGuru
President
Indicium Resources, Inc.
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>

Query compiling time

In SQL2005 is there a way to find out just how much time CPU is using on
compiling a plan for a query and how much on executing that plan?
There is quite a lot of compiles/second (200-400/second) while running some
poorly written batches in our application. During that time, CPU is at 100%
and I would like to know if most of that CPU time is spent on compiling so I
can estimate if we can gain some speed by rewriting those queries.
Thanks.
TomHi Tom,
use this statement before running your batch:
SET STATISTICS TIME ON
after executing your commands, consider the message tab.
HTH,
Leila
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>|||BTW, you may use: DBCC FreeProcCache to clean the procedure cache in order
to get your commands recompiled each time
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>|||Do you have a lot of cursors, dynamic sql or ADO code? Each of those can
cause problems with compiles, especially the latter two.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>

Query compiling time

In SQL2005 is there a way to find out just how much time CPU is using on
compiling a plan for a query and how much on executing that plan?
There is quite a lot of compiles/second (200-400/second) while running some
poorly written batches in our application. During that time, CPU is at 100%
and I would like to know if most of that CPU time is spent on compiling so I
can estimate if we can gain some speed by rewriting those queries.
Thanks.
TomHi Tom,
use this statement before running your batch:
SET STATISTICS TIME ON
after executing your commands, consider the message tab.
HTH,
Leila
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>|||BTW, you may use: DBCC FreeProcCache to clean the procedure cache in order
to get your commands recompiled each time
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>|||Do you have a lot of cursors, dynamic sql or ADO code? Each of those can
cause problems with compiles, especially the latter two.
TheSQLGuru
President
Indicium Resources, Inc.
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>

Wednesday, March 21, 2012

Query execution plan different between production/test - same data

Query execution plan different between production/test - same data

Query execution plan different between production/test - same data

Tuesday, March 20, 2012

Query execution plan different between production/test - same data

Query Execution Plan

Query Execution Plan

query execution plan

Query Execution Plan

Query Execution Plan

Query estimate cost and real run time different

Wednesday, March 7, 2012

Query cost and execution plan

Query cost and execution plan

Query cost

Query compiling time

Query compiling time

Query compiling time

query assistance

Blog Archive

About Me