February 15, 2005 at 9:25 am
I hope someone can help with this as this doesn't make sense to me...
I have a Stored Procedure on SQL 2000 which runs various queries against another, Linked server running SQL 7.
Previously this SP would take about 1-2 seconds to complete.
Yesterday the SQL 7 server was rebooted. The same SP is now taking 30-40 seconds to complete.
I naturally turned on SQL Profiler against the SQL 7 server to see what the problem was. However - and I have tried this a countless number of times today - whenever SQL Profiler is switched on the SP returns to its normal run time (1-2 seconds). When SQL Profiler is turned off the SP reverts to 30-40 seconds. It makes no difference if SQL Profiler is run from a client or from ther server.
The result is I can't monitor the performance of the SP whilst this odd behaviour is occuring.
Does anyone have any ideas where to start investigating?
February 15, 2005 at 9:51 am
Try to run it through query analyzer and turn on the server trace option and then monitor the performance. This would give you some idea about what statement is taking the most time to execute.
February 16, 2005 at 4:34 am
Rebooting would cause data cache, proc cache to be cleared. Have you reindexed, updated stats and recompiled SP's?
Also the system statistical indexes (_WA_Sys_)would be lost, this may indicate your database may be under indexed and require additional indexes.
Does the query get quicker over time?
February 16, 2005 at 8:10 am
Thank you - both of you have pointed me in the right direction. By reindexing and updating stats (after using the server trace in Query Analyzer) everything has returned to normal.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply