October 21, 2008 at 8:40 am
Curious
In my production environment a report's performance was degrading because one of the base tables is an always-grow table. aka BarcodeScans which is currently w/o an archive plan.
From studying the execution plan in test, an added index seemed to do the job. Performance < 2 seconds for 11 plus table sproc (with dates).
However when the machine rebooted several nights later (scheduled) the stored procedure was re-compiled and performance once again was unacceptable.
Again tweaked the index and again performance improved dramatically. However I had been bitten before and did a manual sp_recompile in TEST. Immediately performance degraded.
It seems the recompile kept picking an execution plan that max'd out the IO on certain index calls. Walking through the execution plan I identified the index and added an include column which resolved the performance with or w/o a recompile.
Question: If the sproc is efficient prior to the recompile, what are some options for encouraging less IO. If I had not discovered the include column I would have tried an index hint to use an index besides the newly-created/updated index. The hope being the recompile would not use the index however execution would. Dreaming?
thanks
Daryl
October 21, 2008 at 9:13 am
Possibly the stats getting inaccurate. Run an update statistics with fullscan and see if that helps.
Post the stored proc's code?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply