Como evitar grande impacto no desempenho durante a criação do índice

Há muitas explicações possíveis para isso. Pode ser que você tenha que aceitá-lo como o custo de fazer uma melhoria, mas pode estar destacando problemas em outras partes do sistema. Causas Possíveis:

• Fragmentação da tabela.
• Fragmentação dos arquivos físicos do banco de dados.
• Escolha incorreta da chave de índice clusterizado.
• Tabela grande com baixa porcentagem de seus dados no buffer pool, exigindo muita E/S física para leitura.
• Falta de capacidade de memória/io/cpu sobressalente para lidar com essa tarefa única além da carga de trabalho normal.
• Criação de índice que requer tempdb para classificação, mas tempdb na mesma unidade que dados e/ou arquivos de log.

Sua pergunta é um pouco esparsa nos detalhes necessários para fornecer uma resposta conclusiva. Para começar, você pode adicionar a tabela DDL (incluindo definição de índice clusterizado) à sua pergunta junto com contagens de linha e tamanho do banco de dados. Além disso, uma descrição da especificação do servidor (memória, cpu, configuração da unidade) e se o tempdb está ou não em uma matriz separada.

Em seguida, o seguinte nos dará uma indicação de como está o desempenho do seu subsistema IO.

SELECT
    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]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN 
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

O seguinte nos dirá o quão mal fragmentados estão seus índices existentes. Você pode querer executá-lo em um momento de silêncio, pois pode afetar o desempenho.

SELECT
    DB_NAME(database_id) AS [Database Name]
  , OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
  , i.name AS [Index Name]
  , ps.index_id
  , index_type_desc
  , avg_fragmentation_in_percent
  , fragment_count
  , page_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN 
    sys.indexes AS i
ON  ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE
    database_id = DB_ID()
AND page_count > 500
ORDER BY
    avg_fragmentation_in_percent DESC ;