Reasons for query execution time difference ??

Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
India
These queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>

Reasons for query execution time difference ??

Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
--
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>

Reasons for query execution time difference ??

Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELEC
T TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_
QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SEL
ECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services
]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZE
R_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>

Realtive query cost in the execution plan

Hello everyone!
I have a general question about measuring query cost while tuning
queries. If I put both queries (the old one and the modified one) into
the same batch, can I then use the query cost (relative to the batch)
percentage number as a reliable way of measuring whether the performance
has improved?
I would also appreciate any links to the good articles on this matter.
Thank you,
Igor
*** Sent via Developersdex http://www.examnotes.net ***that's a good way to start - if it really unbalanced (e.g., version 1 is
90% of the total cost), then i've typically satisfied as to which is best.
however, i've found the most accurate way to measure it is to get the
actual timings over at least 10 runs. if it's a really long query, this
may not be totally feasible
e.g.
declare @.x datetime
set @.x = getdate()
-- run version 1
select datediff(ms, @.x, getdate()) as Version1_timing
set @.x = getdate()
-- run version 2
select datediff(ms, @.x, getdate()) as Version2_timing
mEmENT0m0RI wrote:
> Hello everyone!
> I have a general question about measuring query cost while tuning
> queries. If I put both queries (the old one and the modified one) into
> the same batch, can I then use the query cost (relative to the batch)
> percentage number as a reliable way of measuring whether the performance
> has improved?
> I would also appreciate any links to the good articles on this matter.
> Thank you,
> Igor
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Trey,
Thanks for your reply.
How about measuring the absolute query cost? Let's say I've added some
indexes and want to see the performance difference?
*** Sent via Developersdex http://www.examnotes.net ***|||by "absolute" do you mean what it actually uses rather than the estimate?
if so, run the queries again, but with the Show Execution Plan option
on. (CTRL-K is the shortcut).
this will show the actual query plan used - btw: you'll have to use this
option rather than the estimated query plan if temp tables are involved.
mEmENT0m0RI wrote:
> Trey,
> Thanks for your reply.
> How about measuring the absolute query cost? Let's say I've added some
> indexes and want to see the performance difference?
>
> *** Sent via Developersdex http://www.examnotes.net ***