Database indexing questions
What indexes are associated currently with a given table?
What are the most important processes that make use of the table?
What is the ratio of select operations to data modifications performed on the table?
Has a clustered index been created for the table?
Can the clustered index be replaced by a nonclustered index?
Do any of the indexes cover one or more of the critical queries?
Is a composite index required to enforce the uniqueness of a compound primary key?
What indexes can be defined as unique?
What are the major sorting requirements?
Do some queries use descending ordering of result sets?
Do the indexes support joins and referential integrity checks?
Does indexing affect update types (direct versus deferred)?
What indexes are needed for cursor positioning?
If dirty reads are required, are there unique indexes to support the scan?
Should IDENTITY columns be added to tables and indexes to generate unique indexes? Unique indexes are required for updatable cursors and dirty reads.
When deciding how many indexes to use, consider:
- Space constraints
- Access paths to table
- Percentage of data modifications versus select operations
- Performance requirements of reports versus OLTP
- Performance impacts of index changes
- How often you can use update statistics
