DevOps in the real world: Who should be responsible for index maintenance?

Larger ISVs usually employ at least two types of data professionals, the data developer and the database administrator.  In some situations, especially if DevOps is practiced, the lines may be a bit blurred regarding responsibilities.  I find it interesting in my consulting engagements which data professional is responsible for index maintenance.  Who is responsible for indexes at your company?  Who decides what indexes should exist?  Who creates them?  Who ensures proper index hygiene occurs (defragmentation, manual update stats)?  Which group should be responsible for these tasks?  

Informally I've found at my engagements that when both groups exist that it is the DBAs that are responsible for all aspects of indexing.  I think this is just flat-out wrong.  And I'm probably in the minority on this though.  Here's my reasoning:  

• DBAs probably don't know all of the data access paths, and the most important ones we wish to optimize in the application.  Unfortunately your developers may not either, but that's a different issue.  
• If you practice some variant of "database change review", where any and all schema changes to a database go through a review system to ensure they follow standards are are modeled sensibly, then you should have a good feel for what indexes should exist prior to those tables hitting your production systems.  
• If you are doing performance testing then your developers should further understand the data access patterns.  Performance testing won't find every issue but the most egregious issues should be caught.  
• Too many shops allow production DBAs to alter indexing schemes without consulting the data developers at all.  In EVERY case I've seen disaster because of this.  Here's a few examples:
  • we implemented queue tables as heaps and the DBAs didn't like that so they added clustered indexes and our queues experienced odd locking and concurrency behavior.  Contrary to google, sometimes heaps are good.  
  • DBAs applied indexes based on recommendations in the missing index DMVs and the plan caches.  The net result was cases where the same column in a table was the leading index column for 5 or more indexes, differing only by INCLUDED columns.  These DMVs must be countered with common sense and knowledge of your data.  
  • DBAs removed indexes because the cardinality at the time when they checked the index was LOW.  It's common for some tables to have a ProcessedStatusCode column that is binary (processed/not processed).  Throughout most of the day every row is in the processed state (1) until overnight batch processing starts.  Indexes on a ProcessedStatusCode column are critical, albeit counter-intuitive.  Many people believe that indexes on low cardinality tables are useless, but in situations like this they are required.  
  • DBAs removed indexes because sys.dm_db_index_usage_stats showed that data access was heavily skewed to updates and very little to seeks/scans/lookups.  The problem is, the indexes were needed during month-end processing and were suddenly gone when they were required.  

Contrary to what you are probably thinking, this blog post is not an "us vs them", anti-DBA rant.  "But you said above that it is flat-out wrong that DBAs are responsible for indexing."  

Right!

The resolution to all of the issues above is simple.  Collaboration.  Take the last example where important indexes were missing during month-end processing.  Clearly the DBAs had some valid reasons to remove the indexes and the data devs had valid reasons for needing them.  After our month-end processing went from 12 hours to 7 days due to the missing indexes, and after everyone stopped playing the blame game and hootin and hollerin, we collaborated and determined that we could actually build those indexes a day or so before month-end processing and then remove them later.  These indexes are huge and the chargeback for the SAN storage was not insignificant.  

This was a non-zero-sum event.  We managed to save on storage, our system was faster during critical month-end processing (those indexes were less fragmented), and the system was marginally faster during standard daily processing where the update overhead to those indexes could be eliminated. So, here's a case where a bit of collaboration led to one of those truly rare "win-win" situations.  And that's what we all want during conflict resolution.  We don't want to hurt anyone's feelings.  

(I found this graphic on an EXTREMELY popular DevOps site.  Maybe I'm being picky but I still see a separation of Dev and Ops.  Where is the collaboration?  I don't understand how this graphic couldn't be titled "Waterfall")

This is just another example where DevOps works.  My last few blog posts I pointed out that there is a misconception that DevOps means combining developers and ops guys.  It isn't.  It is about about situations like this.  Nobody is perfect.  I've worked in just as many shops where EVERY column in a table had an index because the developer figured that if a few indexes are good then more must be better.  Indexing is one of those cases where it really is difficult to determine which group should have ultimate responsibility, the developers or the DBAs.  Simple collaboration and following DevOps is the key.  

You have just read another DevOps post on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.