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 →

I was working on a project this week and stumbled across an issue I hadn’t seen before. The programmer had written stored procedures for every possible CRUD operation as well as all reporting requirements. Security was controlled by granting EXECUTE permissions only to appropriate stored procedures and by explicitly not granting permission to any tables or views within the database. One of the procedures was getting a bad query plan and timing out. This is when I was called in. The procedure was performing a search based on an unknown number of up to 10 search parameters. The code was filled with many AND/OR combinationsRead More →

Trace flag 1117 is a setting that is often misunderstood.  When enabled it forces any file growth event on a data file to include all data files in the same file group so that the files can remain the same size.  I usually hear it described as a setting that changes how TempDB files are treated as they need to grow. While true, what is interesting is that this is more of a side effect of the flag.  The trace flag is not TempDB specific and affects all databases on a SQL Server instance that have more than 1 file in any file group 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 →

This week started off in Nashville at the in.sight user conference and expo.  My team prepared 6 SQL Server presentations that went off great.  I couldn’t be prouder of my crew.  Back at home the rest of the team did a great job holding down the fort while shorthanded.  2 of my presentations were extremely technical in nature covering minimum maintenance on a SQL Server and index sargability.  Upon returning home the strangest coincidences occurred. On my first day back to work I received a case where a customer had gotten corruption in their database.  By the time the case made its way to meRead 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 →

Thank you to those of you that came to see my presentation on full text indexes at SQL Saturday #615 in Baltimore!  As promised, please find the slide deck and table builder/ sample queries script at these links.  Also, I have included a few links to useful Microsoft documentation pages below to help you continue to expand your knowledge. CONTAINS FUNCTION FREETEXT FUNCTION FULL TEXT EXAMPLE LIST A few notes: I received an explanation for the strange query plan functionality I showed at the end of the class.  It turns out I was 100% wrong.  The plan was showing just fine and will show aRead More →

Meet the Enemy: Sure, it looks all innocent and helpful.  Just sitting there informing you of your business activities.  But all too often I’ve seen this hideous monster ruin a SQL Server.   Let’s take a moment and talk about why this TV is our enemy.  Then we can follow up with a few ways to fix the problem. It’s becoming more and more commonplace to place these monitors around an office.  It’s becoming so common in my industry, transportation, that when I get a call saying that system performance is falling off suddenly I have learned to ask about any new TVs early inRead More →

I have been selected to present at SQL Saturday #615 in Baltimore!  I’m am honored, excited, and maybe a little scared.  Come see me (and a huge list of all star presenters) on May 6 at Loyola University in Columbia, MD. My session is “Full Text indexing!” or…”What is that service I never turn on?”.  In this session I’ll talk about what Full Text Indexing is, why it is a separate service, how to set it up, and how to use it.Read More →