I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is there a way to prevent or limit this? After all I am creating the index to increase performance. Or is this a onetime hit I have to endure for things to be better in the future?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
There are many possible explanations for this. It may be that you have to accept it as the cost of making an improvement but it may be highlighting issues elsewhere in the system. Possible causes:
Your question is a little sparse on the details necessary to provide a conclusive answer. To start with, can you add the table DDL (including clustered index definition) to your question along with row counts and database size. Also, a description of the server spec (memory, cpu, drive configuration) and whether or not tempdb is on a separate array.
Next, the following will give us an indication of how your IO subsystem is performing.
The following will tell us how badly fragmented your existing indexes are. You may want to run this at a quiet time as it can impact on performance.