Avant-propos
Très souvent, les utilisateurs, développeurs et administrateurs du SGBD MS SQL Server rencontrent des problèmes de performances de la base de données ou du SGBD dans son ensemble, la surveillance de MS SQL Server est donc très pertinente.
Cet article est un complément à l'article et il abordera certains aspects de la surveillance de MS SQL Server, en particulier : comment déterminer rapidement les ressources manquantes, ainsi que des recommandations pour définir des indicateurs de trace.
Pour que les scripts suivants fonctionnent, vous devez créer un schéma inf dans la base de données souhaitée comme suit :
Création d'un schéma inf
use <имя_БД>;
go
create schema inf;
Méthode de détection d'un manque de RAM
Le premier indicateur du manque de RAM est le cas où une instance de MS SQL Server consomme toute la RAM qui lui est allouée.
Pour ce faire, nous allons créer la représentation suivante de inf.vRAM :
Création de la vue 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;
Ensuite, vous pouvez déterminer qu'une instance de MS SQL Server consomme toute la mémoire qui lui est allouée par la requête suivante :
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Si SQL_server_physical_memory_in_use_Mb est systématiquement supérieur ou égal à SQL_server_committed_target_Mb, les statistiques d'attente doivent être vérifiées.
Pour déterminer le manque de RAM grâce aux statistiques d'attente, créons la vue inf.vWaits :
Création de la vue 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];
Dans ce cas, vous pouvez déterminer le manque de RAM avec la requête suivante :
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Ici, vous devez faire attention aux indicateurs Percentage et AvgWait_S. S'ils sont significatifs dans leur totalité, alors il y a une très forte probabilité qu'il n'y ait pas assez de RAM pour l'instance MS SQL Server. Les valeurs significatives sont déterminées individuellement pour chaque système. Cependant, vous pouvez commencer par ce qui suit : Percentage>=1 et AvgWait_S>=0.005.
Pour générer des indicateurs vers un système de surveillance (par exemple, Zabbix), vous pouvez créer les deux requêtes suivantes :
- combien de types d'attentes sont occupés par la RAM en pourcentage (la somme de tous ces types d'attentes):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - combien de types d'attente RAM prennent en millisecondes (la valeur maximale de tous les retards moyens pour tous ces types d'attente):
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' );
Sur la base de la dynamique des valeurs obtenues pour ces deux indicateurs, nous pouvons conclure s'il y a suffisamment de RAM pour une instance de MS SQL Server.
Méthode de détection de surcharge du processeur
Pour identifier le manque de temps processeur, il suffit d'utiliser la vue système sys.dm_os_schedulers. Ici, si runnable_tasks_count est constamment supérieur à 1, il y a une forte probabilité que le nombre de cœurs ne soit pas suffisant pour l'instance MS SQL Server.
Pour envoyer un indicateur à un système de surveillance (par exemple, Zabbix), vous pouvez créer la requête suivante :
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Sur la base de la dynamique des valeurs obtenues pour cet indicateur, nous pouvons conclure s'il y a suffisamment de temps processeur (le nombre de cœurs de processeur) pour une instance de MS SQL Server.
Cependant, il est important de garder à l'esprit le fait que les requêtes elles-mêmes peuvent demander plusieurs threads à la fois. Et parfois, l'optimiseur ne peut pas estimer correctement la complexité de la requête elle-même. Ensuite, la requête peut se voir allouer trop de threads qui ne peuvent pas être traités en même temps à l'instant donné. Et cela provoque également un type d'attente associé à un manque de temps processeur et à une croissance de la file d'attente pour les planificateurs qui utilisent des cœurs de processeur spécifiques, c'est-à-dire que l'indicateur runnable_tasks_count augmentera dans de telles conditions.
Dans ce cas, avant d'augmenter le nombre de cœurs CPU, il est nécessaire de configurer correctement les propriétés de parallélisme de l'instance MS SQL Server elle-même, et à partir de la version 2016, de configurer correctement les propriétés de parallélisme des bases de données requises :
Ici, vous devez faire attention aux paramètres suivants :
- Max Degree of Parallelism - définit le nombre maximum de threads pouvant être alloués à chaque requête (la valeur par défaut est 0 - limitée uniquement par le système d'exploitation lui-même et l'édition de MS SQL Server)
- Seuil de coût pour le parallélisme - coût estimé du parallélisme (la valeur par défaut est 5)
- Max DOP - définit le nombre maximum de threads pouvant être alloués à chaque requête au niveau de la base de données (mais pas plus que la valeur de la propriété "Max Degree of Parallelism") (la valeur par défaut est 0 - limitée uniquement par le système d'exploitation lui-même et l'édition de MS SQL Server, ainsi que la restriction sur la propriété "Max Degree of Parallelism" de toute l'instance de MS SQL Server)
Ici, il est impossible de donner une recette aussi bonne pour tous les cas, c'est-à-dire qu'il faut analyser des requêtes lourdes.
D'après ma propre expérience, je recommande l'algorithme d'actions suivant pour les systèmes OLTP pour la configuration des propriétés de parallélisme :
- désactivez d'abord le parallélisme en définissant le degré maximal de parallélisme à l'échelle de l'instance sur 1
- analyser les requêtes les plus lourdes et sélectionner le nombre optimal de threads pour celles-ci
- définissez le degré maximal de parallélisme sur le nombre optimal sélectionné de threads obtenus à l'étape 2 et, pour des bases de données spécifiques, définissez la valeur DOP maximale obtenue à l'étape 2 pour chaque base de données
- analyser les requêtes les plus lourdes et identifier l'effet négatif du multithreading. Si c'est le cas, augmentez le seuil de coût pour le parallélisme.
Pour les systèmes tels que 1C, Microsoft CRM et Microsoft NAV, dans la plupart des cas, l'interdiction du multithreading convient
De plus, s'il existe une édition Standard, dans la plupart des cas, l'interdiction du multithreading convient en raison du fait que cette édition est limitée en nombre de cœurs de processeur.
Pour les systèmes OLAP, l'algorithme décrit ci-dessus n'est pas adapté.
D'après ma propre expérience, je recommande l'algorithme d'actions suivant pour les systèmes OLAP pour la configuration des propriétés de parallélisme :
- analyser les requêtes les plus lourdes et sélectionner le nombre optimal de threads pour celles-ci
- définissez le degré maximal de parallélisme sur le nombre optimal sélectionné de threads obtenus à l'étape 1 et, pour des bases de données spécifiques, définissez la valeur DOP maximale obtenue à l'étape 1 pour chaque base de données
- analyser les requêtes les plus lourdes et identifier l'effet négatif de la limitation de la simultanéité. Si c'est le cas, réduisez la valeur du seuil de coût pour le parallélisme ou répétez les étapes 1 et 2 de cet algorithme.
Autrement dit, pour les systèmes OLTP, nous passons du mono-threading au multi-threading, et pour les systèmes OLAP, au contraire, nous passons du multi-threading au single-threading. Ainsi, vous pouvez choisir les paramètres de parallélisme optimaux pour une base de données spécifique et l'intégralité de l'instance de MS SQL Server.
Il est également important de comprendre que les paramètres des propriétés de parallélisme doivent être modifiés au fil du temps, en fonction des résultats de la surveillance des performances de MS SQL Server.
Instructions pour la définition des indicateurs de trace
D'après ma propre expérience et celle de mes collègues, pour des performances optimales, je recommande de définir les indicateurs de trace suivants au niveau de l'exécution du service MS SQL Server pour les versions 2008-2016 :
- 610 - Réduction de la journalisation des insertions dans les tables indexées. Peut aider avec des insertions dans des tables avec de nombreux enregistrements et de nombreuses transactions, avec de longues attentes fréquentes de WRITELOG pour les changements d'index
- 1117 - Si un fichier dans un groupe de fichiers répond aux exigences de seuil de croissance automatique, tous les fichiers du groupe de fichiers augmentent
- 1118 - Force tous les objets à se trouver dans des étendues différentes (interdiction des étendues mixtes), ce qui minimise le besoin d'analyser la page SGAM, qui est utilisée pour suivre les étendues mixtes
- 1224 - Désactive l'escalade de verrous en fonction du nombre de verrous. Cependant, une utilisation excessive de la mémoire peut déclencher une escalade de verrouillage
- 2371 - Modifie le seuil fixe de mise à jour automatique des statistiques en seuil dynamique de mise à jour automatique des statistiques. Important pour la mise à jour des plans de requête pour les grandes tables, où un nombre incorrect d'enregistrements entraîne des plans d'exécution erronés
- 3226 - Supprime les messages de réussite de la sauvegarde dans le journal des erreurs
- 4199 - Inclut les modifications apportées à l'optimiseur de requête publié dans les CU et les Service Packs SQL Server
- 6532-6534 - Inclut des améliorations de performances pour les opérations de requête sur les types de données spatiales
- 8048 - Convertit les objets mémoire partitionnés NUMA en objets partitionnés CPU
- 8780 - Active l'allocation de temps supplémentaire pour la planification des requêtes. Certaines requêtes sans ce flag peuvent être rejetées car elles n'ont pas de plan de requête (bug très rare)
- 8780 - 9389 - Active un tampon de mémoire d'attribution dynamique supplémentaire pour les instructions en mode batch, ce qui permet à l'opérateur en mode batch de demander de la mémoire supplémentaire et d'éviter de déplacer des données vers tempdb si de la mémoire supplémentaire est disponible
Également avant 2016, il est utile d'activer l'indicateur de trace 2301, qui permet des optimisations d'aide à la décision améliorées et aide ainsi à choisir des plans de requête plus corrects. Cependant, depuis la version 2016, cela a souvent un effet négatif sur les temps d'exécution globaux assez longs des requêtes.
De plus, pour les systèmes qui ont beaucoup d'index (par exemple, pour les bases de données 1C), je recommande d'activer l'indicateur de trace 2330, qui désactive la collecte de l'utilisation de l'index, ce qui a généralement un effet positif sur le système.
Pour plus d'informations sur les indicateurs de trace, consultez
À partir du lien ci-dessus, il est également important de prendre en compte les versions et les versions de MS SQL Server, car pour les versions plus récentes, certains indicateurs de trace sont activés par défaut ou n'ont aucun effet.
Vous pouvez activer et désactiver l'indicateur de trace avec les commandes DBCC TRACEON et DBCC TRACEOFF, respectivement. Pour plus de détails voir
Vous pouvez obtenir l'état des indicateurs de trace à l'aide de la commande DBCC TRACESTATUS :
Pour que les indicateurs de trace soient inclus dans le démarrage automatique du service MS SQL Server, vous devez vous rendre dans SQL Server Configuration Manager et ajouter ces indicateurs de trace via -T dans les propriétés du service :
Les résultats de
Dans cet article, certains aspects de la surveillance de MS SQL Server ont été analysés, à l'aide desquels vous pouvez identifier rapidement le manque de RAM et de temps CPU libre, ainsi qu'un certain nombre d'autres problèmes moins évidents. Les indicateurs de trace les plus couramment utilisés ont été passés en revue.
Sources:
»
»
»
»
»
»
»
Source: habr.com