perfomance related questions

Why would a stored procedure take longer to run on an OLTP environment
although %processor time doesnt seem to be pegged ( 30%), %disk time is
around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
an average but runs faster on development boxes like around 1 sec. It has
the same exact execution plan on both environments. My question is what else
do i need to look into since it doesnt seem to be hardware bound here. Using
SQL 2000. CPU and reads in trace are also higher than in our dev
environment. but the perfmon for h/w looks decent or atleast not that bad .
Any other counters to look at or suggestionsDo you have the same amount of data on both servers? For example if you
have a cursor that runs through all the records in one table and in the
development database you have only 100 records but in the production server
you have 100000 records, then I'd exepect that the stored procedure would
take longer to run on the production server. Also if you have lots of
users that are working with the database in the production, you might have
locking problems, that slow the execution.
Adi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eY8NNr7YDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Why would a stored procedure take longer to run on an OLTP environment
> although %processor time doesnt seem to be pegged ( 30%), %disk time is
> around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
> an average but runs faster on development boxes like around 1 sec. It has
> the same exact execution plan on both environments. My question is what
else
> do i need to look into since it doesnt seem to be hardware bound here.
Using
> SQL 2000. CPU and reads in trace are also higher than in our dev
> environment. but the perfmon for h/w looks decent or atleast not that bad
.
> Any other counters to look at or suggestions
>
>|||perfmon cpu is the instantaneous cpu usage, profiler cpu
is the cumulative cpu for the query, regardless of how
long it took,
unless a query takes several cpu secs, and the difference
between profiler duration and cpu is not larger, you may
not even notice the cpu in perfmon
>--Original Message--
>Assuming the stored procedure was only selecting , why
would the CPU from
>perfmon do not show it as busy yet consume more time in
CPU in profiler to
>execute the sproc. There is no parallelism. There are
some temp tables
>..There are some nested joins.
>"joe chang" <jchang6@.yahoo.com> wrote in message
>news:000d01c36401$27d19f60$a101280a@.phx.gbl...
>> what specifically are the production and development
>> systems?
>> processor, frequency, cache, #of cpu, memory, # of
disks,
>> status of hyper-threading?
>> what is in the execution plan of your stored proc ?
>> joins - what type, loop, hash, merge, row count for each
>> table, is there a temp table involved? is a parallel
plan
>> involved? if so, disabled HT or try OPTION (MAXDOP 1)
>> sometimes the dev env is JBOD (no raid) the prod env is
>> raid5, with absolutely horrible write performance, so
>> anything that involves writes will performance much
worse
>> on the $40k production system than the $1k dev system
>> >--Original Message--
>> >Why would a stored procedure take longer to run on an
>> OLTP environment
>> >although %processor time doesnt seem to be pegged (
30%),
>> %disk time is
>> >around 10% and pages/sec is 0. It takes around 10 secs
to
>> run everytime on
>> >an average but runs faster on development boxes like
>> around 1 sec. It has
>> >the same exact execution plan on both environments. My
>> question is what else
>> >do i need to look into since it doesnt seem to be
>> hardware bound here. Using
>> >SQL 2000. CPU and reads in trace are also higher than
in
>> our dev
>> >environment. but the perfmon for h/w looks decent or
>> atleast not that bad .
>> >Any other counters to look at or suggestions
>> >
>> >
>> >
>> >.
>> >
>
>.
>