asked
Statistics, indexes and recompilation
Hi! I'm trying to figure out the connection between plan recompilation and: - Index Rebuild - Index Reorganize - Update statistics If I start with SQL Server 2008 and 2008R2, I _think_ a plan is recompiled when statistics for an underlying table has been updated, and when an index on the table has been rebuilt (or created). Am I right in that assumption? What about SQL Server 2005? Does Index Reorg have anything to do with plan recompilation? Does the same rules apply for stored procedures?
I think @WilliamD basically answered this question (+1 BTW), but I want to change the emphasis to make things slightly more clear. Assuming no data definition language (DDL) changes, meaning nothing is dropped or created, then this whole question boils down to statistics. Anything that updates statistics leads to a recompile on queries that consume those statistics. A rebuild, as @Blackhawk-17 says, is basically a drop and recreate. It's not exactly, but effectively it is. Regardless, part of the process of a rebuild is to do a full scan of the index and update the statistics based on that scan. Updated statistics... recompile. A reorg of an index does nothing at all to the statistics. Because the statistics are not touched, no recompile. As data changes in the tables, thresholds are met which will cause an automatic update of statistics... recompile. If you manually update stats... recompile. It's all about statistics. That's the emphasis that I wanted to add.
I felt bad typing that one up, but everyone was talking around it, despite giving very good answers. Better and more informed than mine, for sure, but I just wanted to make the point you were both making more explicit.
Thanks! I thought updating statistics would certainly cause a recompile, but after a situation when a proc suddenly performed much slower, where updating statistics did not solve the problem but recompiling the stored procedure did solve it makes me wonder about the behaviour. In for UPDATE STATISTICS it's specifically mentioned that it causes recompile for SQL Server 2008R2 (
http://msdn.microsoft.com/en-us/library/ms187348.aspx) but that's not mentioned in the 2005 article:
http://msdn.microsoft.com/en-us/library/ms187348(v=SQL.90).aspx
I suspect they're just clarifying. Updating stats lead to recompiles in 2000 too. I'm sure it worked basically the same in 2005.
Rebuilding an index, updating stats, creating an index and any other DDL changes to objects inside an exec plan will trigger a recompile. This has to be, because the objects that were used to decide on the exec plan have been changed. The same plan may still come out of the QO, but a new plan has to be created so that the DDL changes can be considered. I'm less certain about reorgs, I don't think that they would cause recompiles (I haven't checked this though). This behaviour is pretty much the same for 2005 and above, although 2008 and above has better compilation methods for multi-statement batches (e.g. stored procs). The QO can perform statement level recompiles instead of batch level. This means that if the query plan for a select inside a 5 statement sproc needs recompiling, only that one select query plan will be recompiled. The remaining 4 statements that don't need recompiling can remain as they were.
@WillamD has pretty well covered it. Two other ways that may result in recompilations are changing (certain) SET options and memory pressure where the plan has actually been dropped from cache. As for rebuild/reorganize... rebuild makes sense in that it is a DROP/CREATE type of operation (DDL) and this would cause a statistics update. I don't think a reorganization would trigger it as the QO probably "trusts" that the B-Trees are optimized. Looking inside of them and determining if their fragmentation would exclude an index would be a little too high of overhead to be practical I would think. Add to that the fact that there would be no changes to rowmodctr and I think there would be no need to update statistics, so no need for a recompilation either. Of course this is just my theory. **EDIT** ---------- I just wanted to add why SET options can cause recompilation. The bottom line is that some SET options can cause different results, think CONCAT\_NULL\_YIELDS\_NULL and NUMERIC\_ROUNDABORT, so the query optimizer has to develop the execution plan differently.
