Wednesday, March 28, 2012

Problems with Stored Procedure Call

I have developed an application that interfaces with SQL 2000 via stored procedure calls. The execution time of a particular stored procedure takes approximately 2 seconds on average, but we see spikes every so often up to 150 ~ 200 seconds. The timing of the spikes are erratic and do not correlate with any blocking or maintenance job runs. We have reviewed the stored procedure and optimized it to the nth degree and still no improvement. Any suggestions on how I can track down what is causing the spikes?

index fragmentation

database statictics

disk i/o

hope this helps,

Derek

sql

No comments:

Post a Comment