Strategy to deign index for new database/table

It is recommended to take a strategic approach while you start designing index for a new database or new table/s for optimal performance and use of resources. Please consider using below strategy to design indexes for a newly created database/table/s:

PHASE 1: INITIAL DESIGN

This phase deals with designing index for a newly created database/table/s before moving it to production. Please follow below steps to design index for the database/table/s:

1. Start with each table and go one by one. Identify all queries which will run on the table and identify a best clustered index candidate for the table.

2. Select Primary Key and unique key. Do not blindly make your primary key as a clustered index. It is fine if your clustered index is not primary key.

3. Have foreign key are in place where ever needed. Foreign keys are not that bad for performance as long as you have index on this. So make sure you create non clustered index on the columns which are part of foreign key.

4. Create non clustered index for highly selective queries. Highly selective queries are the queries that will be frequently used and their performances are critical to business. Prefer to use composite (wide) index instead of having multiple smaller index but do not go for covering index.

5. STOP INDEX DESIGN AND IMPLEMENTATION AT THIS STAGE AND MOVE AHEAD WITH YOUR FINAL LOAD TEST AND/OR USER ACCEPTANCE TEST WHERE YOU CAN COLLECT TRUE WORKLOAD FROM PRODUCTION LOAD SCENERIO.

6. Gather true work load from load test and/or user acceptance test and analyze it and then design other non-clustered indexes. Follow below rules while you create such indexes:

a. Be sure that the workload you are working with truly represents the production scenario. A workload which captures partial scenario can lead us take decision which may degrade application’s performance.

b. Use available tools to analyze the workload and situation. Most common tools will be DTA (Database Tuning Advisor) and Missing Index Hint from Query Plan. However both tool has it’s limitation so do not blindly follow their recommendation.

7. MOVE THE DATABASE/TABLES TO PRODUCTION AT THIS STAGE. HOWEVER PLEASE REFER TO PHASE 2 FOR MAINTEAINCE OF INDEX FROM DESIGN PRESPECTIVE WHILE DATABASE/TABLE/S ARE IN PRODUCTION.

PHASE 2: DYNAMIC MAINTENANCE

Applications changes with time. This means database objects and server configuration changes which can make some index irrelevant or harmful for some queries or ma require new index. So once the database/tables are in production you need to keep evaluating the index design. You can execute below strategies to accomplish this task:

Proactive: Ensure that you capture good workload and execute Step 6 of PHASE 1 of this post at a realistic time interval. This time interval should be based on the database/application’s performance history and other relevant parameters. However you need to be very careful to ensure that you do not adversely affect existing system/s while working on this proactive maintenance.

Reactive: When you move a new code (change) to production, make sure that indexing is designed properly.

* Note: In this post I outlined a general strategy to design and implement index for new application/database/table/s which should work for almost any situation. However good index design comes from extensive experience and deep knowledge. Also consider using other options (like indexed view, code review etc) instead of completely relying on index for achieving desired performance.