Prefaci
Molt sovint, els usuaris, desenvolupadors i administradors del SGBD de MS SQL Server es troben amb problemes de rendiment de la base de dades o del SGBD en conjunt, de manera que la supervisió de MS SQL Server és molt rellevant.
Aquest article és una addició a l'article i tractarà alguns aspectes de la supervisió de MS SQL Server, en particular: com determinar ràpidament quins recursos falten, així com recomanacions per establir marques de traça.
Perquè funcionin els scripts següents, heu de crear un esquema inf a la base de dades desitjada de la manera següent:
Creació d'un esquema inf
use <имя_БД>;
go
create schema inf;
Mètode per detectar la manca de memòria RAM
El primer indicador de la manca de memòria RAM és el cas quan una instància de MS SQL Server consumeix tota la memòria RAM assignada.
Per fer-ho, crearem la següent representació d'inf.vRAM:
S'està creant 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;
A continuació, podeu determinar que una instància de MS SQL Server consumeix tota la memòria que li assigna la consulta següent:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Si SQL_server_physical_memory_in_use_Mb és constantment superior o igual a SQL_server_committed_target_Mb, s'han de comprovar les estadístiques d'espera.
Per determinar la manca de memòria RAM mitjançant estadístiques d'espera, creem la vista inf.vWaits:
S'està creant 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 aquest cas, podeu determinar la manca de memòria RAM amb la consulta següent:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Aquí cal parar atenció als indicadors Percentatge i AvgWait_S. Si són importants en la seva totalitat, hi ha una probabilitat molt alta que no hi hagi prou RAM per a la instància de MS SQL Server. Els valors significatius es determinen individualment per a cada sistema. Tanmateix, podeu començar amb el següent: Percentage>=1 i AvgWait_S>=0.005.
Per enviar indicadors a un sistema de monitorització (per exemple, Zabbix), podeu crear les dues consultes següents:
- quants tipus d'espera ocupa la memòria RAM en percentatge (la suma de tots aquests tipus d'espera):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - quants tipus d'espera de RAM triguen en mil·lisegons (el valor màxim de tots els retards mitjans per a tots aquests tipus d'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' );
A partir de la dinàmica dels valors obtinguts per a aquests dos indicadors, podem concloure si hi ha prou RAM per a una instància de MS SQL Server.
Mètode de detecció de sobrecàrrega de la CPU
Per identificar la manca de temps del processador, n'hi ha prou amb la vista del sistema sys.dm_os_schedulers. Aquí, si runnable_tasks_count és constantment superior a 1, hi ha una gran probabilitat que el nombre de nuclis no sigui suficient per a la instància de MS SQL Server.
Per enviar un indicador a un sistema de monitorització (per exemple, Zabbix), podeu crear la consulta següent:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
A partir de la dinàmica dels valors obtinguts per a aquest indicador, podem concloure si hi ha prou temps de processador (nombre de nuclis de CPU) per a una instància de MS SQL Server.
Tanmateix, és important tenir en compte el fet que les sol·licituds poden sol·licitar diversos fils alhora. I de vegades l'optimitzador no pot estimar correctament la complexitat de la consulta en si. Aleshores, és possible que a la sol·licitud se li assignin massa fils que no es puguin processar al mateix temps en el moment donat. I això també provoca un tipus d'espera associada a la manca de temps del processador i el creixement de la cua per als programadors que utilitzen nuclis de CPU específics, és a dir, l'indicador runnable_tasks_count creixerà en aquestes condicions.
En aquest cas, abans d'augmentar el nombre de nuclis de CPU, cal configurar correctament les propietats de paral·lelisme de la pròpia instància de MS SQL Server i, a partir de la versió 2016, configurar correctament les propietats de paral·lelisme de les bases de dades requerides:
Aquí heu de parar atenció als paràmetres següents:
- Grau màxim de paral·lelisme: estableix el nombre màxim de fils que es poden assignar a cada sol·licitud (el valor per defecte és 0, limitat només pel propi sistema operatiu i l'edició de MS SQL Server)
- Llindar de cost per al paral·lelisme: cost estimat del paral·lelisme (el valor predeterminat és 5)
- DOP màxim: estableix el nombre màxim de fils que es poden assignar a cada consulta a nivell de base de dades (però no més que el valor de la propietat "Grau màxim de paral·lelisme") (el valor per defecte és 0, limitat només pel propi sistema operatiu i l'edició de MS SQL Server, així com la restricció de la propietat "Grau màxim de paral·lelisme" de tota la instància de MS SQL Server)
Aquí és impossible donar una recepta igual de bona per a tots els casos, és a dir, cal analitzar consultes pesades.
Des de la meva pròpia experiència, recomano el següent algorisme d'accions per als sistemes OLTP per configurar propietats de paral·lelisme:
- primer desactiveu el paral·lelisme establint el grau màxim de paral·lelisme a 1
- analitzeu les sol·licituds més pesades i seleccioneu el nombre òptim de fils per a elles
- establiu el grau màxim de paral·lelisme al nombre òptim seleccionat de fils obtinguts a partir del pas 2 i, per a bases de dades específiques, establiu el valor DOP màxim obtingut al pas 2 per a cada base de dades
- analitzar les sol·licituds més pesades i identificar l'efecte negatiu del multithreading. Si és així, augmenta el llindar de costos per al paral·lelisme.
Per a sistemes com 1C, Microsoft CRM i Microsoft NAV, en la majoria dels casos, és adequat prohibir el multithreading
A més, si hi ha una edició estàndard, en la majoria dels casos la prohibició del multiprocés és adequada a causa del fet que aquesta edició està limitada en el nombre de nuclis de CPU.
Per als sistemes OLAP, l'algorisme descrit anteriorment no és adequat.
Des de la meva pròpia experiència, recomano el següent algorisme d'accions per als sistemes OLAP per configurar propietats de paral·lelisme:
- analitzeu les sol·licituds més pesades i seleccioneu el nombre òptim de fils per a elles
- establiu el grau màxim de paral·lelisme al nombre òptim seleccionat de fils obtinguts a partir del pas 1 i, per a bases de dades específiques, establiu el valor DOP màxim obtingut al pas 1 per a cada base de dades
- analitzar les consultes més pesades i identificar l'efecte negatiu de limitar la concurrència. Si és així, baixeu el valor Llindar de cost per al paral·lelisme o repetiu els passos 1-2 d'aquest algorisme
És a dir, per als sistemes OLTP passem d'un fil d'un sol fil a un fil múltiple, i per als sistemes OLAP, per contra, passem de l'enllaç múltiple a un fil d'un sol fil. Així, podeu triar la configuració de paral·lelisme òptima tant per a una base de dades específica com per a tota la instància de MS SQL Server.
També és important entendre que la configuració de les propietats de paral·lelisme s'ha de canviar al llarg del temps, en funció dels resultats del seguiment del rendiment de MS SQL Server.
Directrius per establir banderes de traça
Des de la meva pròpia experiència i l'experiència dels meus companys, per obtenir un rendiment òptim, recomano establir els següents senyals de traça al nivell d'execució del servei MS SQL Server per a les versions 2008-2016:
- 610 - Reducció de registre d'insercions en taules indexades. Pot ajudar amb les insercions a taules amb molts registres i moltes transaccions, amb llargues esperes freqüents de WRITELOG per a canvis en els índexs
- 1117 - Si un fitxer d'un grup de fitxers compleix els requisits del llindar de creixement automàtic, tots els fitxers del grup de fitxers creixen
- 1118: obliga a localitzar tots els objectes en diferents extensions (prohibició d'extensions mixtes), cosa que minimitza la necessitat d'escanejar la pàgina SGAM, que s'utilitza per fer un seguiment d'extensions mixtes
- 1224: desactiva l'escalada de bloqueig en funció del nombre de bloquejos. Tanmateix, l'ús excessiu de la memòria pot provocar l'escalada de bloqueig
- 2371 - Canvia el llindar d'actualització automàtica d'estadístiques fixes al llindar d'actualització automàtica dinàmica d'estadístiques. Important per actualitzar els plans de consulta per a taules grans, on un recompte incorrecte de registres provoca plans d'execució erronis.
- 3226 - Suprimeix els missatges d'èxit de còpia de seguretat al registre d'errors
- 4199: inclou canvis a l'optimitzador de consultes publicats en CU i Service Packs de SQL Server
- 6532-6534: inclou millores de rendiment per a operacions de consulta sobre tipus de dades espacials
- 8048 - Converteix objectes de memòria particionada NUMA en objectes de CPU particionats
- 8780: activa l'assignació de temps addicional per a la planificació de consultes. Algunes sol·licituds sense aquesta marca poden ser rebutjades perquè no tenen un pla de consultes (error molt rar)
- 8780 - 9389 - Habilita un buffer de memòria d'atorgament dinàmic addicional per a les declaracions del mode per lots, que permet a l'operador del mode per lots sol·licitar memòria addicional i evitar moure dades a tempdb si hi ha memòria addicional disponible
També abans del 2016, és útil habilitar el senyalador de traça 2301, que permet optimitzacions de suport de decisions millorades i, per tant, ajuda a triar plans de consulta més correctes. Tanmateix, a partir de la versió 2016, sovint té un efecte negatiu en temps d'execució de consultes generals força llargs.
A més, per a sistemes amb molts índexs (per exemple, per a bases de dades 1C), recomano habilitar el senyalador de traça 2330, que desactiva la recopilació de l'ús de l'índex, que generalment té un efecte positiu en el sistema.
Per obtenir més informació sobre les marques de traça, vegeu
Des de l'enllaç anterior, també és important tenir en compte les versions i compilacions de MS SQL Server, ja que per a les versions més noves, alguns senyaladors de traça estan habilitats per defecte o no tenen cap efecte.
Podeu activar i desactivar la marca de traça amb les ordres DBCC TRACEON i DBCC TRACEOFF, respectivament. Per a més detalls vegeu
Podeu obtenir l'estat de les marques de traça mitjançant l'ordre DBCC TRACESTATUS:
Perquè les marques de traça s'incloguin a l'inici automàtic del servei MS SQL Server, heu d'anar al Gestor de configuració de SQL Server i afegir aquestes marques de traça mitjançant -T a les propietats del servei:
Resultats de
En aquest article, s'han analitzat alguns aspectes de la supervisió de MS SQL Server, amb l'ajuda dels quals podeu identificar ràpidament la manca de RAM i temps lliure de CPU, així com una sèrie d'altres problemes menys evidents. S'han revisat els indicadors de traça més utilitzats.
Fonts:
»
»
»
»
»
»
»
Font: www.habr.com