{"id":2611,"date":"2013-06-20T04:37:52","date_gmt":"2013-06-20T11:37:52","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/kimberly\/?p=2611"},"modified":"2017-09-25T19:23:14","modified_gmt":"2017-09-26T02:23:14","slug":"the-accidental-dba-day-20-of-30-are-your-indexing-strategies-working-aka-indexing-dmvs","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/the-accidental-dba-day-20-of-30-are-your-indexing-strategies-working-aka-indexing-dmvs\/","title":{"rendered":"The Accidental DBA (Day 20 of 30): Are your indexing strategies working? (aka Indexing DMVs)"},"content":{"rendered":"<p><em>This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs &#8216;keep the SQL Server lights on&#8217;. It&#8217;s a little taster to let you know what we cover in our\u00a0<a href=\"https:\/\/www.sqlskills.com\/sql-server-training\/ie0\/?utm_source=accidentaldba&amp;utm_medium=blogs&amp;utm_campaign=training\" target=\"_blank\" rel=\"noopener noreferrer\">Immersion Event for The Accidental\/Junior DBA<\/a>, which we present\u00a0<a href=\"https:\/\/www.sqlskills.com\/sql-server-training\/immersion-events-schedule\/?utm_source=accidentaldba&amp;utm_medium=blogs&amp;utm_campaign=training\" target=\"_blank\" rel=\"noopener noreferrer\" class=\"broken_link\">several times each year<\/a>. You can find all the other posts in this series at\u00a0<a href=\"https:\/\/www.SQLskills.com\/help\/accidental-dba\/?utm_source=accidentaldba&amp;utm_medium=blogs&amp;utm_campaign=training\" target=\"_blank\" rel=\"noopener noreferrer\">http:\/\/www.SQLskills.com\/help\/AccidentalDBA<\/a>. Enjoy!<\/em><\/p>\n<p>As an accidental DBA you are constantly wearing many hats. You&#8217;ve heard that indexes are critical for performance (and that\u2019s absolutely true) but it\u2019s not just any indexes \u2013 it\u2019s the RIGHT indexes. The unfortunate thing about indexing is that there\u2019s both a science and an art to it. The science of it is that EVERY single query can be tuned and almost any SINGLE index can have a positive effect on certain\/specific scenarios. What I mean by this is that I can show an index that\u2019s ABSOLUTELY fantastic in one scenario but yet it can be horrible for others.\u00a0<strong>In other words, there\u2019s no short cut or one-size-fits-all answer to indexing.<\/strong><\/p>\n<p>This is where the art comes in \u2013 indexing for performance is really about finding the right balance between too many and too few indexes, as well as trying to get more from the indexes that you do keep.<\/p>\n<p>Having said that, it\u2019s way beyond the scope of what we can talk about in a short post. Unfortunately, a full discussion about these things would take a lot of time but if you&#8217;re interested in digging in a bit deeper on any of these topics&#8230;<\/p>\n<ul>\n<li><strong>How do indexes work?<\/strong><\/li>\n<li><strong>What\u2019s the clustered index?<\/strong><\/li>\n<li><strong>What are good\/general strategies for indexing?<\/strong><\/li>\n<li>When should columns be put in the key (and in what order should they be defined) versus when should they be in the INCLUDE list?<\/li>\n<li>Would we benefit from a filtered index?<\/li>\n<\/ul>\n<p>&#8230; then check out my new (in 2017) online training course <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-indexing-for-performance\" target=\"_blank\" rel=\"noopener noreferrer\"><em>SQL Server: Indexing for Performance<\/em><\/a>.<\/p>\n<p>These are difficult discussions. And really, if you\u2019re only meant to \u201cmaintain\u201d the system and keep the lights on \u2013 these are probably beyond what you can effectively do in your already-packed day.<\/p>\n<p>So, who is handling this job, to create and define the database\u2019s indexes? It\u2019s probably the application developers. These might be in-house developers (which is good \u2013 you\u2019ll have someone with whom to consult when you do your analysis) or they might be vendor developers (which might be both good and bad). The good side is that some vendors are open to discussions on their customer support lines and may help you through the issues that you\u2019re seeing. The bad news is that some vendors are not open to discussions and they might not support you if you make any indexing changes.<\/p>\n<p><strong>So, first and foremost \u2013 make sure you thoroughly understand the environment you\u2019re about to analyze and do not change anything without verifying that you can.<\/strong><\/p>\n<h2><strong>Introduction<\/strong><\/h2>\n<p><b>The good news is that it\u2019s NOT all doom and gloom.<\/b>\u00a0There are some very helpful DMVs and resources that you can use to analyze your environment and see where some of your indexing issues are. Again, you might not be able to change them (immediately) but, you will be armed with information to help you discuss what you are seeing.<\/p>\n<p>When I\u2019m analyzing a\u00a0<em>new-to-me<\/em>\u00a0system, I tend to break down my index analysis into three parts:<\/p>\n<ol>\n<li>Are there any indexes just lying around not doing anything useful\u2026 time to get rid of the dead weight!<\/li>\n<li>Are there any indexes that are bloated and unhealthy \u2013 costing me time and space\u2026 time to analyze the health of my existing (and useful) indexes<\/li>\n<li>Then, and only then do I feel like you can add more indexes.<\/li>\n<\/ol>\n<h2><strong>Part I: Getting rid of the dead weight<\/strong><\/h2>\n<h3><b>Fully duplicate indexes<\/b><\/h3>\n<p>SQL Server lets you create redundant\/duplicate indexes. This is annoying but it\u2019s always been the case. It certainly begs the question about why SQL Server lets you do this and I wrote up an answer to this in an article on SQL Server Magazine here: Why SQL Server Lets You Create Redundant Indexes (<a href=\"http:\/\/sqlmag.com\/blog\/why-sql-server-lets-you-create-redundant-indexes\">http:\/\/sqlmag.com\/blog\/why-sql-server-lets-you-create-redundant-indexes<\/a>). Regardless of why, you still need to remove them. And, even if you don\u2019t remember seeing duplicate indexes, you might be surprised. Without knowing index internals, it might be harder to recognize duplicates than you think. It\u2019s not always as easy as Index1 on col1 and Index2 on col1. Internally, SQL Server adds columns to your index and most commands (like sp_helpindex) do not show these internally added columns. The good news is that I have a version of sp_helpindex that does show you the entire structure. And, tied to that updated version of sp_helpindex, I built a script for finding duplicate indexes at either the table-level or database-wide. Check out these links:<\/p>\n<ul>\n<li>If you\u2019re on SQL Server 2008 or higher, use:\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/removing-duplicate-indexes\/\">Removing duplicate indexes<\/a>.<\/li>\n<li>If you need to do this Finding duplicates on SQL Server 2000 (thanks to Randolph West [@rabryst] ):\u00a0<a href=\"http:\/\/rabryst.ca\/2012\/03\/remove-duplicate-indexes-in-sql-server-2000\/\">http:\/\/rabryst.ca\/2012\/03\/remove-duplicate-indexes-in-sql-server-2000\/<\/a>.<\/li>\n<li>And, if you want to learn more about how indexes can be duplicates but not entirely look like they are, check out this post:\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/how-can-you-tell-if-an-index-is-really-a-duplicate\/\">How can you tell if an index is REALLY a duplicate?<\/a>.<\/li>\n<\/ul>\n<p>But, you could BREAK the application if they\u2019ve used index hints. So, beware! Generally, it might be best to\u00a0<b>disable<\/b>\u00a0an index for a while before you just drop it.<\/p>\n<h3><b>Unused Indexes<\/b><\/h3>\n<p>Almost as expensive as a duplicate index is one that never gets used. However, this is a lot more challenging to determine. There is a fantastic DMV (sys.dm_db_index_usage_stats) that gives you information about index usage but, it\u2019s not perfect. And, some of the behaviors have changed in some releases (sigh). If you\u2019re really wanting to best understand your index usage patterns you\u2019ll have to persist this information over a business cycle and be sure to persist it prior to index maintenance (see this connect item:\u00a0<b>Rebuilding an index clears stats from sys.dm_db_index_usage_stats<\/b>).\u00a0<a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/739566\/rebuilding-an-index-clears-stats-from-sys-dm-db-index-usage-stats\">https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/739566\/rebuilding-an-index-clears-stats-from-sys-dm-db-index-usage-stats<\/a>\u00a0Note: this is only an issue is SQL Server 2012.<\/p>\n<p>Bug, again, even the information tracked in this DMV isn\u2019t perfect. One of my biggest frustrations is the user_updates only tracks the number of STATEMENTS, not the number of ROWS modified. For example, if I execute this statement (without a WHERE clause) UPDATE Table SET ColumnX = Value and it affects 10,000 rows, then the user_updates column will be incremented by 1 for BOTH the table and any indexes that include ColumnX. So, you might have an even higher (possibly MUCH higher) value for updates.<\/p>\n<p>And, there\u2019s more to it than that. Instead of duplicating this information, I\u2019ll link to a FANTASTIC post by Microsoft PFE Ignacio [Nacho] Alonso\u2019s FAQ around sys.dm_db_index_usage_stats.\u00a0<a href=\"http:\/\/blogs.msdn.com\/b\/ialonso\/archive\/2012\/10\/08\/faq-around-sys-dm-db-index-usage-stats.aspx\" class=\"broken_link\">http:\/\/blogs.msdn.com\/b\/ialonso\/archive\/2012\/10\/08\/faq-around-sys-dm-db-index-usage-stats.aspx<\/a>.<\/p>\n<p>Finally, both Paul and I have written about this DMV as well as how to persist it. Check out these posts:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/spring-cleaning-your-indexes-part-i\/\">Spring cleaning your indexes \u2013 Part I<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-how-can-you-tell-if-an-index-is-being-used\/\">Indexes From Every Angle: How can you tell if an index is being used?<\/a><\/li>\n<\/ul>\n<h3><b>Similar or semi-redundant indexes<\/b><\/h3>\n<p>You might have some indexes that are good candidates for consolidation:<\/p>\n<ul>\n<li>Indexes that have the same key (but possibly different INCLUDEd columns)\n<ul>\n<li>Index1: Key = LastName<\/li>\n<li>Index2: Key = LastName, INCLUDE = FirstName<\/li>\n<li>In this case you don\u2019t \u201cNEED\u201d Index1. There\u2019s NOTHING that Index1 does that Index2 cannot also do. However, Index2 is wider. So, a query that solely wants the following will have more I\/Os to do because of the wider index:\n<ul>\n<li>SELECT LastName, count(*) FROM table GROUP BY LastName<\/li>\n<\/ul>\n<\/li>\n<li>But, the argument is \u2013 how critical is that query? How often is that index really used? Remember, you can use sys.dm_db_index_usage_stats to help you determine how often it\u2019s used.<\/li>\n<\/ul>\n<\/li>\n<li>Indexes that have left-based subsets of other index keys\n<ul>\n<li>Index1: Key = LastName, FirstName, MiddleInitial<\/li>\n<li>Index2: Key = LastName INCLUDE = SSN<\/li>\n<li>Index3: Key = LastName, FirstName INCLUDE = phone<\/li>\n<li>In this case each index does provide some specific (and unique) uses. However, you have a lot of redundancy there.<\/li>\n<li>What if you created a new Index: LastName, FirstName, MiddleInitial INCLUDE (SSN, phone)<\/li>\n<li>Again, this new index is wider than any of the prior 3 but this new index has even more uses and it has less overall overhead (only one index to maintain, only one index on disk, only one index in cache [and, it\u2019s more likely to stay in cache]). But, you still have to determine how critical each of the queries are that were using the narrower indexes? As well as how much more expensive they are with the new index.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>So, this is where the \u201cart\u201d of indexing comes into play. Index consolidation is a critical step in reducing waste and table bloat but there isn\u2019t a simple answer to every consolidation option. This is another \u201cit depends\u201d case.<\/p>\n<h2><strong>Part II: Analyze the health of your existing indexes<\/strong><\/h2>\n<p>This topic has been talked about in many places. And, we\u2019ve even chatted about it in our Accidental DBA series here:\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-14-of-30-index-maintenance\/\">The Accidental DBA (Day 14 of 30): Index Maintenance<\/a>.<\/p>\n<p>In the content of this post, I want to make sure that after I&#8217;ve cleaned up the dead weight, my existing and useful indexes are healthy. And, you might want to review your index maintenance strategies and see if they\u2019re \u201clighter\u201d and take less time. And, be sure that they don\u2019t miss anything. A couple of key reminders:<\/p>\n<ul>\n<li>Make sure your index maintenance routines at indexes on tables\u00a0<strong>AND views<\/strong><\/li>\n<li>Make sure your index routines use a LIMITED scan if you\u2019re only analyzing avg_fragmentation_in_percent<\/li>\n<\/ul>\n<p>And, here are a few other resources that you might find handy on this topic:<\/p>\n<ul>\n<li>Paul&#8217;s Index Fragmentation <a href=\"http:\/\/technet.microsoft.com\/en-US\/sqlserver\/gg429790.aspx\">video<\/a>\u00a0and <a href=\"http:\/\/technet.microsoft.com\/en-US\/sqlserver\/gg429789.aspx\">demo<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/database-maintenance-best-practices-part-ii-setting-fillfactor\/\">Database Maintenance Best Practices Part II \u2013 Setting FILLFACTOR<\/a><\/li>\n<li>SQL Server 2000 Index Fragmentation whitepaper (yes, I know it&#8217;s OLD but it&#8217;s still good for the concepts of managing index fragmentation but the commands have all changed):\u00a0<a href=\"http:\/\/technet.microsoft.com\/library\/Cc966523\">Microsoft SQL Server 2000 Index Defragmentation Best Practices<\/a><\/li>\n<\/ul>\n<h2><strong>Part III: Adding more indexes<\/strong><\/h2>\n<p>This is a tricky one. There are lots of good\/bad practices around adding indexes. One of the worst is that most folks just add indexes without really fully analyzing (and CORRECTLY analyzing) their existing indexes. The reason I say correctly analyzing their existing indexes is that the tools (like sp_helpindex and SSMS) hide some of the information about columns that might have been added to your indexes. So, unless you really know what your indexes look like you won\u2019t be able to correctly add new indexes while consolidating your existing indexes.<\/p>\n<p>The primary tool that I want to discuss here is the &#8220;user impact&#8221; aspect of the missing index DMV queries that exist out there (and, there are some great examples of using the missing index DMVs). And, while I STRONGLY encourage you to use them as a GUIDE, I do want you to remember that they&#8217;re not perfect. Here are my main issues\/concerns\/gripes:<\/p>\n<ul>\n<li>The missing index DMVs (and therefore the &#8220;index\/green hint&#8221; that shows up in showplan) only tune the plan that was executed. If the plan performed a hash join then the index is going to help the hash join. But, it&#8217;s unlikely that the join type will change. And, it might be the case that a different index would perform a different join type and the query would be even faster. If you&#8217;re about to trust the missing index DMVs recommendations(or, the green hint), then consider reverse-engineering the queries that are being tuned by these recommendations (see\u00a0<a href=\"http:\/\/sqlblog.com\/blogs\/jonathan_kehayias\/archive\/2009\/07\/27\/digging-into-the-sql-plan-cache-finding-missing-indexes.aspx\" class=\"broken_link\">Jon&#8217;s post on how to do this<\/a>) and then (if possible) run these queries through DTA (the Database Engine Tuning Advisor). DTA has capabilities that the missing index DMVs do not in that DTA can &#8220;hypothesize&#8221; about alternate strategies. This makes the index recommendations even better!<\/li>\n<li>The missing index DMVs only think about the BEST index for EACH query. And, that does make sense (from a QUERY tuning perspective) but, you need to do SYSTEM tuning. You can&#8217;t just create individual indexes for each and every query that needs one. You definitely want to consider the indexes that have the highest user impact but you also don&#8217;t want to forget about consolidation.<\/li>\n<li>The missing index DMVs can show indexes that you already have.\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/\">Missing index DMVs bug that could cost your sanity\u2026<\/a><\/li>\n<\/ul>\n<p>Here are a few Missing Index DMV queries\/resources:<\/p>\n<ul>\n<li><a href=\"http:\/\/blogs.msdn.com\/b\/bartd\/archive\/2007\/07\/19\/are-you-using-sql-s-missing-index-dmvs.aspx\" class=\"broken_link\">Are you using SQL&#8217;s Missing Index DMVs?<\/a><\/li>\n<li><a href=\"http:\/\/sqlfool.com\/2009\/04\/a-look-at-missing-indexes\/\" class=\"broken_link\">A\u00a0Look at Missing Indexes<\/a><\/li>\n<li><a title=\"Don\u2019t just blindly create those \u201cmissing\u201d indexes!\" href=\"http:\/\/www.sqlperformance.com\/2013\/06\/t-sql-queries\/missing-index\" rel=\"bookmark\">Don\u2019t just blindly create those \u201cmissing\u201d indexes!<\/a><\/li>\n<\/ul>\n<h2><strong>Summary<\/strong><\/h2>\n<p>These are the primary things that I\u2019m looking for when I want to see how the already implemented indexing strategies are working as well as the order in which I begin to analyze and change indexes. But, beware: you can negatively impact the environment so it\u2019s important that adequate testing is done to make sure that what you\u2019re doing has a net-positive effect.<\/p>\n<p>Finally, I also did a video summarizing these things that I&#8217;m describing here &#8211; you might want to check this out as well:\u00a0<a href=\"http:\/\/technet.microsoft.com\/en-US\/sqlserver\/gg545020.aspx\">http:\/\/technet.microsoft.com\/en-US\/sqlserver\/gg545020.aspx<\/a>.<\/p>\n<p>Thanks for reading!<br \/>\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs &#8216;keep the SQL Server lights on&#8217;. It&#8217;s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA, which we present\u00a0several times each year. You can find all the other posts in [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[87,23,36],"tags":[],"class_list":["post-2611","post","type-post","status-publish","format-standard","hentry","category-accidental-dba","category-database-maintenance","category-indexes"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2611","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=2611"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2611\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=2611"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=2611"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=2611"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}