This is the final in a series of posts about query plans.  The third entry explained how the plans are stored in a cache to be reused.  This post will cover the opposite.  When are plans NOT able to be reused?  What would cause a plan to need to be compiled again? There are several reasons that a query plan would need to be compiled again, but they can be boiled down to a few popular reasons. The first one is simple.  The plan cache is stored exclusively in memory.  If there is memory pressure on the instance SQL Server will eject plans from cacheRead More →

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 →

Bryan Rebok and Brent Ozar recently opened my eyes to something I didn’t know.  When SQL Server recommends missing indexes to you it puts the columns in order in which they are found in the table.  That’s it.  I always thought there was more logic into it.  But there isn’t.  Upon reading this I had a terrible realization that I’ve made a lot of awful indexes in my time.  I owe the world an apology.  I hope this post can serve as that apology. I’ve written a function that accepts the equality column list from dm_db_missing_index_details as a parameter and spits those columns back out inRead More →

This is the 4th 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.  This post will cover how the optimizer is able to quickly determine whether a plan in the cache exists for any given query. When a query is executed the database engine quickly creates a hash of the query code.  This hash is called a query handle.  The engine then compares this handle to a listRead More →

This is the 3rd 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.  This post will cover the plan cache. If your server is running SQL Server 2016+ and you have enabled query store please STOP READING this post.  Seriously.  Query store changes and supersedes much of the information in this post.  Instead, read about query store from the expert herself, Erin Stellato. The plan cache is a list of query plans that have been compiled by the optimizer andRead More →

Thanks to everyone in attendance today at the Ohio North SQL Server User Group.  I was really impressed by the turnout tonight. Below is the blog entry Erin mentioned during the session regarding backup compression and TDE enabled databases.  I confirmed that this absolutely works and will adjust my presentation accordingly. Please find my presentation materials at the link on the top right of this page. TDE and Backup Compression: Together At Last  Read More →

June 9, 2026: T-SQL Tuesday #200 As we kick off T-SQL Tuesday #200 it feels like a good time to reflect on the last 100 months.  Did any of us realize just how good the times were back then?  2018 was amazing.  We were all so care free.  I even found this screenshot of my T-SQL Tuesday #100 post.   I remember all the jokes during T-SQL Tuesday #100 about how auto tuning would put all of us out of work.  I don’t think any of us truly believed it would be as calamitous as it ended up being. For those who don’t know, hereRead More →

The previous post in this series introduced the idea of query execution plans.  These plans are compiled by the SQL Server optimizer when a query is executed in an effort to decide an efficient manner in which to execute a query.  This compilation takes time and effort beyond that of actually running the query.  While most compilation effort is relatively small, at times it can be significant and be an impetus to query performance.  In this post we’ll offer methods to measure that time and effort. The demos below utilize the WideWorldImporters DB that can be downloaded for free. To follow along open SSMS, create a new queryRead More →

This post will serve as the first in a series of posts about query execution plans.  In this entry the goal is simply to understand that query execution plans exist, why they exist, and how to do basic navigation of them in SSMS. Every time a query is executed on a SQL Server instance the server must decide how best to find the necessary data to be retrieved or modified.  It’s not as simple as going to a table and returning rows.  A table may have multiple indexes and the decision must be made about which, if any, is appropriate for this query.  There mightRead More →