reg Stored procedure Performance

Hi ,
I am facing a typical problem with Stored procedure performance. When i
excute my stored procedure first time it is taking around 60 seconds which i
s
not meeting expectations. when i excute the same sp second time with same
scenario it takes around 30 to 35 seconds. after this this behaviour is not
consistent. again if you ran sometimes it takes 50 seconds and sometimes it
takes 35 seconds like this. with this kind of behaviour i was not able to
figure it out my sp execution time exactly. my sp is having lot of dynamic
sql also.
Could anybody have any thoughts why the sp execution time is not consistent
for the same scenario.
Thanks in advance
BhaskarBhaskar wrote:
> Hi ,
> I am facing a typical problem with Stored procedure performance. When
> i excute my stored procedure first time it is taking around 60
> seconds which is not meeting expectations. when i excute the same sp
> second time with same scenario it takes around 30 to 35 seconds.
> after this this behaviour is not consistent. again if you ran
> sometimes it takes 50 seconds and sometimes it takes 35 seconds like
> this. with this kind of behaviour i was not able to figure it out my
> sp execution time exactly. my sp is having lot of dynamic sql also.
> Could anybody have any thoughts why the sp execution time is not
> consistent for the same scenario.
> Thanks in advance
> Bhaskar
SP duration should not be your primary source of performance tuning.
While it's important, it should follow examination of the execution
plans generated by the SP and the overall CPU consumed. Duration is
dependent on many factors like overall system CPU, lock contention,
physical vs. logical disk reads, etc. CPU, Reads, and Execution Plans
should be consistent across executions with the same parameters.
What you probably have is a poorly tuned procedure that is causing a lot
of reads, likely because of missing indexes or non-sargable expressions
in the queries. If that's the case, you'll see slower execution times
when SQL Server has to go to disk to read data as opposed to getting the
same from memory.
To properly tunes the query, examine the execution plan from Profiler or
Query Analyzer. You can use Profiler to see CPU, Duration, and Reads for
the individual statements and for the overall SP. 30 and 50 seconds are
both way too long for anything but a nightly batch process. You should
strive for times in the <100ms or better if possible.
If you see Table Scan or Clustered Index Scan operations, that's likely
the problem. Examine the query and see why indexes are not used. Could
be because there are no indexes available for the query to use or
because the expressions in the query are no optimizable.
For example:
WHERE LEFT(MyTable.MyCol, 1) = 'T'
is not optimizable even if an index exists on the MyCol column. Whereas,
WHERE MyTable.MyCol = 'T'
is optimizable when a MyCol index exists (still may not be used, but it
could be).
Post the DDL for your tables, indexes, and procedure if you need
specific help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 25 Aug 2005 21:17:02 -0700, "Bhaskar"
<Bhaskar@.discussions.microsoft.com> wrote:
>I am facing a typical problem with Stored procedure performance. When i
>excute my stored procedure first time it is taking around 60 seconds which
is
>not meeting expectations. when i excute the same sp second time with same
>scenario it takes around 30 to 35 seconds. after this this behaviour is no
t
>consistent. again if you ran sometimes it takes 50 seconds and sometimes i
t
>takes 35 seconds like this. with this kind of behaviour i was not able to
>figure it out my sp execution time exactly. my sp is having lot of dynamic
>sql also.
>Could anybody have any thoughts why the sp execution time is not consistent
>for the same scenario.
There are a lot of reasons.
The most obvious is if other people are using the server, which has
only so much horsepower to split between users. Are we talking a
server-class machine here, RAID5 for the data, separate disk for the
log, gigabytes of RAM, dual processors or better?
The slow first run is because (a) the SP needs to be compiled, because
(b) the plan is not already in cache, and (c) the data is not yet
cached, either. A few physical reads and your performance goes right
out the window.
As Dave suggests, the road to wisdom starts with running profiler,
looking at plans, looking at statistics - and getting away from
dynamic SQL! And OF COURSE, making certain you have the proper
indexes.
But when you tell me the time varies, that's a pretty strong sign that
other users are contending with your performance, and likely a sign
that your server is too busy or too small.
J.