Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 3 down vote accepted

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:

  • Fragmentation of the table.
  • Fragmentation of the physical database files.
  • Poor choice of clustered index key.
  • Large table with low percentage of its data in the buffer pool, requiring lots of physical IO to read.
  • Lack of spare memory/io/cpu capacity to deal with this one off task on top of the normal workload.
  • Index creation requiring tempdb for sorting but tempdb on same drive as data and or log files.

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.

    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
    avg_io_stall_ms DESC

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.

    DB_NAME(database_id) AS [Database Name]
  , OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
  , AS [Index Name]
  , ps.index_id
  , index_type_desc
  , avg_fragmentation_in_percent
  , fragment_count
  , page_count
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
    sys.indexes AS i
ON  ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
    database_id = DB_ID()
AND page_count > 500
    avg_fragmentation_in_percent DESC ;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.