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
>