SQLTeam.com | Weblogs | Forums

Index Maintenance Directly on Production?


Hi all,

What is your deployment process for index changes? I've always searched for missing/unused/duplicate indexes directly on production and implemented changes there. Development has incorporated our database indexes into their deployment scripts so any changes I make directly on production now can conflict with their deployment scripts causing problems. By having to send index changes to development, changes take > 1 month to make it into production which, in my opinion, is often not quick enough. Obviously, making changes directly on production has its own risks. Just curious what others do. Is development involved in the index maintenance process and/or are indexes considered part of the code base / product or is it treated separately?



what is your role? dev or dba or systems?


VP, Operations :slight_smile:

We don't have a DBA. Our Systems Administrator handles the backups and servers but I do the performance tuning and indexing. We had hired a DBA but he wasn't very good and we decided there wasn't enough work for it to be a full time job yet.

Note: My background is dev. I enjoy performance tuning which is why I still do it.


VP??? Let it go Craig David Smith :grin:
This is what we do.

  1. We have a dev, qa, staging and prod environments
  2. Each and every script goes through some auditing/code review in the pre prod environments (and is in source control: svn/git/tfs)
  3. When full vetted it is deployed with dev's code or separately as a hotfix
  4. if VP Operations gets involved we get rid of him/her


lol. thanks :wink:


Indexes should be considered separately. It's a terrible idea to mix app and index changes, as they are completely separate things. The app might change 10 times with no index changes or vice versa. I've been a DBA for 30 years, and I always try to get index mods -- adds/changes/deletes -- out of app code/moves, period.

Ideally, developers would never create or otherwise touch indexes at all. But in many smaller shops they do and they will, it's just a fact of life. But index modifications should still be done completely separately from app mods.


We would deploy a new index on production too (having found a need for it there).

But we would also add the index into the forthcoming DDL Update Script - so it would be deployed to DEV (the change, being at the end of the script, is ahead of the most recent rollout to DEV so identified as not-yet-on-DEV) ... it would then be included in rollout to QA and subsequently to PROD (where it already is, so it would be Dropped and Recreated - which might be A Bad Thing on a large table ...) - at which point all DBs would be in step ...

... unless a subsequent DDL script change on DEV did away with that index - DEV might add an INCLUDE column, perhaps.