• Articles
• admin

recentemente, trabalhamos em problemas de desempenho do SQL server para alguns de nossos grandes clientes que executam o SQL server como banco de dados. Neste artigo, resumirei o que aprendi com o processo, descrevendo algumas etapas que podemos tomar para resolver problemas de desempenho.

Verifique a configuração do SQL Server

verifique se o servidor de banco de dados está configurado com recursos suficientes, como número de núcleos de CPU e quantidade de memória.

Para verificar a configuração do servidor, você pode abrir “Informações do Sistema” view:

Para verificar a configuração de memória do SQL Server,

1. Inicie o SQL Server Management Studio.
2. clique com o botão direito do mouse na instância do banco de dados e selecione “Propriedades”.
3. clique na tabela “memória” na janela pop-up “propriedades do servidor”.
4. verifique as configurações de memória.

certifique-se de que o modo instantâneo esteja ativado

verifique se o modo instantâneo está ativado para o banco de dados. Para evitar o bloqueio do SQL Server, o sinalizador snapshot mode deve estar ativado. Execute a seguinte consulta para verificar se o sinalizador está ativado:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'

se a consulta retornar ‘1’, o modo instantâneo já estará ativado. Caso contrário, execute a seguinte consulta para ativá-la.

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

relacionados: Definir e implementar linhas de Base de requisitos

verifique os índices do banco de dados

verifique os índices do banco de dados para garantir que não haja índices ausentes. Execute a seguinte consulta para listar todos os índices no banco de dados (crédito: tsql – lista de todos os índices & colunas de índice no SQL Server DB-Stack Overflow):

SELECT t.name TableName, col.name ColumnName, ind.name IndexNameFROM sys.indexes indINNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_idINNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_idINNER JOIN sys.tables t ON ind.object_id = t.object_idWHERE (ind.is_primary_key = 0 AND t.is_ms_shipped = 0)ORDER BY t.name, col.name, ind.name

Salve os resultados da consulta em um arquivo de texto com colunas delimitadas por tabulação para que possam ser importados para um aplicativo de planilha posteriormente.

evite a fragmentação

verifique se os índices do banco de dados não estão fragmentados. Os índices no banco de dados ajudam a acelerar a consulta do banco de dados. Mas quando eles ficam fragmentados, as consultas podem ser muito lentas. Como parte da manutenção do banco de dados, os índices devem ser reorganizados ou reconstruídos regularmente.

execute a seguinte consulta para verificar a porcentagem de fragmentação de todos os índices no banco de dados (crédito: como verificar a fragmentação do índice em índices em um banco de dados):

SELECT dbschemas.Jama Software AS 'Schema', dbtables.Jama Software AS 'Table', dbindexes.Jama Software AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, dbindexes.fill_factorFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables ON dbtables. = indexstats.INNER JOIN sys.schemas dbschemas ON dbtables. = dbschemas.INNER JOIN sys.indexes AS dbindexes ON dbindexes. = indexstats. AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent DESC

aqui está um resultado de consulta de amostra para um banco de dados com índices altamente fragmentados:

para índices com contagens de página superiores a 1000, o “avg_framentation_in_percent” deve ser mantido abaixo de 10%. Não importa muito para pequenos índices. Como regra geral, índices com porcentagens de fragmentação entre 5% e 30% podem ser reorganizados e índices com porcentagens de fragmentação maiores que 30% devem ser reconstruídos.

para reorganizar um único índice:

ALTER INDEX REORGANIZE

para reconstruir um único índice:

ALTER INDEX REBUILD WITH (ONLINE = ON)

você também pode reorganizar ou reconstruir um índice usando o SQL Server Management Studio clicando com o botão direito do mouse no índice que deseja reconstruir ou reorganizar.

se houver muitos índices desfragmentados no banco de dados, você pode executar a seguinte consulta para reconstruir todos eles (crédito: SQL SERVER – 2008 -2005-reconstruir cada índice de todas as tabelas de banco de dados – reconstruir índice com FillFactor). Observe que a consulta pode ser executada por um tempo para um banco de dados grande. Recomenda-se executar a consulta enquanto o banco de dados está ocioso ou offline.

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME()+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'PRINT @sqlEXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO

considere configurar o trabalho de manutenção no SQL Server studio para executar a reorganização do Índice de banco de dados regularmente. Embora a reconstrução de índices no SQL Server provavelmente deva ser feita offline ou quando o sistema estiver ocioso, a reorganização do Índice pode ser feita online. Aqui está um bom artigo sobre este tópico: reconstruir ou reorganizar: manutenção de índice do SQL Server.

para configurar um plano de reorganização de índice no SQL Server, clique com o botão direito em “Gerenciamento”, depois em “planos de Manutenção” e selecione “Novo Plano de manutenção” ou “Assistente de plano de manutenção”. Siga as instruções para criar um plano.

RELACIONADOS: Lançamento de Opções de Gestão em Jama Conectar

Executar Ausente o Relatório de Índice

SQL Server fornece uma ferramenta que analisa atividades de banco de dados e recomenda índices adicionais que podem ajudar com o desempenho da consulta. O relatório poderia nos dar algumas idéias sobre por que certas consultas são lentas.

para gerar o relatório, execute a seguinte consulta após a instância do banco de dados ter sido usada por um tempo (crédito: Não crie cegamente esses índices “ausentes”!):

SELECT d., s = OBJECT_SCHEMA_NAME(d.), o = OBJECT_NAME(d.), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, s.avg_total_user_cost, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dINNER JOIN sys.dm_db_missing_index_groups AS gON d.index_handle = g.index_handleINNER JOIN sys.dm_db_missing_index_group_stats AS sON g.index_group_handle = s.group_handleWHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d., 'IsMsShipped') = 0

leia o artigo encontrando índices ausentes para entender a saída dessa consulta.

monitorar sessões de banco de dados

Use o Monitor de atividades para monitorar sessões de banco de dados. O Microsoft SQL Server Management Studio vem com o Activity Monitor, que pode ser usado para monitorar as sessões do banco de dados.

procure sessões bloqueadas por outras sessões por muito tempo. Clique com o botão direito do mouse na sessão e selecione “Detalhes” para mostrar os detalhes da consulta associada ao processo.

Usar Windows Resource Monitor

Windows Resource Monitor pode ser usado para monitorar a memória e uso da CPU do processo de SQL Server para se certificar de que não há memória suficiente no sistema e a CPU não está saturado. Observe que há um problema conhecido para o SQL Server 2008 em que a figura de memória mostrada para o processo do SQL Server no Resource Monitor não está correta.

Identificar Consultas Lentas

Se nós determinamos que o desempenho não está relacionada aos recursos, o próximo passo é identificar consultas lentas que levam a um desempenho ruim.

a consulta a seguir retorna as 100 principais consultas lentas que são executadas por mais de 300 ms (crédito: consultas de longa duração):

SELECT st.text, qp.query_plan, qs.*FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300

aqui está uma captura de tela de alguns resultados de amostra:

uma Vez reunimos uma lista de consultas lentas, podemos executá-los diretamente no banco de dados e examinar os planos de execução para entender essas consultas.

Outras ferramentas

Aqui está uma lista de outras ferramentas que podem ser usadas para solucionar problemas de SQL Server e aplicativos Java:

• o SQL Server Profiler: pode ser usado para identificar consultas lentas. Você pode iniciá-lo no SQL Server Management Studio
• VisualVM: pode ser usado para monitorar o uso de recursos Java e fazer despejos de thread
• Java Mission Control/Flight recording: monitorar e gravar eventos do sistema
• JProfiler: Identifique chamadas lentas de serviço ou banco de dados (desenvolvimento, não suportado por 8.x implantação)
• Nova Relíquia: pode monitorar o desempenho do banco de dados e registro de consultas lentas

Listas de Verificação

Aqui está uma lista de informações que podem ser coletadas para solucionar problemas de SQL Server:

1. servidor de Banco de dados de configuração (número de núcleos de CPU, memória física, espaço em disco, a versão do Windows Server, versão do SQL Server, SQL Server, configurações de memória)
2. configuração do servidor de aplicativos (número de núcleos de CPU, memória física, espaço em disco, Java configurações de memória)
3. Banco de dados de estatísticas de índice
4. Banco de dados de estatísticas de fragmentação
5. SQL server faltando o relatório de índice
6. servidor de Banco de dados de memória e CPU de estatísticas quando o servidor é lento
7. Operações que fazem com que o sistema seja lenta
8. SQL server lento relatório da consulta
9. estatísticas da tabela de banco de dados
10. alguns despejos de thread de aplicativos Java feitos durante as operações lentas, se aplicável

conclusão

a depuração de problemas de desempenho do SQL server nem sempre é direta. Mas com as ferramentas certas e paciência, devemos ser capazes de entender a causa desses problemas. Espero que a informação neste artigo ajude com isso.