MSSQL lento no banco de dados (~ 2TB) - índice / fragmentação?

Question

Eu tenho um banco de dados MS SQL razoavelmente grande (~ 2TB). A maioria dos dados está em uma tabela (~ 6 bilhões de linhas).

Há duas semanas, abandonei dois índices não agrupados na tabela grande e migrei os dados para uma única matriz SSD RAID de 6 TB. Eu, então, recriou os dois índices que levaram algum tempo (assumindo porque atualmente tenho os dados (para tabela e índices) e faço logon no mesmo array e parece que com o RAID eu não posso ter r / w rápido e seqüencial aleatório ao mesmo tempo).

De qualquer forma, depois de recriar os índices, correu muito bem durante cerca de uma semana. Durante a semana, tenho andado lentamente a limpar a mesa grande, que apenas remove linhas antigas desnecessárias. Até agora eu removi cerca de 300 milhões dos 6 bilhões, e acho que ainda tenho muito mais para ir.

Agora, depois de cerca de uma semana correndo assim, agora ele está correndo muito devagar e não sei qual seria o melhor a ser feito.

Situação atual:

• Dual Xeon
• 192 GB de RAM
• Windows Server 2012 com o SQL Server 2012
• A CPU está atingindo 100% (16 núcleos) - estava usando apenas cerca de 50% antes da desaceleração
• O IO parece não funcionar muito (sem fila)

A tabela grande atualmente tem (não tenho nenhuma informação de fragmentação antes):

• 1x Índice clusterizado: 48% fragmentação
• 1x Índice não agrupado: 36% de fragmentação
• 1x Índice não agrupado: 10% fragmentação
• Eu costumava ter mais dois índices nessa tabela, mas deixei-os há algum tempo

O que você acha que melhor consertaria meu problema

• Reconstrua os índices não agrupados na mesma matriz (suponha que isso resolva o problema, mas demore um longo tempo para fazer como antes. Provavelmente, haverá um problema no futuro próximo, pois ainda estou limpando a mesa)
• Reconstrua os índices não agrupados em uma nova matriz RAID (deve ser corrigido conforme descrito acima, mas pode ser mais rápido)
• Mover os índices não agrupados para uma nova matriz RAID (a opção mais rápida)
• Recrie os dois índices antigos em uma nova matriz RAID (não tenho certeza se isso alivia a pressão de CPU ou IO)

Índices fragmentados causam maior uso da CPU?

Há mais alguma coisa que eu possa estar faltando?

TIA

    

Answer 1

Com base na quantidade de fragmentação que você tem nos seus índices, você deve ir em frente e reconstruí-los. Qualquer índice com mais de 30% de fragmentação deve ser recriado. Eu também me certificaria de atualizar regularmente as estatísticas da tabela (a reconstrução do índice fará isso automaticamente).

Depois de fazer isso, se você estiver ainda vendo uma CPU muito alta e confirmando que é o processo SQLServr.exe, você precisará restringir quais consultas estão usando a CPU e solucioná-los a partir daí.

Você pode executar algo como a consulta a seguir para obter alguns dados agregados sobre as consultas que usam mais CPU:

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
total_logical_writes as [Total Writes],
total_logical_reads as [Total Reads],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Para dados em tempo real, você também pode executar algo assim:

SELECT er.session_id, er.cpu_time, er.reads, er.writes, 
SUBSTRING(st.text, (er.statement_start_offset/2)+1, 
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
WHERE session_id > 50
AND status = 'runnable'
ORDER BY cpu_time desc

Você também pode executar ambos para comparar o tempo real para agregar dados. Ambos devem dar uma idéia sobre o que está usando muita CPU. De lá você quer descobrir por que eles estão correndo tanto tempo. Eles estão fazendo uma tonelada de leituras ou uma tonelada de gravações? Se eles estão fazendo uma tonelada de leituras, isso pode significar que eles estão perdendo alguns índices. Toneladas de gravações podem significar que os índices são realmente o problema.

De qualquer forma, ficar de olho nessas declarações pode lhe dar um ponto de partida.