Prélude
Piuttostu spessu, l'utilizatori, i sviluppatori è l'amministratori di MS SQL Server DBMS sò affruntati cù prublemi di rendiment di a basa di dati o di u DBMS in generale, cusì u monitoraghju MS SQL Server hè assai pertinente.
Questu articulu hè un aghjuntu à l'articulu è esaminà certi aspetti di u monitoraghju MS SQL Server, in particulare: cumu per determinà rapidamente quale risorse mancanu, è ancu cunsiglii per a creazione di bandieri di traccia.
Per i seguenti scripts per travaglià, avete bisognu di creà un schema inf in a basa di dati desiderata cusì:
Creazione di schema inf
use <имя_БД>;
go
create schema inf;
Metudu per detectà a mancanza di RAM
U primu indicatore di una mancanza di RAM hè quandu una istanza di MS SQL Server manghja tutta a RAM assignata.
Per fà questu, crea a seguente rapprisentazione inf.vRAM:
Crià 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;
Allora pudete stabilisce chì una istanza di MS SQL Server cunsuma tutta a memoria attribuita cù a seguente dumanda:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Se l'indicatore SQL_server_physical_memory_in_use_Mb hè constantemente micca menu di SQL_server_committed_target_Mb, allora avete bisognu di verificà e statistiche d'attesa.
Per determinà a mancanza di RAM attraversu statistiche d'attesa, creamu una vista inf.vWaits:
Crià 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];
In questu casu, pudete determinà a mancanza di RAM usendu a seguente dumanda:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Quì avete bisognu di attentu à l'indicatori Percentage è AvgWait_S. Se sò significati in a so tutalità, allora ci hè una probabilità assai alta chì l'istanza MS SQL Server ùn hà micca abbastanza RAM. I valori essenziali sò determinati individualmente per ogni sistema. Tuttavia, pudete principià cù l'indicatore seguente: Percentage>=1 è AvgWait_S>=0.005.
Per pruduce l'indicatori à un sistema di monitoraghju (per esempiu, Zabbix), pudete creà e seguenti duie dumande:
- Chì ghjè u percentualità di i tipi di attesa per a RAM (summa per tutti questi tipi di attesa):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - quanti tipi di attesa di RAM piglianu in millisecondi (u valore massimu di tutti i ritardi medii per tutti questi tipi di attesa):
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' );
Basatu nantu à a dinamica di i valori ottenuti per questi dui indicatori, pudemu cuncludi s'ellu ci hè abbastanza RAM per l'istanza MS SQL Server.
Metudu per detectà a carica eccessiva di CPU
Per identificà una mancanza di tempu CPU, basta aduprà a vista di u sistema sys.dm_os_schedulers. Quì, se l'indicatore runnable_tasks_count hè sempre più grande di 1, allora ci hè una probabilità alta chì u numeru di core ùn hè micca abbastanza per l'istanza MS SQL Server.
Per vede un indicatore in un sistema di monitoraghju (per esempiu, Zabbix), pudete creà a seguente dumanda:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Basatu nantu à a dinamica di i valori ottenuti per questu indicatore, pudemu cuncludi s'ellu ci hè abbastanza tempu di processore (numeru di core CPU) per l'istanza MS SQL Server.
Tuttavia, hè impurtante ricurdà u fattu chì e dumande stessu ponu interrogà parechji fili à una volta. È qualchì volta l'ottimisatore ùn pò micca valutà currettamente a cumplessità di a dumanda stessu. Allora a dumanda pò esse attribuita troppu fili, chì à un certu tempu ùn ponu esse processatu simultaneamente. È questu causa ancu un tipu d'attesa assuciatu cù una mancanza di tempu di processore, è a crescita di a fila per i pianificatori chì utilizanu core CPU specifichi, vale à dì, l'indicatore runnable_tasks_count aumenterà in tali cundizioni.
In questu casu, prima di aumentà u nùmeru di core di CPU, avete bisognu di cunfigurà currettamente e proprietà di parallelismu di l'istanza MS SQL Server stessu, è da a versione 2016, cunfigurà currettamente e proprietà di parallelismu di e basa di dati necessariu:
Quì si deve esse attentu à i seguenti paràmetri:
- Max Degree of Parallelism - stabilisce u numeru massimu di fili chì ponu esse attribuiti à ogni dumanda (u predeterminatu hè 0 - limitatu solu da u sistema operatore stessu è l'edizione di MS SQL Server)
- Cost Threshold for Parallelism - costu stimatu di parallelismu (predeterminatu hè 5)
- Max DOP - stabilisce u numeru massimu di fili chì ponu esse attribuiti à ogni dumanda à u livellu di basa di dati (ma micca più di u valore di a pruprietà "Max Degree of Parallelism") (per difettu hè 0-limitatu solu da u sistema operatore). stessu è l'edizione di MS SQL Server, è ancu una limitazione nantu à a pruprietà "Max Degree of Parallelism" di tutta l'istanza MS SQL Server)
Hè impussibile di dà una ricetta ugualmente bona per tutti i casi, vale à dì, avete bisognu di analizà e dumande difficili.
Basatu nantu à a mo propria sperienza, ricumandemu l'algoritmu di l'azzioni seguenti per i sistemi OLTP per cunfigurà proprietà di parallelismu:
- prima disattivate u parallelismu mettendu Max Degree of Parallelism à 1 à u livellu di l'istanza sana
- analizà e dumande più pesanti è selezziunate u numeru ottimali di filamenti per elli
- stabilisce u Max Degree of Parallelism à u numeru ottimale selezziunatu di fili ottenuti da u passu 2, è ancu per basa di dati specifiche stabilisce u valore Max DOP ottenutu da u passu 2 per ogni basa di dati.
- analizà e dumande più pesanti è identificà l'effettu negativu di multithreading. S'ellu hè, allora aumenta u Sogliu di Costu per Parallelismu.
Per sistemi cum'è 1C, Microsoft CRM è Microsoft NAV, in a maiò parte di i casi, pruibisce u multithreading hè adattatu
Inoltre, sè vo avete l'edizione Standard, allora in a maiò parte di i casi una prohibizione di multi-threading hè appruvata per u fattu chì sta edizione hè limitata in u numeru di core CPU.
L'algoritmu descrittu sopra ùn hè micca adattatu per i sistemi OLAP.
Basatu nantu à a mo propria sperienza, ricumandemu l'algoritmu di l'azzioni seguenti per i sistemi OLAP per cunfigurà proprietà di parallelismu:
- analizà e dumande più pesanti è selezziunate u numeru ottimali di filamenti per elli
- stabilisce u Max Degree of Parallelism à u numeru ottimale selezziunatu di fili ottenuti da u passu 1, è ancu per basa di dati specifiche stabilisce u valore Max DOP ottenutu da u passu 1 per ogni basa di dati.
- analizà e dumande più pesanti è identificà l'effettu negativu di a limitazione di a cuncurrenza. S'ellu hè, allora o diminuite u Sogliu di u Costu per u valore di Parallelismu, o ripetite i passi 1-2 di questu algoritmu.
Vale à dì, per i sistemi OLTP andemu da un filu unicu à un filu multifunzione, è per i sistemi OLAP, à u cuntrariu, andemu da un filu unicu à un filu unicu. Questu modu pudete selezziunà i paràmetri di parallelismu ottimali per una basa di dati specifica è per tutta l'istanza MS SQL Server.
Hè impurtante ancu di capisce chì i paràmetri di pruprietà di cuncurrenza deve esse cambiatu cù u tempu, basatu nantu à i risultati di u monitoraghju di u funziunamentu di MS SQL Server.
Raccomandazioni per stabilisce bandiere di traccia
Da a mo propria sperienza è l'esperienza di i mo culleghi, per un rendimentu ottimale, ricumandemu di stabilisce e seguenti bandiere di traccia à u livellu di esecuzione di u serviziu MS SQL Server per e versioni 2008-2016:
- 610 - Reduce u logu di l'inserzioni in tabelle indexate. Puderà aiutà cù inserisce in tavule cù un gran numaru di registri è parechje transazzione, cù WRITELOG frequenti longu aspetta per i cambiamenti in l'indici
- 1117 - Se un schedariu in un gruppu di file incontra u sogliu di crescita automatica, tutti i schedari in u gruppu di file sò cultivati
- 1118 - Forza tutti l'uggetti per esse situati in diverse estensioni (disallows extents mixed), chì minimizza a necessità di scansà a pagina SGAM, chì hè utilizata per seguità estensioni miste.
- 1224 - Disabilita l'escalation di serratura basatu annantu à u conte di serratura. Tuttavia, l'usu eccessivu di memoria pò attivà l'escalation di serratura
- 2371 - Cambia u sogliu fissu di l'aghjurnamentu automaticu di e statistiche in u sogliu di l'aghjurnamentu automaticu dinamicu di e statistiche. Impurtante per l'aghjurnà i piani di dumanda nantu à tavule grandi induve a definizione incorrecta di u numeru di registri si traduce in piani di esecuzione erronei
- 3226 - Supprime i missaghji di successu di salvezza in u logu di errore
- 4199 - Include cambiamenti à l'ottimisatore di query liberatu in rollups di aghjurnamentu di SQL Server è pacchetti di serviziu
- 6532-6534 - Include miglioramenti di rendiment per e dumande cù tipi di dati spaziali
- 8048 - Converte oggetti di memoria partizionati da NUMA in oggetti partizionati da CPU
- 8780 - Permette l'allocazione di tempu supplementu per a pianificazione di e dumande. Alcune dumande senza sta bandiera ponu esse rifiutate perchè ùn anu micca un pianu di dumanda (un errore assai raru)
- 8780 - 9389 - Permette un buffer di memoria temporale dinamica addiziale per l'operatori in modalità batch, chì permette à l'operatore in modalità batch di dumandà memoria supplementu è evite di trasferisce dati à tempdb se una memoria addiziale hè dispunibule
Inoltre, prima di a versione 2016, hè utile per attivà a bandiera di traccia 2301, chì permette l'ottimisazione avanzata di supportu di decisione è cusì aiuta à sceglie megliu piani di ricerca. Tuttavia, da a versione 2016, spessu hà un effettu negativu nantu à i tempi di esecuzione di e dumande in generale abbastanza longu.
Inoltre, per i sistemi cù assai indici (per esempiu, per basa di dati 1C), ricumandemu di attivà a bandiera di traccia 2330, chì disattiva a cullizzioni di l'usu di l'indici, chì generalmente hà un effettu pusitivu nantu à u sistema.
Pudete amparà di più nantu à e bandiere traccia
Da u ligame sopra, hè ancu impurtante di cunsiderà e versioni è e custruzzioni di MS SQL Server, cum'è per e versioni più recenti, certi bandieri di traccia sò attivati per difettu o ùn anu micca effettu.
Pudete attivà o disattivà a bandiera di traccia cù i cumandamenti DBCC TRACEON è DBCC TRACEOFF, rispettivamente. Vede più dettagli
Pudete ottene u statutu di i bandieri di traccia cù u cumandimu DBCC TRACESTATUS:
Per chì i bandieri di traccia per esse inclusi in l'autostart di u serviziu MS SQL Server, avete bisognu à andà à u Manager di Configurazione di SQL Server è aghjunghje sti bandieri di traccia via -T in e proprietà di serviziu:
Risultati
Questu articulu hà esaminatu certi aspetti di u monitoraghju MS SQL Server, cù l'aiutu di quale pudete identificà rapidamente una mancanza di RAM è u tempu di CPU liberu, è ancu una quantità di altri prublemi menu evidenti. I bandieri di traccia più cumunimenti usati sò stati riveduti.
Sources:
»
»
»
»
»
»
»
Source: www.habr.com