Professional SQL Server® 2005 Performance Tuning

15.10. Schema Issues in Production

Database schemas for enterprise applications such as SAP, Oracle, Siebel, and so on cannot typically be altered using straight database commands, and most vendors don't recommend altering the database schema without the help of application experts (which entails additional fees). But experience has shown that there are often instances of poor performance that arise due to issues caused by the schema.

For example, a typical SQL Server database for a Siebel application contains more than 2000 tables and some of the larger, more heavily used tables contain as many as 60 indexes. Why so many indexes? This is because Siebel tests every possible customer scenario that they can think of and then designs the database schema accordingly. However, what most customers get is a heavily indexed database consisting of hundreds of indexes that are probably never going to be used. Prior to SQL Server 2005, it was nearly impossible to report on index usage. Even if you could ascertain that 50 percent of the indexes in your database were not being used, there was no method to disable them; you could only drop those indexes. Dropping indexes was strictly prohibited by Siebel. Although this case is specific to Siebel, I am sure you have seen or heard of such scenarios before.

So what can you do in such cases? Well, SQL 2005 comes with a new set of catalog views that provide a fair bit of information that greatly enhances the DBA's understanding of database indexes. ...