Last week, we discussed the concept of plan caching in SQL Server 2008. Today, I want to delve deeper into one particular topic that often confuses users – schema changes. Many people assume that a schema change only refers to modifications in the table structure. However, there is more to it than meets the eye.

According to a white paper by Greg Low, a schema change in SQL Server is defined as follows:

• Adding or dropping columns to a table or view
• Adding or dropping constraints, defaults, or rules to/from a table
• Adding an index to a table or an indexed view
• Dropping an index defined on a table or an indexed view (only if the index is used by the query plan in question)
• Dropping a statistic (not creating or updating!) defined on a table will cause a correctness-related recompilation of any query plans that use that table
• Updating a statistic (both manual and auto-update) will cause an optimality-related (data related) recompilation of any query plans that use this statistic

It is important to note that any of these changes will trigger a recompilation of the execution batch. To test this theory, I conducted an experiment using a small script. I created a large table and stored a procedure that accessed that table. Then, I created an index on the table and ran the procedure with and without the index. The execution plans were different, confirming that the batch was recompiled when the index on the table was modified.

Additionally, the white paper provides a script to retrieve data from the DMV related to cached plans. However, when I ran the script, the UseCount column did not reset when the QueryPlan was changed. I reached out to Dr. Greg Low for clarification, and he explained that in SQL Server 2008 (and 2005), these are statement level recompiles, not procedure level recompiles. Therefore, the UseCount values are not reflected in the changes.

In conclusion, understanding schema changes is crucial for optimizing query performance in SQL Server. By being aware of what constitutes a schema change and how it affects execution plans, developers and database administrators can make informed decisions to improve the efficiency of their SQL Server environment.

I highly recommend reading the white paper by Dr. Greg Low for a more comprehensive understanding of plan caching and schema changes in SQL Server.