Vorwort
Benutzer, Entwickler und Administratoren des MS SQL Server-DBMS stoßen häufig auf Leistungsprobleme der Datenbank oder des DBMS als Ganzes, daher ist die Überwachung des MS SQL Servers sehr relevant.
Dieser Artikel ist eine Ergänzung zum Artikel Außerdem werden einige Aspekte der Überwachung von MS SQL Server behandelt, insbesondere: Wie Sie schnell feststellen können, welche Ressourcen fehlen, sowie Empfehlungen zum Setzen von Trace-Flags.
Damit die folgenden Skripte funktionieren, müssen Sie wie folgt ein INF-Schema in der gewünschten Datenbank erstellen:
Erstellen eines INF-Schemas
use <имя_БД>;
go
create schema inf;
Methode zur Erkennung von RAM-Mangel
Der erste Indikator für den Mangel an RAM ist der Fall, wenn eine Instanz von MS SQL Server den gesamten ihr zugewiesenen RAM verbraucht.
Dazu erstellen wir die folgende Darstellung von inf.vRAM:
Erstellen der inf.vRAM-Ansicht
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;
Dann können Sie mit der folgenden Abfrage feststellen, dass eine Instanz von MS SQL Server den gesamten ihr zugewiesenen Speicher verbraucht:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Wenn SQL_server_physical_memory_in_use_Mb durchweg größer oder gleich SQL_server_committed_target_Mb ist, sollten die Wartestatistiken überprüft werden.
Um den RAM-Mangel anhand von Wartestatistiken zu ermitteln, erstellen wir die Ansicht „inf.vWaits“:
Erstellen der inf.vWaits-Ansicht
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];
In diesem Fall können Sie den Mangel an RAM mit der folgenden Abfrage ermitteln:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Hier müssen Sie auf die Indikatoren Percentage und AvgWait_S achten. Wenn sie in ihrer Gesamtheit signifikant sind, ist die Wahrscheinlichkeit sehr hoch, dass nicht genügend RAM für die MS SQL Server-Instanz vorhanden ist. Signifikante Werte werden für jedes System individuell ermittelt. Sie können jedoch mit Folgendem beginnen: Percentage>=1 und AvgWait_S>=0.005.
Um Indikatoren an ein Monitoring-System (z. B. Zabbix) auszugeben, können Sie die folgenden zwei Abfragen erstellen:
- Wie viele Arten von Wartevorgängen sind in Prozent vom RAM belegt (die Summe aller dieser Wartearten):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - wie viele RAM-Wartetypen in Millisekunden dauern (der Maximalwert aller durchschnittlichen Verzögerungen für alle solchen Wartetypen):
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' );
Anhand der Dynamik der erhaltenen Werte für diese beiden Indikatoren können wir schließen, ob genügend RAM für eine Instanz von MS SQL Server vorhanden ist.
Methode zur Erkennung von CPU-Überlastung
Um den Mangel an Prozessorzeit zu erkennen, reicht es aus, die Systemansicht sys.dm_os_schedulers zu verwenden. Wenn hier der runnable_tasks_count konstant größer als 1 ist, dann ist die Wahrscheinlichkeit hoch, dass die Anzahl der Kerne für die MS SQL Server-Instanz nicht ausreicht.
Um einen Indikator an ein Überwachungssystem (z. B. Zabbix) auszugeben, können Sie die folgende Abfrage erstellen:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Anhand der Dynamik der erhaltenen Werte für diesen Indikator können wir schließen, ob für eine Instanz von MS SQL Server genügend Prozessorzeit (Anzahl der CPU-Kerne) vorhanden ist.
Es ist jedoch wichtig zu bedenken, dass Anfragen selbst mehrere Threads gleichzeitig anfordern können. Und manchmal kann der Optimierer die Komplexität der Abfrage selbst nicht richtig einschätzen. Dann werden der Anfrage möglicherweise zu viele Threads zugewiesen, die zum gegebenen Zeitpunkt nicht gleichzeitig verarbeitet werden können. Und dies führt auch zu einer Art Wartezeit, die mit einem Mangel an Prozessorzeit und einem Anwachsen der Warteschlange für Scheduler verbunden ist, die bestimmte CPU-Kerne verwenden, d. h. der runnable_tasks_count-Indikator wird unter solchen Bedingungen wachsen.
In diesem Fall ist es vor der Erhöhung der Anzahl der CPU-Kerne erforderlich, die Parallelitätseigenschaften der MS SQL Server-Instanz selbst korrekt zu konfigurieren und ab der Version 2016 die Parallelitätseigenschaften der erforderlichen Datenbanken korrekt zu konfigurieren:
Hierbei sollten Sie auf folgende Parameter achten:
- Max. Parallelitätsgrad – legt die maximale Anzahl von Threads fest, die jeder Anfrage zugewiesen werden können (der Standardwert ist 0 – nur durch das Betriebssystem selbst und die Edition von MS SQL Server begrenzt).
- Kostenschwellenwert für Parallelität – geschätzte Kosten der Parallelität (Standard ist 5)
- Max DOP – legt die maximale Anzahl von Threads fest, die jeder Abfrage auf Datenbankebene zugewiesen werden können (jedoch nicht mehr als der Wert der Eigenschaft „Max Degree of Parallelism“) (Standard ist 0 – nur durch das Betriebssystem selbst begrenzt). der Edition von MS SQL Server, sowie die Einschränkung auf die Eigenschaft „Max Degree of Parallelism“ der gesamten Instanz von MS SQL Server)
Hier ist es unmöglich, für alle Fälle ein gleich gutes Rezept zu geben, d. h. Sie müssen schwere Anfragen analysieren.
Aus eigener Erfahrung empfehle ich folgenden Aktionsalgorithmus für OLTP-Systeme zum Einrichten von Parallelitätseigenschaften:
- Deaktivieren Sie zunächst die Parallelität, indem Sie den instanzweiten maximalen Parallelitätsgrad auf 1 festlegen
- Analysieren Sie die schwersten Anfragen und wählen Sie die optimale Anzahl von Threads für sie aus
- Legen Sie den maximalen Grad der Parallelität auf die ausgewählte optimale Anzahl von Threads fest, die Sie in Schritt 2 erhalten haben, und legen Sie für bestimmte Datenbanken den maximalen DOP-Wert fest, der Sie in Schritt 2 für jede Datenbank erhalten haben
- Analysieren Sie die schwersten Anfragen und identifizieren Sie die negativen Auswirkungen von Multithreading. Wenn dies der Fall ist, erhöhen Sie den Kostenschwellenwert für Parallelität.
Für Systeme wie 1C, Microsoft CRM und Microsoft NAV ist in den meisten Fällen ein Verbot von Multithreading geeignet
Auch wenn es eine Standard-Edition gibt, dann ist in den meisten Fällen das Verbot von Multithreading sinnvoll, da diese Edition in der Anzahl der CPU-Kerne begrenzt ist.
Für OLAP-Systeme ist der oben beschriebene Algorithmus nicht geeignet.
Aus eigener Erfahrung empfehle ich folgenden Aktionsalgorithmus für OLAP-Systeme zum Einrichten von Parallelitätseigenschaften:
- Analysieren Sie die schwersten Anfragen und wählen Sie die optimale Anzahl von Threads für sie aus
- Legen Sie den maximalen Grad der Parallelität auf die ausgewählte optimale Anzahl von Threads fest, die Sie in Schritt 1 erhalten haben, und legen Sie für bestimmte Datenbanken den maximalen DOP-Wert fest, der Sie in Schritt 1 für jede Datenbank erhalten haben
- Analysieren Sie die schwersten Abfragen und identifizieren Sie die negativen Auswirkungen der Einschränkung der Parallelität. Wenn dies der Fall ist, verringern Sie entweder den Kostenschwellenwert für Parallelität oder wiederholen Sie die Schritte 1–2 dieses Algorithmus
Das heißt, bei OLTP-Systemen gehen wir vom Single-Threading zum Multi-Threading über, bei OLAP-Systemen hingegen vom Multi-Threading zum Single-Threading. Somit können Sie die optimalen Parallelitätseinstellungen sowohl für eine bestimmte Datenbank als auch für die gesamte Instanz von MS SQL Server auswählen.
Es ist auch wichtig zu verstehen, dass die Einstellungen der Parallelitätseigenschaften im Laufe der Zeit geändert werden müssen, basierend auf den Ergebnissen der Überwachung der Leistung von MS SQL Server.
Richtlinien zum Setzen von Trace-Flags
Aus eigener Erfahrung und der Erfahrung meiner Kollegen empfehle ich für eine optimale Leistung, die folgenden Trace-Flags auf der Ausführungsebene des MS SQL Server-Dienstes für die Versionen 2008–2016 zu setzen:
- 610 – Reduzierte Protokollierung von Einfügungen in indizierte Tabellen. Kann bei Einfügungen in Tabellen mit vielen Datensätzen und vielen Transaktionen hilfreich sein, wobei WRITELOG häufig lange auf Änderungen in Indizes wartet
- 1117 – Wenn eine Datei in einer Dateigruppe die Schwellenwertanforderungen für das automatische Wachstum erfüllt, wachsen alle Dateien in der Dateigruppe
- 1118 – Erzwingt, dass sich alle Objekte in unterschiedlichen Extents befinden (Verbot gemischter Extents), wodurch die Notwendigkeit minimiert wird, die SGAM-Seite zu scannen, die zum Verfolgen gemischter Extents verwendet wird
- 1224 – Deaktiviert die Sperreskalation basierend auf der Anzahl der Sperren. Eine übermäßige Speichernutzung kann jedoch eine Sperreskalation auslösen
- 2371 – Ändert den festen Schwellenwert für die automatische Statistikaktualisierung in den dynamischen Schwellenwert für die automatische Statistikaktualisierung. Wichtig für die Aktualisierung von Abfrageplänen für große Tabellen, bei denen eine falsche Anzahl von Datensätzen zu fehlerhaften Ausführungsplänen führt
- 3226 – Unterdrückt Sicherungserfolgsmeldungen im Fehlerprotokoll
- 4199 – Enthält Änderungen am Abfrageoptimierer, die in CUs und SQL Server Service Packs veröffentlicht wurden
- 6532–6534 – Enthält Leistungsverbesserungen für Abfragevorgänge für räumliche Datentypen
- 8048 – Konvertiert NUMA-partitionierte Speicherobjekte in CPU-partitionierte Objekte
- 8780 – Ermöglicht zusätzliche Zeitzuweisung für die Abfrageplanung. Einige Anfragen ohne dieses Flag werden möglicherweise abgelehnt, weil sie keinen Abfrageplan haben (sehr seltener Fehler).
- 8780 – 9389 – Aktiviert zusätzlichen dynamischen Gewährungsspeicherpuffer für Batchmodusanweisungen, wodurch der Batchmodusoperator zusätzlichen Speicher anfordern und das Verschieben von Daten nach tempdb vermeiden kann, wenn zusätzlicher Speicher verfügbar ist
Auch vor 2016 ist es sinnvoll, das Trace-Flag 2301 zu aktivieren, das erweiterte Optimierungen der Entscheidungsunterstützung ermöglicht und somit bei der Auswahl korrekterer Abfragepläne hilft. Ab Version 2016 wirkt sich dies jedoch häufig negativ auf recht lange Gesamtausführungszeiten von Abfragen aus.
Außerdem empfehle ich für Systeme mit vielen Indizes (z. B. für 1C-Datenbanken), das Trace-Flag 2330 zu aktivieren, das die Erfassung der Indexnutzung deaktiviert, was sich im Allgemeinen positiv auf das System auswirkt.
Weitere Informationen zu Ablaufverfolgungsflags finden Sie unter
Aus dem obigen Link geht hervor, dass es auch wichtig ist, Versionen und Builds von MS SQL Server zu berücksichtigen, da bei neueren Versionen einige Trace-Flags standardmäßig aktiviert sind oder keine Wirkung haben.
Sie können das Trace-Flag mit den Befehlen DBCC TRACEON bzw. DBCC TRACEOFF aktivieren und deaktivieren. Weitere Einzelheiten finden Sie unter
Sie können den Status der Trace-Flags mit dem DBCC TRACESTATUS-Befehl abrufen:
Damit Ablaufverfolgungsflags in den Autostart des MS SQL Server-Dienstes einbezogen werden, müssen Sie zum SQL Server-Konfigurationsmanager gehen und diese Ablaufverfolgungsflags über -T in den Diensteigenschaften hinzufügen:
Ergebnisse
In diesem Artikel wurden einige Aspekte der Überwachung von MS SQL Server analysiert, mit deren Hilfe Sie den Mangel an RAM und freier CPU-Zeit sowie eine Reihe anderer, weniger offensichtlicher Probleme schnell erkennen können. Die am häufigsten verwendeten Ablaufverfolgungsflags wurden überprüft.
Quellen:
»
»
»
»
»
»
»
Source: habr.com