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:
sys.dm_db_index_usage_statsshowed 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."
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.
Dave Wentzel CONTENT
sql server data architecture devops