strange problem of stored procedure performance
-
Hi,
I am facing strange problem of stored procedure performance, I have followed the below steps:
1.EXEC usp_test, takes 12 Seconds (First time Call)
2.EXEC usp_test, takes 5 Seconds
3.EXEC usp_test, takes 5 Seconds
4.Rebuild All/AnyOne non-clustered index on particular one table OR (Create new non-clustered index on same table and drop it immediately)
5.EXEC usp_test, takes 7 Seconds (First time Call)
6.EXEC usp_test, takes 0 Seconds
7.EXEC usp_test, takes 0 Seconds
8.dbcc freeproccache
9.EXEC usp_test, takes 12 Seconds (First time Call)
10.EXEC usp_test, takes 5 Seconds
11.EXEC usp_test, takes 5 Seconds
After Step 4 behaviour of same SP has been changed & It perform good.
After Step 8 behaviour of same SP has been changed & It perform as it is.
Why it takes 5 Seconds on next execution ?
What role "Rebuild index" play here.
Thanks in Advance...
//
-
Steps 4 and 8 both invalidate/remove the cached plan from cache forcing the optimiser to create a new execution plan
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yeah, this is pure param sniffing. You're caching and recompiling when you do the reindex.
There are ways to even out the perf. Just lookup parameter sniffing online and you'll find plenty of resources.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply