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 →

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 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 →

There are a few reasons that one might need to restore a SQL Server backup.  Perhaps it is time to refresh a test database with a copy from production. Maybe there has been a disaster and this is how the disaster recovery plan is getting the system back online.  Another common restore reason is to recover a single data element that was removed or modified in production by accident.  Whatever the reason, being able to restore a database and understanding some of the basic options thereof is an essential skill for the SQL Server DBA. For this demo I will cover the topic using theRead More →

In the previous blog entries we talked about the different data files, recovery models, and how they interact.  Today’s entry will talk about Native SQL Server Backups.  Having an understanding of the files and recovery models will help this entry make more sense.  We’ll cover restoring these backup files in the next installment. The most basic backup of a SQL Server database is a full backup.  This takes a snapshot of the data and log files for the moment in time that the backup was invoked.  It includes all of the objects of the database including tables and code objects.  This file can be restoredRead More →

This series is aimed at beginners in the SQL Server world or at those thrust into the role of DBA with little or no training.  As such we’ll be focusing on some common concepts and doing so at a very high level.  This series will not get especially technical nor will it cover topics in extreme detail.  Also, the demo information will be done using mostly UI from SSMS and wizards as applicable rather than scripting.  If being a full time SQL Server DBA is among your career goals you should consider this series of posts as only a starting point and seek further knowledgeRead More →

This series is aimed at beginners in the SQL Server world or at those thrust into the role of DBA with little or no training.  As such we’ll be focusing on some common concepts and doing so at a very high level.  This series will not get especially technical nor will it cover topics in extreme detail.  Also, the demo information will be done using mostly UI from SSMS and wizards as applicable rather than scripting.  If being a full time SQL Server DBA is among your career goals you should consider this series of posts as only a starting point and seek further knowledgeRead More →