The prior post in this series was able to fix a parameterization issue by removing dynamic SQL and replacing it with a traditional SQL statement.  But sometimes that can’t be done because dynamic SQL is a requirement.  In that case the dynamic statement can still be parameterized using 2 optional parameters to the procedure sp_ExecuteSQL. The first parameter of sp_ExecuteSQL is the only required parameter and contains the SQL statement to be executed.  What is less known is that this statement can reference variable names.  By using variable names instead of hard coded values the query execution plan used by that SQL statement is more likelyRead More →

This is the 5th in a series of posts about query plans.  The first post covered what a query plan is and how to read it.  The second post described the effort required to make a query plan.  The third entry explained how the plans are stored in a cache to be reused.  The 4th post showed how the optimizer finds appropriate plans in cache.  This post will cover how poor coding practices can lead to excessive plans being stored in the cache. Plans are found in cache to be reused only when an exact match of the SQL query is found.  This works great until constant valuesRead More →