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 →

Even the most green DBAs know that indexes are a good thing.  A database won’t run well without them.  But it is important to remember that indexes aren’t free.  They have to be created, stored, backed up, consistency checked, updated, defragmented, and have their statistics updated.  That’s a lot of work!  This extra work associated with indexes is sometimes called the “write penalty”.  It is why having too many indexes can be just as bad as not having enough. The goal of the DBA should be to have as many high-value indexes — indexes that will be used often by the workload — as possibleRead More →

In this entry I am going to cover a handful of common myths and misconceptions about how SQL Server handles memory.  Each of these comes from repeated instances of confusion in my dealings with other SQL Server users.  Many of these have come up from even very seasoned DBAs so don’t feel bad if you’ve been laboring under a misconception yourself. The Myth: I logged in and found my SQL Server is running at 95% memory utilization.  I restarted the box which fixed it temporarily, but it grew and grew right back to 95% within a few hours!!  SQL Server must have a memory leak!Read More →