ProHoster > Blog > Adminisztráció > Az MS SQL Server megfigyelésének néhány szempontja. Útmutató a nyomkövetési zászlók beállításához

Az MS SQL Server megfigyelésének néhány szempontja. Útmutató a nyomkövetési zászlók beállításához

Előszó

Az MS SQL Server DBMS felhasználói, fejlesztői és adminisztrátorai gyakran ütköznek az adatbázis vagy az DBMS egészének teljesítményproblémákkal, ezért az MS SQL Server figyelése nagyon fontos.
Ez a cikk a cikk kiegészítése A Zabbix használata az MS SQL Server adatbázis figyelésére és kitér az MS SQL Server figyelésének néhány aspektusára, különösen: hogyan lehet gyorsan meghatározni, hogy mely erőforrások hiányoznak, valamint a nyomkövetési jelzők beállítására vonatkozó ajánlásokat.
A következő szkriptek működéséhez létre kell hoznia egy inf sémát a kívánt adatbázisban az alábbiak szerint:
Inf séma létrehozása

use <имя_БД>;
go
create schema inf;

Módszer a RAM hiányának észlelésére

A RAM hiányának első mutatója az az eset, amikor az MS SQL Server egy példánya felemészti a számára lefoglalt összes RAM-ot.
Ehhez az inf.vRAM következő reprezentációját hozzuk létre:
Az inf.vRAM nézet létrehozása

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;

Ezután a következő lekérdezéssel megállapíthatja, hogy az MS SQL Server egy példánya elfoglalja-e a számára lefoglalt összes memóriát:

select  SQL_server_physical_memory_in_use_Mb,  SQL_server_committed_target_Mb
from [inf].[vRAM];

Ha az SQL_server_physical_memory_in_use_Mb folyamatosan nagyobb vagy egyenlő, mint az SQL_server_committed_target_Mb, akkor ellenőrizni kell a várakozási statisztikákat.
A RAM hiányának a várakozási statisztikákon keresztül történő meghatározásához hozzuk létre az inf.vWaits nézetet:
Az inf.vWaits nézet létrehozása

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];

Ebben az esetben a RAM hiányát a következő lekérdezéssel állapíthatja meg:

SELECT [Percentage]
      ,[AvgWait_S]
  FROM [inf].[vWaits]
  where [WaitType] in (
    'PAGEIOLATCH_XX',
    'RESOURCE_SEMAPHORE',
    'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Itt figyelni kell a Percentage és AvgWait_S mutatókra. Ha összességükben jelentősek, akkor nagyon nagy a valószínűsége annak, hogy nincs elég RAM az MS SQL Server példányhoz. A jelentős értékeket rendszerenként külön-külön határozzák meg. Kezdheti azonban a következővel: Percentage>=1 és AvgWait_S>=0.005.
Az indikátorok felügyeleti rendszerbe (például Zabbix) való kimenetéhez a következő két lekérdezést hozhatja létre:

  1. hány típusú várakozást foglal el a RAM százalékban (az összes ilyen típusú várakozás összege): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. hány RAM várakozási típusa tart ezredmásodpercben (az összes átlagos késés maximális értéke az összes ilyen várakozási típusnál): 
    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'
  );

E két mutató kapott értékeinek dinamikája alapján megállapíthatjuk, hogy van-e elegendő RAM az MS SQL Server egy példányához.

CPU túlterhelés-észlelési módszer

A processzoridő hiányának azonosításához elegendő a sys.dm_os_schedulers rendszernézetet használni. Itt, ha a runnable_tasks_count állandóan nagyobb, mint 1, akkor nagy a valószínűsége annak, hogy a magok száma nem elegendő az MS SQL Server példányhoz.
Ha egy jelzőt szeretne kiadni egy megfigyelő rendszernek (például Zabbix), létrehozhatja a következő lekérdezést:

select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;

Ennek a mutatónak a kapott értékeinek dinamikája alapján megállapíthatjuk, hogy elegendő processzoridő (a CPU magok száma) van-e az MS SQL Server egy példányához.
Fontos azonban szem előtt tartani azt a tényt, hogy maguk a kérések egyszerre több szálat is kérhetnek. És néha az optimalizáló nem tudja helyesen megbecsülni magának a lekérdezésnek a bonyolultságát. Ekkor előfordulhat, hogy a kéréshez túl sok szál van lefoglalva, amelyet nem lehet egyszerre feldolgozni. Ez pedig egyfajta várakozást is okoz, amely a processzoridő hiányával jár, és a sor növekedését okozza a meghatározott CPU magokat használó ütemezők számára, vagyis a runnable_tasks_count jelző ilyen körülmények között nő.
Ebben az esetben a CPU-magok számának növelése előtt helyesen kell konfigurálnia magának az MS SQL Server-példánynak a párhuzamossági tulajdonságait, és a 2016-os verziótól kezdve megfelelően konfigurálnia kell a szükséges adatbázisok párhuzamossági tulajdonságait:
Az MS SQL Server megfigyelésének néhány szempontja. Útmutató a nyomkövetési zászlók beállításához

