Prefacio
Moitas veces, os usuarios, desenvolvedores e administradores do DBMS de MS SQL Server atopan problemas de rendemento da base de datos ou do DBMS no seu conxunto, polo que a vixilancia de MS SQL Server é moi relevante.
Este artigo é unha adición ao artigo e cubrirá algúns aspectos do seguimento de MS SQL Server, en particular: como determinar rapidamente que recursos faltan, así como recomendacións para establecer marcas de rastrexo.
Para que funcionen os seguintes scripts, cómpre crear un esquema inf na base de datos desexada do seguinte xeito:
Creando un esquema inf
use <имя_БД>;
go
create schema inf;
Método para detectar falta de RAM
O primeiro indicador da falta de memoria RAM é o caso cando unha instancia de MS SQL Server consume toda a memoria RAM asignada a ela.
Para iso, crearemos a seguinte representación de inf.vRAM:
Creando a vista 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;
A continuación, pode determinar que unha instancia de MS SQL Server consume toda a memoria que lle asignou a 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 é constantemente maior ou igual a SQL_server_committed_target_Mb, deberían comprobarse as estatísticas de espera.
Para determinar a falta de RAM mediante estatísticas de espera, creemos a vista inf.vWaits:
Creando a vista 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];
Neste caso, pode determinar a falta de RAM coa seguinte consulta:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Aquí cómpre prestar atención aos indicadores Porcentaxe e AvgWait_S. Se son significativos na súa totalidade, entón hai unha probabilidade moi alta de que non haxa suficiente RAM para a instancia de MS SQL Server. Os valores significativos determínanse individualmente para cada sistema. Non obstante, pode comezar polo seguinte: Porcentaxe>=1 e AvgWait_S>=0.005.
Para emitir indicadores a un sistema de seguimento (por exemplo, Zabbix), pode crear as dúas consultas seguintes:
- cantos tipos de esperas ocupa a RAM en porcentaxe (a suma de todos estes tipos de esperas):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - cantos tipos de espera RAM levan en milisegundos (o valor máximo de todos os atrasos medios para todos os 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' );
En base á dinámica dos valores obtidos para estes dous indicadores, podemos concluír se hai RAM suficiente para unha instancia de MS SQL Server.
Método de detección de sobrecarga da CPU
Para identificar a falta de tempo do procesador, abonda con utilizar a vista do sistema sys.dm_os_schedulers. Aquí, se o runnable_tasks_count é constantemente maior que 1, entón hai unha alta probabilidade de que o número de núcleos non sexa suficiente para a instancia de MS SQL Server.
Para enviar un indicador a un sistema de monitorización (por exemplo, Zabbix), pode crear a seguinte consulta:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
En base á dinámica dos valores obtidos para este indicador, podemos concluír se hai tempo de procesador suficiente (número de núcleos de CPU) para unha instancia de MS SQL Server.
Non obstante, é importante ter en conta o feito de que as propias solicitudes poden solicitar varios fíos á vez. E ás veces o optimizador non pode estimar correctamente a complexidade da propia consulta. Entón, é posible que a solicitude teña demasiados fíos que non se poidan procesar ao mesmo tempo nun momento determinado. E isto tamén provoca un tipo de espera asociado á falta de tempo do procesador e ao crecemento da cola para os programadores que usan núcleos de CPU específicos, é dicir, o indicador runnable_tasks_count crecerá nesas condicións.
Neste caso, antes de aumentar o número de núcleos de CPU, é necesario configurar correctamente as propiedades de paralelismo da propia instancia de MS SQL Server e, a partir da versión 2016, configurar correctamente as propiedades de paralelismo das bases de datos requiridas:
Aquí debes prestar atención aos seguintes parámetros:
- Grao máximo de paralelismo: establece o número máximo de fíos que se poden asignar a cada solicitude (o valor predeterminado é 0, limitado só polo propio sistema operativo e a edición de MS SQL Server)
- Limiar de custo para o paralelismo: custo estimado do paralelismo (o valor predeterminado é 5)
- DOP máximo: establece o número máximo de fíos que se poden asignar a cada consulta a nivel de base de datos (pero non máis que o valor da propiedade "Grao máximo de paralelismo") (o valor predeterminado é 0, limitado só polo propio sistema operativo e a edición de MS SQL Server, así como a restrición da propiedade "Grado máximo de paralelismo" de toda a instancia de MS SQL Server)
Aquí é imposible dar unha receita igual de boa para todos os casos, é dicir, cómpre analizar consultas pesadas.
Desde a miña propia experiencia, recomendo o seguinte algoritmo de accións para sistemas OLTP para configurar propiedades de paralelismo:
- primeiro desactive o paralelismo configurando o Grao máximo de paralelismo para toda a instancia en 1
- analizar as solicitudes máis pesadas e seleccionar o número óptimo de fíos para elas
- Estableza o grao máximo de paralelismo ao número óptimo seleccionado de fíos obtidos do paso 2 e, para bases de datos específicas, configure o valor DOP máximo obtido do paso 2 para cada base de datos
- analizar as solicitudes máis pesadas e identificar o efecto negativo do multithreading. Se é así, aumenta o limiar de custos para o paralelismo.
Para sistemas como 1C, Microsoft CRM e Microsoft NAV, na maioría dos casos, é adecuado prohibir o multithreading
Ademais, se hai unha edición estándar, na maioría dos casos a prohibición do multithreading é adecuada debido ao feito de que esta edición está limitada no número de núcleos de CPU.
Para sistemas OLAP, o algoritmo descrito anteriormente non é adecuado.
Desde a miña propia experiencia, recomendo o seguinte algoritmo de accións para sistemas OLAP para configurar propiedades de paralelismo:
- analizar as solicitudes máis pesadas e seleccionar o número óptimo de fíos para elas
- Estableza o grao máximo de paralelismo ao número óptimo seleccionado de fíos obtidos do paso 1 e, para bases de datos específicas, configure o valor DOP máximo obtido do paso 1 para cada base de datos
- analizar as consultas máis pesadas e identificar o efecto negativo de limitar a concorrencia. Se é así, baixa o valor Limiar de custo para o paralelismo ou repite os pasos 1-2 deste algoritmo
É dicir, para os sistemas OLTP pasamos de single-threading a multithreading, e para os sistemas OLAP, pola contra, pasamos de multithreading a single-threading. Así, pode escoller a configuración óptima de paralelismo tanto para unha base de datos específica como para a instancia completa de MS SQL Server.
Tamén é importante entender que a configuración das propiedades de paralelismo debe cambiarse co paso do tempo, en función dos resultados do seguimento do rendemento de MS SQL Server.
Directrices para establecer bandeiras de rastrexo
Desde a miña propia experiencia e a experiencia dos meus compañeiros, para un rendemento óptimo, recomendo establecer as seguintes marcas de rastrexo no nivel de execución do servizo MS SQL Server para as versións 2008-2016:
- 610 - Rexistro reducido de insercións en táboas indexadas. Pode axudar con insercións en táboas con moitos rexistros e moitas transaccións, con frecuentes esperas longas de WRITELOG para cambios nos índices
- 1117 - Se un ficheiro dun grupo de ficheiros cumpre os requisitos do limiar de crecemento automático, todos os ficheiros do grupo de ficheiros crecen
- 1118 - Obriga a que todos os obxectos se localicen en diferentes extensións (prohibición de extensións mixtas), o que minimiza a necesidade de escanear a páxina SGAM, que se usa para rastrexar extensións mixtas
- 1224 - Desactiva a escalada de bloqueos en función do número de bloqueos. Non obstante, o uso excesivo da memoria pode provocar a escalada do bloqueo
- 2371 - Cambia o limiar fixo de actualización automática de estatísticas polo limiar de actualización automática dinámica de estatísticas. Importante para actualizar os plans de consulta para táboas grandes, onde un reconto incorrecto de rexistros produce plans de execución erróneos
- 3226 - Suprime as mensaxes de éxito da copia de seguridade no rexistro de erros
- 4199 - Inclúe cambios no optimizador de consultas lanzado en CU e Service Packs de SQL Server
- 6532-6534 - Inclúe melloras de rendemento para operacións de consulta sobre tipos de datos espaciais
- 8048 - Converte obxectos de memoria particionada NUMA en obxectos particionados por CPU
- 8780: activa a asignación de tempo adicional para a planificación de consultas. Algunhas solicitudes sen esta marca poden ser rexeitadas porque non teñen un plan de consulta (erro moi raro)
- 8780 - 9389 - Activa un búfer de memoria de concesión dinámica adicional para as instrucións do modo por lotes, o que permite que o operador do modo por lotes solicite máis memoria e evite mover os datos a tempdb se hai máis memoria dispoñible
Tamén antes de 2016, é útil habilitar a marca de rastrexo 2301, que permite optimizacións de apoio á decisión melloradas e, polo tanto, axuda a elixir plans de consulta máis correctos. Non obstante, a partir da versión 2016, adoita ter un efecto negativo nos tempos xerais de execución de consultas bastante longos.
Ademais, para sistemas con moitos índices (por exemplo, para bases de datos 1C), recomendo habilitar a marca de rastrexo 2330, que desactiva a recollida de uso de índices, que xeralmente ten un efecto positivo no sistema.
Para obter máis información sobre as bandeiras de rastrexo, consulte
Desde a ligazón anterior, tamén é importante ter en conta as versións e compilacións de MS SQL Server, xa que para as versións máis recentes, algunhas marcas de rastrexo están habilitadas por defecto ou non teñen ningún efecto.
Pode activar e desactivar a marca de rastrexo cos comandos DBCC TRACEON e DBCC TRACEOFF, respectivamente. Para máis detalles ver
Podes obter o estado das marcas de rastrexo usando o comando DBCC TRACESTATUS:
Para que os indicadores de rastrexo se inclúan no inicio automático do servizo MS SQL Server, debe ir ao Xestor de configuración de SQL Server e engadir estes indicadores de rastrexo mediante -T nas propiedades do servizo:
Resultados de
Neste artigo, analizáronse algúns aspectos da monitorización de MS SQL Server, coa axuda dos cales pode identificar rapidamente a falta de memoria RAM e tempo de CPU libre, así como unha serie de outros problemas menos obvios. Revisáronse as bandeiras de rastrexo máis utilizadas.
Fontes:
»
»
»
»
»
»
»
Fonte: www.habr.com