ProHoster > Blog > Stjórnsýsla > Sumir þættir við eftirlit með MS SQL Server. Ráðleggingar um uppsetningu sporfána

Sumir þættir við eftirlit með MS SQL Server. Ráðleggingar um uppsetningu sporfána

Formáli

Notendur, forritarar og stjórnendur MS SQL Server DBMS standa oft frammi fyrir frammistöðuvandamálum gagnagrunnsins eða DBMS í heild sinni, svo eftirlit með MS SQL Server er mjög viðeigandi.
Þessi grein er viðbót við greinina Notkun Zabbix til að fylgjast með MS SQL Server Database og það mun skoða nokkra þætti í vöktun MS SQL Server, sérstaklega: hvernig á að ákvarða fljótt hvaða auðlindir vantar, sem og ráðleggingar um uppsetningu rekja fána.
Til að eftirfarandi forskriftir virki þarftu að búa til inf skema í viðkomandi gagnagrunni sem hér segir:
Að búa til inf skema

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

Aðferð til að greina skort á vinnsluminni

Fyrsti vísbendingin um skort á vinnsluminni er þegar tilvik af MS SQL Server étur upp allt vinnsluminni sem honum er úthlutað.
Til að gera þetta skaltu búa til eftirfarandi framsetningu inf.vRAM:
Að búa til inf.vRAM útsýni

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;

Þá geturðu ákvarðað að tilvik af MS SQL Server eyði öllu minni sem honum er úthlutað með því að nota eftirfarandi fyrirspurn:

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

Ef SQL_server_physical_memory_in_use_Mb vísirinn er stöðugt ekki minni en SQL_server_committed_target_Mb, þá þarftu að athuga biðtölfræðina.
Til að ákvarða skort á vinnsluminni í gegnum biðtölfræði, skulum við búa til útsýni inf.vWaits:
Að búa til inf.vWaits útsýnið

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

Í þessu tilviki geturðu ákvarðað skort á vinnsluminni með því að nota eftirfarandi fyrirspurn:

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

Hér þarftu að huga að hlutfallsvísunum og AvgWait_S vísunum. Ef þau eru mikilvæg í heild sinni, þá eru mjög miklar líkur á því að MS SQL Server tilvikið hafi ekki nóg vinnsluminni. Nauðsynleg gildi eru ákvörðuð fyrir sig fyrir hvert kerfi. Hins vegar geturðu byrjað á eftirfarandi vísi: Prósenta>=1 og AvgWait_S>=0.005.
Til að gefa út vísbendingar í eftirlitskerfi (til dæmis Zabbix) geturðu búið til eftirfarandi tvær fyrirspurnir:

  1. Hvert er hlutfall biðtegunda fyrir vinnsluminni (summa fyrir allar slíkar biðgerðir): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. hversu margar biðgerðir af vinnsluminni taka á millisekúndum (hámarksgildi allra meðaltafa fyrir allar slíkar biðgerðir): 
    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'
  );

Byggt á gangverki gildanna sem fæst fyrir þessa tvo vísbendingar getum við ályktað hvort það sé nóg vinnsluminni fyrir MS SQL Server tilvikið.

Aðferð til að greina of mikið CPU álag

Til að bera kennsl á skort á CPU tíma, notaðu bara sys.dm_os_schedulers kerfisskjáinn. Hér, ef runnable_tasks_count vísirinn er stöðugt stærri en 1, þá eru miklar líkur á að fjöldi kjarna sé ekki nóg fyrir MS SQL Server tilvikið.
Til að birta vísir í eftirlitskerfi (til dæmis Zabbix) geturðu búið til eftirfarandi beiðni:

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

Byggt á gangverki gildanna sem fengust fyrir þennan vísi, getum við komist að þeirri niðurstöðu hvort nægur örgjörvatími (fjöldi CPU kjarna) sé fyrir MS SQL Server tilvikið.
Hins vegar er mikilvægt að muna þá staðreynd að fyrirspurnirnar sjálfar geta spurt marga þræði í einu. Og stundum getur fínstillingin ekki rétt metið hversu flókin fyrirspurnin sjálf er. Þá gæti beiðninni verið úthlutað of mörgum þráðum sem ekki er hægt að afgreiða samtímis á tilteknum tíma. Og þetta veldur líka tegund bið sem tengist skorti á örgjörvatíma og vöxtur biðröðarinnar fyrir tímaáætlunarmenn sem nota sérstaka CPU kjarna, það er runnable_tasks_count vísirinn mun aukast við slíkar aðstæður.
Í þessu tilviki, áður en fjöldi örgjörvakjarna fjölgar, þarftu að stilla samhliða eiginleika sjálfs MS SQL Server tilviksins rétt, og frá útgáfu 2016, stilla rétt samhliða eiginleika nauðsynlegra gagnagrunna:
Sumir þættir við eftirlit með MS SQL Server. Ráðleggingar um uppsetningu sporfána