Az MS SQL Server megfigyelésének néhány szempontja. Útmutató a nyomkövetési zászlók beállításához
Itt a következő paraméterekre kell figyelni:

  1. Max Degree of Parallelism - beállítja az egyes kérésekhez hozzárendelhető szálak maximális számát (az alapértelmezés 0 - csak maga az operációs rendszer és az MS SQL Server kiadása korlátozza)
  2. Költségküszöb a párhuzamossághoz – a párhuzamosság becsült költsége (alapértelmezett 5)
  3. Max DOP - beállítja az egyes lekérdezésekhez adatbázis szinten hozzárendelhető szálak maximális számát (de legfeljebb a "Maximális párhuzamossági fok" tulajdonság értéke) (alapértelmezett 0 - csak maga az operációs rendszer korlátozza és az MS SQL Server kiadása, valamint az MS SQL Server teljes példányára vonatkozó "Max Degree of Parallelism" tulajdonság korlátozása)

Itt lehetetlen minden esetre egyformán jó receptet adni, azaz nehéz lekérdezéseket kell elemezni.
Saját tapasztalataim alapján a következő műveleti algoritmust ajánlom OLTP-rendszerekhez a párhuzamossági tulajdonságok beállításához:

  1. először tiltsa le a párhuzamosságot azáltal, hogy a példányszintű Maximális párhuzamossági fokot 1-re állítja
  2. elemezze a legsúlyosabb kéréseket, és válassza ki a szálak optimális számát
  3. állítsa be a párhuzamosság maximális fokát a 2. lépésből kapott kiválasztott optimális számú szálra, és bizonyos adatbázisok esetén állítsa be a 2. lépésből kapott Max DOP értéket minden adatbázishoz.
  4. elemezze a legsúlyosabb kéréseket, és azonosítsa a többszálú kezelés negatív hatását. Ha igen, akkor növelje meg a párhuzamosság költségküszöbét.
    Az olyan rendszerek esetében, mint az 1C, Microsoft CRM és Microsoft NAV, a legtöbb esetben megfelelő a többszálú használat tiltása.

Továbbá, ha van Standard kiadás, akkor a legtöbb esetben a többszálú feldolgozás tilalma megfelelő, mivel ez a kiadás korlátozott a CPU magok számára.
OLAP rendszerek esetén a fent leírt algoritmus nem megfelelő.
Saját tapasztalatom alapján a következő műveleti algoritmust ajánlom az OLAP rendszerek számára a párhuzamossági tulajdonságok beállításához:

  1. elemezze a legsúlyosabb kéréseket, és válassza ki a szálak optimális számát
  2. állítsa be a párhuzamosság maximális fokát a 1. lépésből kapott kiválasztott optimális számú szálra, és bizonyos adatbázisok esetén állítsa be a 1. lépésből kapott Max DOP értéket minden adatbázishoz.
  3. elemezze a legsúlyosabb lekérdezéseket, és azonosítsa az egyidejűség korlátozásának negatív hatását. Ha igen, akkor vagy csökkentse a párhuzamosság költségküszöbét, vagy ismételje meg az algoritmus 1-2.

Vagyis az OLTP rendszerek esetében az egyszálúról a többszálasra, az OLAP-rendszereknél pedig éppen ellenkezőleg, a többszálasról az egyszálasra. Így kiválaszthatja az optimális párhuzamossági beállításokat mind egy adott adatbázishoz, mind az MS SQL Server teljes példányához.
Azt is fontos megérteni, hogy a párhuzamossági tulajdonságok beállításait idővel módosítani kell, az MS SQL Server teljesítményének figyelésének eredményei alapján.

Útmutató a nyomkövetési zászlók beállításához

