Dynamic Index Creation - SQL Server

Question

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.

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?

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.

Dropping Indexes:

• 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).
• Only drop unused indexes for tables that are being actively used (indexes on unused parts of the system aren't a concern).
• Log it.
• This won't highlight duplicate indexes (that will have to be manual), just the quick wins (unused indexes with writes).

Creating Indexes

• Only look for indexes with a value above a certain threshold.
• 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).
• 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.
• Log it.

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

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.

Edit: 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.

Answer 1

I can't recommend what you're contemplating, but you might be able to simplify your life by gathering the inputs to your contemplated program and making them available to clients and the support team.

If the problem were as simple as you suppose, surely the server itself or the tuning advisor would have solved it by now. You're making at least one unwarranted assumption,

require the server to be up for a minimum amount of time to ensure all relevant server statistics are up to date.

Table statistics are only as good as the last time the were updated after a significant change. Uptime won't guarantee anything about truncate table or a bulk insert.

This won't highlight duplicate indexes

But that's something you can do in a single query using the system tables. (It would be disappointing if the tuning gadget didn't help with those.) You could similarly look for overlapping indexes, such as for columns {a,b} and {a}; the second won't be useful unless {b} is selective and there are queries that don't mention {b}.

To look for new indexes, I would be tempted to try to instrument query use frequency and automate the analysis of query plan output. If you can identify frequently used, long-running queries and map their physical operations (table scan, hash join, etc.) onto the tables and existing indexes, you would have good input for adding and removing indexes. But you have to allow for the infrequently run quarterly report that, without its otherwise unused index, would take days to complete.

I must tell you that when I did that kind of analysis once some years ago, I was disappointed to learn that most problem children were awful queries, usually prompted by awful table design. No index will help the SQL mule. Hopefully that will not be your experience.

An aspect you didn't touch on that might be just as important is machine capacity. You might look into gathering, say, hourly snapshots of SQL Server stats, like disk queue depth and paging. Hardly a server exists that can't be improved with more RAM, and sometimes that's really the best answer.