SQL Server Index Performance Tips for Business Intelligence
If you are new to Reporting Databases you need to be aware that they indexing needs are different from OLTP database operations. While you will still have clustered and nonclustered indexes how you utilize them will be different with Reporting systems.
Having a lot of indexes on a table will impact the speed of transaction statements such as INSERT, UPDATE and DELETE. With any of this operations all indexes must be updated to reflect the changes in the data on the table. While this can be a problem with transactional systems, it may not be an issue in Reporting databases that receive batch updates. The enhanced performance while generating the reports with SELECT statements will be worth the impact. Speed of report delivery is critical with reporting systems, not necessarily how fast you input the records.
When looking at the performance of SELECT statements be sure to use the SQL Execution Plan to see how your report queries are currently selecting data. On smaller tables a table scan on a table with no indexes may be faster than creating indexes on the same table. The best way to determine if an index can help in this case is to run the Execution plan and then clear the cache. Add an index and then rerun the Execution plan to see how the Query Optimize handles the query.
It is acceptable and expected to create lots of nonclustered indexes on tables in a Reporting database. This gives the query optimizer more options to choose from as queries are generated against the tables.
Another common optimization that most developers do not use is the ability to sort the columns within the index. Most reports will want to return results in certain orders, by product, sales date, or state. By creating an index that has a sort order on columns frequently used in the ORDER BY clause of a query you can increase the performance of the query. The index can replace the need for the database engine to sort the results of the query in an additional step.
For complete guidance on SQL Server Indexes see: