February 13, 2003 at 1:15 pm
SQL 7 Sp2 1-8 users Nt network
This is wierd and driving me crazy.
I have a stored procedure called from an ODBC
Connection. Been running this code since November. Usually it runs just fine and Profiler shows Duration ~675, Reads 20-40K
During the last week it occasionally bogs down with Duration > 3000, Reads 900K+
once it does it the first time, all users get the same results. might run for 1 hour or 6 then resets (rebuildindex sometimes works) and is back to running normal.
I have checked Recompile, Locks, Index Tuning
and cannot find the problem.
Any ideas please.
February 13, 2003 at 2:40 pm
First run
SET SHOWPLAN_TEXT ON
GO
Run the out of wack query and save output results.
GO
SET SHOWPLAN_TEXT OFF
GO
Then do the following basic maintainence next.
DBCC DBREINDEX all table indexes.
DBCC UPDATEUSAGE to insure sysindexes is up to date.
sp_updatestats to force the stats to refresh.
Again run
SET SHOWPLAN_TEXT ON
GO
Run the out of wack query and save this output results.
GO
SET SHOWPLAN_TEXT OFF
GO
Is there anything speicifc that changes before and after. Also if they are the same look at the type of actions that occurr. Some have bigger impact on tables as they grow. Table scans and such always have a negative effect.
If nothing jumps right out please post both results here and if you will post the SP code someone may notice something you have not realized in the code itself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply