Prefácio
Frequentemente, usuários, desenvolvedores e administradores do MS SQL Server DBMS encontram problemas de desempenho do banco de dados ou do DBMS como um todo, portanto, o monitoramento do MS SQL Server é muito relevante.
Este artigo é um complemento do artigo e abordará alguns aspectos do monitoramento do MS SQL Server, em particular: como determinar rapidamente quais recursos estão faltando, bem como recomendações para definir sinalizadores de rastreamento.
Para que os seguintes scripts funcionem, você precisa criar um esquema inf no banco de dados desejado da seguinte forma:
Criando um esquema inf
use <имя_БД>;
go
create schema inf;
Método para detectar falta de RAM
O primeiro indicador da falta de RAM é o caso quando uma instância do MS SQL Server consome toda a RAM alocada para ela.
Para isso, criaremos a seguinte representação de inf.vRAM:
Criando a visualização inf.vRAM
CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb] --сколько свободно ОЗУ на сервере в МБ
, a.[RAM_Avail_Percent] --процент свободного ОЗУ на сервере
, a.[Server_physical_memory_Mb] --сколько всего ОЗУ на сервере в МБ
, a.[SQL_server_committed_target_Mb] --сколько всего ОЗУ выделено под MS SQL Server в МБ
, a.[SQL_server_physical_memory_in_use_Mb] --сколько всего ОЗУ потребляет MS SQL Server в данный момент времени в МБ
, a.[SQL_RAM_Avail_Percent] --поцент свободного ОЗУ для MS SQL Server относительно всего выделенного ОЗУ для MS SQL Server
, a.[StateMemorySQL] --достаточно ли ОЗУ для MS SQL Server
, a.[SQL_RAM_Reserve_Percent] --процент выделенной ОЗУ для MS SQL Server относительно всего ОЗУ сервера
--достаточно ли ОЗУ для сервера
, (case when a.[RAM_Avail_Percent]<10 and a.[RAM_Avail_Percent]>5 and a.[TotalAvailOSRam_Mb]<8192 then 'Warning' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Danger' else 'Normal' end) as [StateMemoryServer]
from
(
select cast(a0.available_physical_memory_kb/1024.0 as int) as TotalAvailOSRam_Mb
, cast((a0.available_physical_memory_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [RAM_Avail_Percent]
, a0.system_low_memory_signal_state
, ceiling(b.physical_memory_kb/1024.0) as [Server_physical_memory_Mb]
, ceiling(b.committed_target_kb/1024.0) as [SQL_server_committed_target_Mb]
, ceiling(a.physical_memory_in_use_kb/1024.0) as [SQL_server_physical_memory_in_use_Mb]
, cast(((b.committed_target_kb-a.physical_memory_in_use_kb)/casT(b.committed_target_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Avail_Percent]
, cast((b.committed_target_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Reserve_Percent]
, (case when (ceiling(b.committed_target_kb/1024.0)-1024)<ceiling(a.physical_memory_in_use_kb/1024.0) then 'Warning' else 'Normal' end) as [StateMemorySQL]
from sys.dm_os_sys_memory as a0
cross join sys.dm_os_process_memory as a
cross join sys.dm_os_sys_info as b
cross join sys.dm_os_sys_memory as v
) as a;
Então você pode determinar que uma instância do MS SQL Server consome toda a memória alocada a ela pela seguinte consulta:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Se SQL_server_physical_memory_in_use_Mb for consistentemente maior ou igual a SQL_server_committed_target_Mb, as estatísticas de espera deverão ser verificadas.
Para determinar a falta de RAM por meio de estatísticas de espera, vamos criar a exibição inf.vWaits:
Criando a visualização inf.vWaits
CREATE view [inf].[vWaits] as
WITH [Waits] AS
(SELECT
[wait_type], --имя типа ожидания
[wait_time_ms] / 1000.0 AS [WaitS],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
[signal_wait_time_ms] / 1000.0 AS [SignalS],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
[waiting_tasks_count] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [waiting_tasks_count]>0
and [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
)
, ress as (
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
[W1].[WaitCount] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold
)
SELECT [WaitType]
,MAX([Wait_S]) as [Wait_S]
,MAX([Resource_S]) as [Resource_S]
,MAX([Signal_S]) as [Signal_S]
,MAX([WaitCount]) as [WaitCount]
,MAX([Percentage]) as [Percentage]
,MAX([AvgWait_S]) as [AvgWait_S]
,MAX([AvgRes_S]) as [AvgRes_S]
,MAX([AvgSig_S]) as [AvgSig_S]
FROM ress
group by [WaitType];
Nesse caso, você pode determinar a falta de RAM com a seguinte consulta:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Aqui você precisa prestar atenção aos indicadores Percentage e AvgWait_S. Se eles forem significativos em sua totalidade, há uma probabilidade muito alta de que não haja RAM suficiente para a instância do MS SQL Server. Valores significativos são determinados individualmente para cada sistema. No entanto, você pode começar com o seguinte: Porcentagem>=1 e AvgWait_S>=0.005.
Para enviar indicadores para um sistema de monitoramento (por exemplo, Zabbix), você pode criar as duas consultas a seguir:
- quantos tipos de espera são ocupados pela RAM em porcentagem (a soma de todos esses tipos de espera):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - quantos tipos de espera de RAM levam em milissegundos (o valor máximo de todos os atrasos médios para todos esses tipos de espera):
select coalesce(max([AvgWait_S])*1000, 0.00) as [AvgWait_MS] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' );
Com base na dinâmica dos valores obtidos para esses dois indicadores, podemos concluir se há RAM suficiente para uma instância do MS SQL Server.
Método de detecção de sobrecarga da CPU
Para identificar a falta de tempo do processador, basta utilizar a visão do sistema sys.dm_os_schedulers. Aqui, se o runnable_tasks_count for constantemente maior que 1, haverá uma alta probabilidade de que o número de núcleos não seja suficiente para a instância do MS SQL Server.
Para enviar um indicador para um sistema de monitoramento (por exemplo, Zabbix), você pode criar a seguinte consulta:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Com base na dinâmica dos valores obtidos para este indicador, podemos concluir se há tempo de processador suficiente (o número de núcleos da CPU) para uma instância do MS SQL Server.
No entanto, é importante ter em mente o fato de que as próprias solicitações podem solicitar vários encadeamentos de uma só vez. E às vezes o otimizador não pode estimar corretamente a complexidade da própria consulta. Em seguida, a solicitação pode receber muitos encadeamentos que não podem ser processados ao mesmo tempo em um determinado momento. E isso também causa um tipo de espera associado à falta de tempo do processador e ao crescimento da fila para escalonadores que usam núcleos de CPU específicos, ou seja, o indicador runnable_tasks_count crescerá nessas condições.
Nesse caso, antes de aumentar o número de núcleos da CPU, é necessário configurar corretamente as propriedades de paralelismo da própria instância do MS SQL Server e, a partir da versão 2016, configurar corretamente as propriedades de paralelismo dos bancos de dados necessários:
Aqui você deve prestar atenção aos seguintes parâmetros:
- Grau máximo de paralelismo - define o número máximo de threads que podem ser alocados para cada solicitação (o padrão é 0 - limitado apenas pelo próprio sistema operacional e pela edição do MS SQL Server)
- Limiar de custo para paralelismo - custo estimado de paralelismo (o padrão é 5)
- Max DOP - define o número máximo de threads que podem ser alocados para cada consulta no nível do banco de dados (mas não mais que o valor da propriedade "Max Degree of Parallelism") (o padrão é 0 - limitado apenas pelo próprio sistema operacional e a edição do MS SQL Server, bem como a restrição da propriedade "Grau Máximo de Paralelismo" de toda a instância do MS SQL Server)
Aqui é impossível dar uma receita igualmente boa para todos os casos, ou seja, você precisa analisar consultas pesadas.
Por experiência própria, recomendo o seguinte algoritmo de ações para sistemas OLTP para configurar propriedades de paralelismo:
- primeiro desative o paralelismo definindo o Grau máximo de paralelismo da instância como 1
- analise as solicitações mais pesadas e selecione o número ideal de threads para elas
- defina o grau máximo de paralelismo para o número ideal selecionado de threads obtido na etapa 2 e, para bancos de dados específicos, defina o valor máximo de DOP obtido na etapa 2 para cada banco de dados
- analise as solicitações mais pesadas e identifique o efeito negativo do multithreading. Se for, aumente o limite de custo para paralelismo.
Para sistemas como 1C, Microsoft CRM e Microsoft NAV, na maioria dos casos, a proibição de multithreading é adequada
Além disso, se houver uma edição Standard, na maioria dos casos a proibição de multithreading é adequada devido ao fato de que esta edição é limitada no número de núcleos da CPU.
Para sistemas OLAP, o algoritmo descrito acima não é adequado.
Por experiência própria, recomendo o seguinte algoritmo de ações para sistemas OLAP para configurar propriedades de paralelismo:
- analise as solicitações mais pesadas e selecione o número ideal de threads para elas
- defina o grau máximo de paralelismo para o número ideal selecionado de threads obtido na etapa 1 e, para bancos de dados específicos, defina o valor máximo de DOP obtido na etapa 1 para cada banco de dados
- analise as consultas mais pesadas e identifique o efeito negativo de limitar a simultaneidade. Se for, diminua o valor Limite de custo para paralelismo ou repita as etapas 1-2 deste algoritmo
Ou seja, para sistemas OLTP, passamos de single-threading para multi-threading, e para sistemas OLAP, ao contrário, passamos de multi-threading para single-threading. Assim, você pode escolher as configurações ideais de paralelismo para um banco de dados específico e toda a instância do MS SQL Server.
Também é importante entender que as configurações das propriedades de paralelismo precisam ser alteradas ao longo do tempo, com base nos resultados do monitoramento do desempenho do MS SQL Server.
Diretrizes para definir sinalizadores de rastreamento
Com base em minha própria experiência e na experiência de meus colegas, para um desempenho ideal, recomendo definir os seguintes sinalizadores de rastreamento no nível de execução do serviço MS SQL Server para as versões 2008-2016:
- 610 - Log reduzido de inserções em tabelas indexadas. Pode ajudar com inserções em tabelas com muitos registros e muitas transações, com longas esperas frequentes de WRITELOG para alterações nos índices
- 1117 - Se um arquivo em um grupo de arquivos atender aos requisitos de limite de crescimento automático, todos os arquivos no grupo de arquivos aumentarão
- 1118 - Obriga todos os objetos a serem localizados em extensões diferentes (proibição de extensões mistas), o que minimiza a necessidade de escanear a página SGAM, que é utilizada para rastrear extensões mistas
- 1224 - Desativa a escalação de bloqueio com base no número de bloqueios. No entanto, o uso excessivo de memória pode acionar a escalação de bloqueio
- 2371 - Altera o limite fixo de atualização automática de estatísticas para o limite dinâmico de atualização automática de estatísticas. Importante para atualizar planos de consulta para tabelas grandes, onde uma contagem incorreta de registros resulta em planos de execução errados
- 3226 - Suprime mensagens de sucesso de backup no log de erros
- 4199 - Inclui alterações no otimizador de consulta lançado em CUs e SQL Server Service Packs
- 6532-6534 - Inclui melhorias de desempenho para operações de consulta em tipos de dados espaciais
- 8048 - Converte objetos de memória particionada NUMA em objetos particionados pela CPU
- 8780 - Habilita alocação de tempo adicional para planejamento de consulta. Algumas requisições sem este sinalizador podem ser rejeitadas por não terem um plano de consulta (erro muito raro)
- 8780 - 9389 - Habilita buffer de memória de concessão dinâmica adicional para instruções de modo de lote, o que permite que o operador do modo de lote solicite memória adicional e evite mover dados para tempdb se houver memória adicional disponível
Também antes de 2016, é útil ativar o sinalizador de rastreamento 2301, que permite otimizações aprimoradas de suporte à decisão e, portanto, ajuda na escolha de planos de consulta mais corretos. No entanto, a partir da versão 2016, geralmente tem um efeito negativo em tempos de execução de consulta gerais bastante longos.
Além disso, para sistemas com muitos índices (por exemplo, para bancos de dados 1C), recomendo ativar o sinalizador de rastreamento 2330, que desativa a coleta de uso de índice, o que geralmente tem um efeito positivo no sistema.
Para obter mais informações sobre sinalizadores de rastreamento, consulte
No link acima, também é importante considerar as versões e compilações do MS SQL Server, pois para as versões mais recentes, alguns sinalizadores de rastreamento são ativados por padrão ou não têm efeito.
Você pode ativar e desativar o sinalizador de rastreamento com os comandos DBCC TRACEON e DBCC TRACEOFF, respectivamente. Para mais detalhes consulte
Você pode obter o status dos sinalizadores de rastreamento usando o comando DBCC TRACESTATUS:
Para que os sinalizadores de rastreamento sejam incluídos na inicialização automática do serviço MS SQL Server, você deve acessar o SQL Server Configuration Manager e adicionar esses sinalizadores de rastreamento via -T nas propriedades do serviço:
Resultados de
Neste artigo, foram analisados alguns aspectos do monitoramento do MS SQL Server, com a ajuda dos quais você pode identificar rapidamente a falta de RAM e tempo livre de CPU, além de vários outros problemas menos óbvios. Os sinalizadores de rastreamento mais comumente usados foram revisados.
Fontes:
»
»
»
»
»
»
»
Fonte: habr.com