Sumir þættir við eftirlit með MS SQL Server. Ráðleggingar um uppsetningu sporfána
Hér ættir þú að borga eftirtekt til eftirfarandi breytur:

  1. Max Degree of Parallelism - stillir hámarksfjölda þráða sem hægt er að úthluta til hverrar beiðni (sjálfgefið er 0 - takmarkað aðeins af stýrikerfinu sjálfu og útgáfu MS SQL Server)
  2. Kostnaðarþröskuldur fyrir samsvörun - áætlaður kostnaður við samsvörun (sjálfgefið er 5)
  3. Hámarks DOP - stillir hámarksfjölda þráða sem hægt er að úthluta til hverrar fyrirspurnar á gagnagrunnsstigi (en ekki meira en gildið á "Max Degree of Parallelism" eiginleikanum) (sjálfgefið er það 0 - takmarkast aðeins af stýrikerfinu sjálfu og útgáfu MS SQL Server, sem og takmörkun á „Max Degree of Parallelism“ eign alls MS SQL Server tilviksins)

Það er ómögulegt að gefa jafn góða uppskrift fyrir öll mál, það er að segja að þú þarft að greina erfiðar fyrirspurnir.
Byggt á eigin reynslu mæli ég með eftirfarandi reiknirit aðgerða fyrir OLTP kerfi til að stilla eiginleika samhliða:

  1. slökktu fyrst á samsvörun með því að stilla hámarksgráðu samhliða á 1 á stigi alls tilviksins
  2. greina þyngstu fyrirspurnirnar og velja besta fjölda þráða fyrir þær
  3. stilltu Max Degree of Parallelism á valinn ákjósanlegan fjölda þráða sem fæst úr skrefi 2, og einnig fyrir sérstaka gagnagrunna skaltu stilla hámarks DOP gildið sem fæst úr skrefi 2 fyrir hvern gagnagrunn
  4. greina þyngstu fyrirspurnirnar og greina neikvæð áhrif fjölþráða. Ef það er, þá hækkið kostnaðarþröskuld fyrir samsvörun.
    Fyrir kerfi eins og 1C, Microsoft CRM og Microsoft NAV hentar í flestum tilfellum að banna fjölþráður

Einnig, ef þú ert með Standard útgáfuna, þá er í flestum tilfellum bann við fjölþráðum viðeigandi vegna þess að þessi útgáfa er takmörkuð í fjölda CPU kjarna.
Reikniritið sem lýst er hér að ofan hentar ekki fyrir OLAP kerfi.
Byggt á eigin reynslu mæli ég með eftirfarandi reiknirit aðgerða fyrir OLAP kerfi til að stilla eiginleika samhliða:

  1. greina þyngstu fyrirspurnirnar og velja besta fjölda þráða fyrir þær
  2. stilltu Max Degree of Parallelism á valinn ákjósanlegan fjölda þráða sem fæst úr skrefi 1, og einnig fyrir sérstaka gagnagrunna skaltu stilla hámarks DOP gildið sem fæst úr skrefi 1 fyrir hvern gagnagrunn
  3. greina þyngstu fyrirspurnirnar og greina neikvæð áhrif þess að takmarka samhliða. Ef svo er, þá annað hvort lækkaðu kostnaðarþröskuldinn fyrir samhliða gildi eða endurtaktu skref 1-2 í þessu reikniriti

Það er að segja að fyrir OLTP kerfi förum við frá einum þræði yfir í fjölþráð og fyrir OLAP kerfi, þvert á móti, förum við úr fjölþráðum yfir í einn þráð. Þannig geturðu valið bestu samhliða stillingar bæði fyrir tiltekinn gagnagrunn og fyrir allt MS SQL Server tilvikið.
Það er líka mikilvægt að skilja að stillingum samhliða eiginleika þarf að breyta með tímanum, byggt á niðurstöðum eftirlits með frammistöðu MS SQL Server.

Ráðleggingar um uppsetningu sporfána

