January 20, 2016 at 11:06 pm
We are running SQL Server 2008 and would like to know why after a recompile the stored procedures are performing much better, although we have auto update statistics enabled as well as regular
index rebuilds .Any ideas ?
January 21, 2016 at 1:25 am
you might be suffering from parameter sniffing?
January 21, 2016 at 1:55 am
BL0B_EATER (1/21/2016)
you might be suffering from parameter sniffing?
Best article I've read on the subject - https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
January 21, 2016 at 2:16 am
You need to capture the execution plan when the query is performing well and when the query is performing badly. Compare the two plans. There differences should indicate why you're seeing performance differences.
By the way, except that an index rebuild updates the statistics, by itself an index rebuild won't change query behavior. Instead, if you're getting lots of scans, it can help general performance. It won't affect optimizer choices though.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply