prefacio
Muy a menudo, los usuarios, desarrolladores y administradores del DBMS de MS SQL Server encuentran problemas de rendimiento de la base de datos o del DBMS en su conjunto, por lo que el monitoreo de MS SQL Server es muy relevante.
Este artículo es una adición al artículo y cubrirá algunos aspectos de la supervisión de MS SQL Server, en particular: cómo determinar rápidamente qué recursos faltan, así como recomendaciones para establecer indicadores de seguimiento.
Para que funcionen los siguientes scripts, debe crear un esquema inf en la base de datos deseada de la siguiente manera:
Creación de un esquema inf
use <имя_БД>;
go
create schema inf;
Método para detectar falta de RAM
El primer indicador de la falta de RAM es el caso cuando una instancia de MS SQL Server consume toda la RAM asignada.
Para ello, crearemos la siguiente representación de inf.vRAM:
Creación de la 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;
Luego, puede determinar que una instancia de MS SQL Server consume toda la memoria que se le ha asignado mediante la siguiente consulta:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Si SQL_server_physical_memory_in_use_Mb es constantemente mayor o igual que SQL_server_committed_target_Mb, se deben verificar las estadísticas de espera.
Para determinar la falta de RAM a través de las estadísticas de espera, creemos la vista inf.vWaits:
Creación de la 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];
En este caso, puedes determinar la falta de RAM con la siguiente consulta:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Aquí debe prestar atención a los indicadores Porcentaje y AvgWait_S. Si son significativos en su totalidad, existe una probabilidad muy alta de que no haya suficiente RAM para la instancia de MS SQL Server. Los valores significativos se determinan individualmente para cada sistema. Sin embargo, puede comenzar con lo siguiente: Porcentaje>=1 y AvgWait_S>=0.005.
Para enviar indicadores a un sistema de monitoreo (por ejemplo, Zabbix), puede crear las siguientes dos consultas:
- cuántos tipos de esperas ocupa la RAM en porcentaje (la suma de todos esos 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' ); - cuántos tipos de espera de RAM tardan en milisegundos (el valor máximo de todos los retrasos promedio para todos esos 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' );
Según la dinámica de los valores obtenidos para estos dos indicadores, podemos concluir si hay suficiente RAM para una instancia de MS SQL Server.
Método de detección de sobrecarga de la CPU
Para identificar la falta de tiempo de procesador, basta con utilizar la vista del sistema sys.dm_os_schedulers. Aquí, si runnable_tasks_count es constantemente mayor que 1, entonces existe una alta probabilidad de que la cantidad de núcleos no sea suficiente para la instancia de MS SQL Server.
Para enviar un indicador a un sistema de monitoreo (por ejemplo, Zabbix), puede crear la siguiente consulta:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Según la dinámica de los valores obtenidos para este indicador, podemos concluir si hay suficiente tiempo de procesador (la cantidad de núcleos de CPU) para una instancia de MS SQL Server.
Sin embargo, es importante tener en cuenta el hecho de que las propias solicitudes pueden solicitar varios subprocesos a la vez. Y a veces el optimizador no puede estimar correctamente la complejidad de la consulta en sí. Entonces, a la solicitud se le pueden asignar demasiados subprocesos que no se pueden procesar al mismo tiempo en el momento dado. Y esto también provoca una especie de espera asociada a la falta de tiempo de procesador y el crecimiento de la cola para los programadores que usan núcleos de CPU específicos, es decir, el indicador runnable_tasks_count crecerá en tales condiciones.
En este caso, antes de aumentar la cantidad de núcleos de la CPU, es necesario configurar correctamente las propiedades de paralelismo de la propia instancia de MS SQL Server y, a partir de la versión 2016, configurar correctamente las propiedades de paralelismo de las bases de datos requeridas:
Aquí debe prestar atención a los siguientes parámetros:
- Grado máximo de paralelismo: establece el número máximo de subprocesos que se pueden asignar a cada solicitud (el valor predeterminado es 0, limitado solo por el propio sistema operativo y la edición de MS SQL Server)
- Umbral de costo para el paralelismo: costo estimado del paralelismo (el valor predeterminado es 5)
- Max DOP: establece la cantidad máxima de subprocesos que se pueden asignar a cada consulta en el nivel de la base de datos (pero no más que el valor de la propiedad "Grado máximo de paralelismo") (el valor predeterminado es 0, limitado solo por el propio sistema operativo y la edición de MS SQL Server, así como la restricción de la propiedad "Grado máximo de paralelismo" de toda la instancia de MS SQL Server)
Aquí es imposible dar una receta igualmente buena para todos los casos, es decir, necesita analizar consultas pesadas.
Desde mi propia experiencia, recomiendo el siguiente algoritmo de acciones para sistemas OLTP para configurar propiedades de paralelismo:
- primero deshabilite el paralelismo configurando el grado máximo de paralelismo de toda la instancia en 1
- analice las solicitudes más pesadas y seleccione el número óptimo de subprocesos para ellas
- establezca el grado máximo de paralelismo en el número óptimo seleccionado de subprocesos obtenidos en el paso 2 y, para bases de datos específicas, establezca el valor máximo de DOP obtenido en el paso 2 para cada base de datos
- analice las solicitudes más pesadas e identifique el efecto negativo de los subprocesos múltiples. Si es así, aumente el Umbral de costo para el paralelismo.
Para sistemas como 1C, Microsoft CRM y Microsoft NAV, en la mayoría de los casos, es adecuado prohibir los subprocesos múltiples.
Además, si hay una edición estándar, en la mayoría de los casos la prohibición de subprocesos múltiples es adecuada debido a que esta edición está limitada en la cantidad de núcleos de CPU.
Para los sistemas OLAP, el algoritmo descrito anteriormente no es adecuado.
Desde mi propia experiencia, recomiendo el siguiente algoritmo de acciones para sistemas OLAP para configurar propiedades de paralelismo:
- analice las solicitudes más pesadas y seleccione el número óptimo de subprocesos para ellas
- establezca el grado máximo de paralelismo en el número óptimo seleccionado de subprocesos obtenidos en el paso 1 y, para bases de datos específicas, establezca el valor máximo de DOP obtenido en el paso 1 para cada base de datos
- analice las consultas más pesadas e identifique el efecto negativo de limitar la concurrencia. Si es así, reduzca el valor del Umbral de costo para el paralelismo o repita los pasos 1 y 2 de este algoritmo.
Es decir, para los sistemas OLTP pasamos de un solo subproceso a un subproceso múltiple, y para los sistemas OLAP, por el contrario, pasamos de un subproceso múltiple a un subproceso único. Por lo tanto, puede elegir la configuración de paralelismo óptima tanto para una base de datos específica como para toda la instancia de MS SQL Server.
También es importante comprender que la configuración de las propiedades de paralelismo debe cambiarse con el tiempo, en función de los resultados de la supervisión del rendimiento de MS SQL Server.
Directrices para establecer indicadores de seguimiento
Desde mi propia experiencia y la experiencia de mis colegas, para un rendimiento óptimo, recomiendo configurar los siguientes indicadores de rastreo en el nivel de ejecución del servicio MS SQL Server para las versiones 2008-2016:
- 610 - Registro reducido de inserciones en tablas indexadas. Puede ayudar con inserciones en tablas con muchos registros y muchas transacciones, con largas esperas frecuentes de WRITELOG para cambios en los índices
- 1117 - Si un archivo en un grupo de archivos cumple con los requisitos de umbral de crecimiento automático, todos los archivos en el grupo de archivos crecen
- 1118 - Obliga a todos los objetos a ubicarse en diferentes extensiones (prohibición de extensiones mixtas), lo que minimiza la necesidad de escanear la página SGAM, que se utiliza para rastrear extensiones mixtas
- 1224: deshabilita la escalada de bloqueo en función del número de bloqueos. Sin embargo, el uso excesivo de memoria puede desencadenar una escalada de bloqueo
- 2371: cambia el umbral de actualización de estadísticas automáticas fijas por el umbral de actualización de estadísticas automáticas dinámicas. Importante para actualizar planes de consulta para tablas grandes, donde un recuento incorrecto de registros da como resultado planes de ejecución erróneos
- 3226: suprime los mensajes de éxito de la copia de seguridad en el registro de errores
- 4199: incluye cambios en el optimizador de consultas publicado en CU y SQL Server Service Packs
- 6532-6534: incluye mejoras de rendimiento para operaciones de consulta en tipos de datos espaciales
- 8048 - Convierte objetos de memoria particionada NUMA en objetos particionados de CPU
- 8780: habilita la asignación de tiempo adicional para la planificación de consultas. Algunas solicitudes sin este indicador pueden rechazarse porque no tienen un plan de consulta (error muy raro)
- 8780 - 9389 - Habilita el búfer de memoria de concesión dinámica adicional para declaraciones de modo por lotes, lo que permite que el operador del modo por lotes solicite memoria adicional y evite mover datos a tempdb si hay memoria adicional disponible
También antes de 2016, es útil habilitar el indicador de seguimiento 2301, que permite optimizaciones de soporte de decisiones mejoradas y, por lo tanto, ayuda a elegir planes de consulta más correctos. Sin embargo, a partir de la versión 2016, a menudo tiene un efecto negativo en los tiempos generales de ejecución de consultas bastante largos.
Además, para sistemas con muchos índices (por ejemplo, para bases de datos 1C), recomiendo habilitar el indicador de seguimiento 2330, que deshabilita la recopilación del uso de índices, lo que generalmente tiene un efecto positivo en el sistema.
Para obtener más información acerca de las marcas de seguimiento, consulte
Desde el enlace anterior, también es importante considerar las versiones y compilaciones de MS SQL Server, ya que para las versiones más nuevas, algunas marcas de seguimiento están habilitadas de forma predeterminada o no tienen ningún efecto.
Puede activar y desactivar el indicador de rastreo con los comandos DBCC TRACEON y DBCC TRACEOFF, respectivamente. Para más detalles ver
Puede obtener el estado de las marcas de seguimiento mediante el comando DBCC TRACESTATUS:
Para que los indicadores de seguimiento se incluyan en el inicio automático del servicio de MS SQL Server, debe ir al Administrador de configuración de SQL Server y agregar estos indicadores de seguimiento a través de -T en las propiedades del servicio:
resultados
En este artículo, se analizaron algunos aspectos del monitoreo de MS SQL Server, con la ayuda de los cuales puede identificar rápidamente la falta de RAM y tiempo libre de CPU, así como una serie de otros problemas menos obvios. Se han revisado los indicadores de seguimiento más utilizados.
Fuentes:
»
»
»
»
»
»
»
Fuente: habr.com