Af eigin reynslu og reynslu samstarfsmanna minna, til að ná sem bestum árangri, mæli ég með því að setja eftirfarandi rakningarflög á MS SQL Server þjónustu keyrslustigi fyrir 2008-2016 útgáfur:

  1. 610 - Draga úr skráningu innskots í verðtryggðar töflur. Getur hjálpað til við innskot í töflur með miklum fjölda færslur og margar færslur, með tíðum langri WRITELOG bið eftir breytingum á vísitölum
  2. 1117 - Ef skrá í skráahópi uppfyllir þröskuld sjálfvirkrar vaxtar, eru allar skrár í skráahópnum stækkaðar
  3. 1118 - Þvingar alla hluti til að vera staðsettir í mismunandi umfangi (lokar blönduð umfang), sem lágmarkar þörfina á að skanna SGAM síðuna, sem er notuð til að fylgjast með blönduðu umfangi
  4. 1224 - Slökkva á stigmögnun læsa byggt á fjölda læsinga. Hins vegar getur of mikil minnisnotkun gert kleift að auka læsingu
  5. 2371 - Breytir föstum sjálfvirkum tölfræðiuppfærsluþröskuldi í kraftmikla sjálfvirka tölfræðiuppfærsluþröskuld. Mikilvægt til að uppfæra fyrirspurnaáætlanir á stórum töflum þar sem rangt skilgreint fjölda skráa leiðir til rangra framkvæmdaáætlana
  6. 3226 - Bælir árangursskilaboð afritunar í villuskránni
  7. 4199 - Inniheldur breytingar á fyrirspurnarfínstillingunni sem gefinn er út í SQL Server uppfærslur og þjónustupakka
  8. 6532-6534 - Inniheldur árangursbætur fyrir fyrirspurnir með landgagnategundum
  9. 8048 - Breytir NUMA skipta minnishlutum í örgjörva skipta
  10. 8780 - Gerir viðbótartímaúthlutun fyrir fyrirspurnarskipulagningu. Sumum beiðnum án þessa flaggs gæti verið hafnað vegna þess að þær eru ekki með fyrirspurnaráætlun (mjög sjaldgæf villa)
  11. 8780 - 9389 - Gerir aukið kraftmikið tímabundið minni biðminni fyrir lotuham rekstraraðila, sem gerir lotuham rekstraraðila kleift að biðja um viðbótarminni og forðast að flytja gögn til tempdb ef viðbótarminni er tiltækt

Það er einnig gagnlegt að virkja rakningarfánann 2016 fyrir útgáfu 2301, sem gerir háþróaða hagræðingu ákvarðanastuðnings kleift og hjálpar þannig við að velja betri fyrirspurnaáætlanir. Hins vegar, síðan útgáfa 2016, hefur það oft neikvæð áhrif á nokkuð langan heildarframkvæmdartíma fyrirspurna.
Einnig, fyrir kerfi með mikið af vísitölum (til dæmis fyrir 1C gagnagrunna), mæli ég með því að virkja rekja fána 2330, sem gerir söfnun vísitölunotkunar óvirka, sem hefur almennt jákvæð áhrif á kerfið.
Þú getur lært meira um rekja fána hér
Af hlekknum hér að ofan er einnig mikilvægt að huga að útgáfum og smíðum MS SQL Server, þar sem fyrir nýrri útgáfur eru sum rekjafánar sjálfkrafa virkjuð eða hafa engin áhrif.
Þú getur virkjað eða slökkt á rekjafánanum með því að nota DBCC TRACEON og DBCC TRACEOFF skipanirnar, í sömu röð. Sjá nánari upplýsingar hér
Þú getur fengið stöðu rekja fána með því að nota DBCC TRACESTATUS skipunina: meira
Til þess að rekja flögg sé innifalin í sjálfvirkri ræsingu MS SQL Server þjónustunnar þarftu að fara í SQL Server Configuration Manager og bæta þessum rekjaflöggum við í gegnum -T í þjónustueiginleikum:
Sumir þættir við eftirlit með MS SQL Server. Ráðleggingar um uppsetningu sporfána

Niðurstöður

Þessi grein skoðaði nokkra þætti í vöktun MS SQL Server, með hjálp sem þú getur fljótt greint skort á vinnsluminni og ókeypis CPU tíma, auk fjölda annarra minna augljósra vandamála. Farið var yfir algengustu sporfánana.

Heimildir:

» Biðtölfræði fyrir SQL Server
» SQL Server bið tölfræði eða vinsamlegast segðu mér hvar það særir
» Kerfissýn sys.dm_os_schedulers
» Notkun Zabbix til að fylgjast með MS SQL Server Database
» SQL lífsstíll
» Rekja fánar
» sql.ru

Heimild: www.habr.com

Bæta við athugasemd