Saját és kollégáim tapasztalatai alapján az optimális teljesítmény érdekében javaslom a következő nyomkövetési jelzők beállítását az MS SQL Server szolgáltatás futási szintjén a 2008-2016-os verziókhoz:

  1. 610 - Csökkentett beszúrások naplózása indexelt táblákba. Segíthet a sok rekordot és sok tranzakciót tartalmazó táblákba történő beszúrásban, a WRITELOG gyakori, hosszú, indexek változására vár.
  2. 1117 - Ha egy fájlcsoportban lévő fájl megfelel az automatikus növekedési küszöb követelményeinek, a fájlcsoportban lévő összes fájl nő
  3. 1118 - Kényszeríti, hogy minden objektumot különböző mértékben helyezzen el (vegyes kiterjedés tilalma), ami minimálisra csökkenti a vegyes kiterjedések nyomon követésére használt SGAM oldal átvizsgálásának szükségességét
  4. 1224 – Letiltja a zárolások számának megfelelő kiterjesztését. A túlzott memóriahasználat azonban a zárolás eszkalációját válthatja ki
  5. 2371 – A rögzített automatikus statisztikai frissítési küszöbértéket dinamikus automatikus statisztikai frissítési küszöbértékre módosítja. Fontos a nagy táblák lekérdezési terveinek frissítéséhez, ahol a rekordok helytelen száma hibás végrehajtási terveket eredményez
  6. 3226 – Letiltja a biztonsági mentés sikerüzeneteit a hibanaplóban
  7. 4199 – Tartalmazza a lekérdezésoptimalizáló CU-kban és SQL Server Service Pack csomagokban kiadott módosításait
  8. 6532-6534 – A téradattípusokon végzett lekérdezési műveletek teljesítményének fejlesztéseit tartalmazza
  9. 8048 - NUMA particionált memóriaobjektumot konvertál CPU particionált objektumokká
  10. 8780 - Lehetővé teszi további időkiosztást a lekérdezés tervezéséhez. Előfordulhat, hogy egyes, ez a jelző nélküli kérések elutasításra kerülnek, mert nincs lekérdezési tervük (nagyon ritka hiba)
  11. 8780 - 9389 - További dinamikus engedélyezési memóriapuffert tesz lehetővé kötegelt módú utasításokhoz, amely lehetővé teszi a kötegelt mód operátorának, hogy több memóriát kérjen, és elkerülje az adatok áthelyezését a tempdb-be, ha több memória áll rendelkezésre

Szintén 2016 előtt hasznos a 2301 nyomkövetési jelző engedélyezése, amely lehetővé teszi a továbbfejlesztett döntéstámogatási optimalizálást, és így segít a helyesebb lekérdezési tervek kiválasztásában. A 2016-os verziótól kezdve azonban ez gyakran negatív hatással van a meglehetősen hosszú általános lekérdezés-végrehajtási időkre.
Ezenkívül a sok indexet tartalmazó rendszereknél (például 1C adatbázisoknál) javaslom a 2330 nyomkövetési jelző engedélyezését, amely letiltja az indexhasználat gyűjtését, ami általában pozitív hatással van a rendszerre.
A nyomkövetési zászlókkal kapcsolatos további információkért lásd: itt
A fenti hivatkozás alapján fontos figyelembe venni az MS SQL Server verzióit és buildjeit is, mivel az újabb verzióknál egyes nyomkövetési jelzők alapértelmezés szerint engedélyezve vannak, vagy nincs hatása.
A nyomkövetési jelzőt a DBCC TRACEON és a DBCC TRACEOFF parancsokkal kapcsolhatja be és ki. További részletekért lásd itt
A nyomkövetési jelzők állapotát a DBCC TRACESTATUS paranccsal kaphatja meg: több
Ahhoz, hogy a nyomkövetési jelzők szerepeljenek az MS SQL Server szolgáltatás automatikus indításában, fel kell lépnie az SQL Server Configuration Manager-re, és hozzá kell adnia ezeket a nyomkövetési jelzőket a -T segítségével a szolgáltatás tulajdonságaiban:
Az MS SQL Server megfigyelésének néhány szempontja. Útmutató a nyomkövetési zászlók beállításához

Eredményei

Ebben a cikkben az MS SQL Server monitorozásának néhány aspektusát elemeztük, amelyek segítségével gyorsan azonosítható a RAM és a szabad CPU idő hiánya, valamint számos más kevésbé nyilvánvaló probléma. A leggyakrabban használt nyomkövetési jelzők áttekintésre kerültek.

Forrás:

» SQL Server várakozási statisztika
» Az SQL Server várakozási statisztikája, vagy kérem, mondja meg, hol fáj
» Rendszernézet sys.dm_os_schedulers
» A Zabbix használata az MS SQL Server adatbázis figyelésére
» SQL életmód
» Trace Flags
» sql.ru

Forrás: will.com

Hozzászólás