Dynamic Index Creation - SQL Server
<p>Ok, so I work for a company who sells a web product which has a MS SQL Server back end (can be any version, we've just changed our requirements to 2008+ now that 05 is out of extended support). All databases are owned by the company who purchases the product but we have VPN access and have a tech support department to deal with any issues. One part of my role is to act as 3rd line support for SQL issues. </p>

<p>When performance is a concern one of the usual checks is unused/missing indexes. We've got the usual standard indexes but depending on which modules or how a company utilises the system then it will require different indexes (there's an accounting module and a document management module amongst others). With hundreds of customers it's not possible to remote onto each on a regular basis in order to carry out optimisation work. I'm wondering if anybody else in my position has considered a scheduled task that may be able to drop and create indexes when needed?</p>

<p>I've got concerns (obviously), any changes that this procedure makes would also be stored in a table with full details of the change and a time stamp. I'd need this to be bullet proof, can't be sending something out into the wild if it may cause issues. I'm thinking an overnight or (probably) weekly task.</p>

<p>Dropping Indexes:</p>

<ul>
<li>Would require the server to be up for a minimum amount of time to ensure all relevant server statistics are up to date (say 2 weeks or 1 month).</li>
<li>Only drop unused indexes for tables that are being actively used (indexes on unused parts of the system aren't a concern).</li>
<li>Log it.</li>
<li>This won't highlight duplicate indexes (that will have to be manual), just the quick wins (unused indexes with writes).</li>
</ul>

<p>Creating Indexes</p>

<ul>
<li>Only look for indexes with a value above a certain threshold.</li>
<li>Would have to check whether any similar indexes could be modified to cover the requirement. This could be on a ranking (check all indexed fields are the same and then score the included fields to see if additional would be needed).</li>
<li>Limit to a maximum number of indexes to be created (say 5 per week) to ensure it doesn't get carried away and create a bunch at once). This should help only focus on the most important indexes.</li>
<li>Log it.</li>
</ul>

<p>This would need to be dynamic as we've got customers on different versions of the system with different usage patterns.</p>

<p>Just to clarify: I'm not expecting anybody to code for this, it's more a question relating to the feasibility and concerns for a task like this.</p>

<p><strong>Edit:</strong> I've put a bounty on this to gather some further opinions and to get feedback from anybody who may have tried this before. I'll award it to the answer with the most upvotes by the time the bounty duration ends.</p>