November 2, 2012 at 1:11 am
Hi
There is one sp which have the performence problem. For resolving that problem i have created the New Index on 1 Table. After creating index the problem is resolved. Now I have alter the sp. than again the sp is taking time. than i have drop and recreate the same index. Then sp is woking fine.
What is the reason behind that. are we need to rebuild the index every time when i create the sp ? or there is some criteria ?
November 2, 2012 at 2:27 am
Most likely you're getting stale statistics and the proc is sensitive to that. Just a guess since I can't see the proc.
If that is the case, you neither need to drop and recreate nor rebuild the index, just update the stats.
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
November 2, 2012 at 2:31 am
is every time we need to update the stats whenever sp is alter?
November 2, 2012 at 2:42 am
No, not in general.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply