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 →

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 →

If you read my last post, you’ll find this to the be exact opposite.  Instead of determining the best way to find rows that exist in one dataset but not another, we’ll be looking for rows that exist in 2 different datasets. The obvious answer is a join statement, but does that always perform the best?  Certainly, if you require other fields from the secondary table then a join will always be best.  For this test we are looking for rows that appear in a table called orderheader that are also in a temporary table called #ords.  The field that we are comparing is anRead More →

It is a pretty common need to find rows that exist in one dataset, but not in another.  The question I will look to answer today is, “which way is best?”.  For this test we are looking for rows that appear in a table called orderheader that aren’t also in a temporary table called #ords.  The field that we are comparing is an INT called ord_hdrnumber and it is the Clustered Primary Key of each table. We’ll compare the pros, cons, and performance of 4 popular methods to complete the task.   –NOT IN SELECT a.ord_hdrnumber FROM orderheader a WHERE a.ord_hdrnumber NOT IN (SELECT b.ord_hdrnumberRead More →