priekšvārds
Diezgan bieži MS SQL Server DBVS lietotāji, izstrādātāji un administratori saskaras ar datu bāzes vai visas DBVS veiktspējas problēmām, tāpēc MS SQL Server uzraudzība ir ļoti aktuāla.
Šis raksts ir raksta papildinājums un tas aptvers dažus MS SQL Server uzraudzības aspektus, jo īpaši: kā ātri noteikt, kuri resursi trūkst, kā arī ieteikumus izsekošanas karogu iestatīšanai.
Lai darbotos tālāk norādītie skripti, vēlamajā datu bāzē ir jāizveido inf shēma šādi:
Inf shēmas izveide
use <имя_БД>;
go
create schema inf;
RAM trūkuma noteikšanas metode
Pirmais RAM trūkuma rādītājs ir gadījums, kad MS SQL Server gadījums apēd visu tai piešķirto RAM.
Lai to izdarītu, mēs izveidosim šādu inf.vRAM atveidojumu:
Skata inf.vRAM izveide
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;
Pēc tam varat noteikt, ka MS SQL Server gadījums patērē visu atmiņu, kas tam piešķirta ar šādu vaicājumu:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Ja SQL_server_physical_memory_in_use_Mb konsekventi ir lielāks vai vienāds ar SQL_server_committed_target_Mb, tad ir jāpārbauda gaidīšanas statistika.
Lai noteiktu RAM trūkumu, izmantojot gaidīšanas statistiku, izveidosim skatu inf.vWaits:
Skata inf.vWaits izveide
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];
Šajā gadījumā jūs varat noteikt RAM trūkumu ar šādu vaicājumu:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Šeit jums jāpievērš uzmanība rādītājiem Percentage un AvgWait_S. Ja tie ir nozīmīgi kopumā, tad pastāv ļoti liela varbūtība, ka MS SQL Server instancei nepietiek RAM. Nozīmīgās vērtības katrai sistēmai tiek noteiktas atsevišķi. Tomēr varat sākt ar sekojošo: Procenti>=1 un AvgWait_S>=0.005.
Lai izvadītu indikatorus uzraudzības sistēmai (piemēram, Zabbix), varat izveidot šādus divus vaicājumus:
- cik gaidīšanas veidus aizņem RAM procentos (visu šādu gaidīšanas veidu summa):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - cik RAM gaidīšanas veidu aizņem milisekundēs (visu vidējo aizkavi maksimālā vērtība visiem šādiem gaidīšanas veidiem):
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' );
Pamatojoties uz šo divu rādītāju iegūto vērtību dinamiku, mēs varam secināt, vai MS SQL Server gadījumam ir pietiekami daudz RAM.
CPU pārslodzes noteikšanas metode
Lai noteiktu procesora laika trūkumu, pietiek izmantot sistēmas skatu sys.dm_os_schedulers. Šeit, ja runnable_tasks_count pastāvīgi ir lielāks par 1, tad pastāv liela varbūtība, ka MS SQL Server instancē nepietiek ar kodolu skaitu.
Lai izvadītu indikatoru uzraudzības sistēmai (piemēram, Zabbix), varat izveidot šādu vaicājumu:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Pamatojoties uz šī rādītāja iegūto vērtību dinamiku, mēs varam secināt, vai ir pietiekami daudz procesora laika (CPU kodolu skaits) MS SQL Server gadījumam.
Tomēr ir svarīgi paturēt prātā, ka paši pieprasījumi var pieprasīt vairākus pavedienus vienlaikus. Un dažreiz optimizētājs nevar pareizi novērtēt paša vaicājuma sarežģītību. Tad pieprasījumam var tikt piešķirts pārāk daudz pavedienu, kurus konkrētajā laikā nevar apstrādāt vienlaikus. Un tas arī izraisa gaidīšanas veidu, kas saistīts ar procesora laika trūkumu un rindas pieaugumu plānotājiem, kas izmanto noteiktus CPU kodolus, t.i., šādos apstākļos pieaugs palaišanas_uzdevumu_skaita indikators.
Šajā gadījumā pirms CPU kodolu skaita palielināšanas ir pareizi jākonfigurē pašas MS SQL Server instances paralēlisma rekvizīti un, sākot ar 2016. gada versiju, pareizi jākonfigurē nepieciešamo datu bāzu paralēlisma rekvizīti:
Šeit jums jāpievērš uzmanība šādiem parametriem:
- Max Degree of Parallelism - iestata maksimālo pavedienu skaitu, ko var piešķirt katram pieprasījumam (noklusējums ir 0 - ierobežo tikai pati operētājsistēma un MS SQL Server izdevums)
- Paralēlisma izmaksu slieksnis — aptuvenās paralēlisma izmaksas (noklusējuma vērtība ir 5)
- Max DOP - iestata maksimālo pavedienu skaitu, ko var piešķirt katram vaicājumam datu bāzes līmenī (bet ne vairāk kā rekvizīta "Maksimālā paralēlisma pakāpe" vērtība) (noklusējums ir 0 - ierobežo tikai pati operētājsistēma un MS SQL Server izdevums, kā arī ierobežojums attiecībā uz rekvizītu "Max Degree of Parallelism" visai MS SQL Server instancei)
Šeit nav iespējams sniegt vienlīdz labu recepti visiem gadījumiem, t.i., jums ir jāanalizē smagi vaicājumi.
No savas pieredzes es iesaku šādu darbību algoritmu OLTP sistēmām paralēlisma rekvizītu iestatīšanai:
- vispirms atspējojiet paralēlismu, iestatot instancē Max Degree of Parallelism uz 1
- analizējiet smagākos pieprasījumus un atlasiet tiem optimālo pavedienu skaitu
- iestatiet maksimālo paralēlisma pakāpi izvēlētajam optimālajam pavedienu skaitam, kas iegūts no 2. darbības, un konkrētām datu bāzēm iestatiet katrai datubāzei maksimālo DOP vērtību, kas iegūta no 2. darbības.
- analizēt vissmagākos pieprasījumus un noteikt daudzpavedienu negatīvo ietekmi. Ja tā ir, tad palieliniet paralēlisma izmaksu slieksni.
Sistēmām, piemēram, 1C, Microsoft CRM un Microsoft NAV, vairumā gadījumu ir piemērots daudzpavedienu aizliegums.
Tāpat, ja ir Standarta izdevums, tad vairumā gadījumu ir piemērots daudzpavedienu aizliegums, jo šim izdevumam ir ierobežots CPU kodolu skaits.
OLAP sistēmām iepriekš aprakstītais algoritms nav piemērots.
No savas pieredzes es iesaku šādu darbību algoritmu OLAP sistēmām paralēlisma rekvizītu iestatīšanai:
- analizējiet smagākos pieprasījumus un atlasiet tiem optimālo pavedienu skaitu
- iestatiet maksimālo paralēlisma pakāpi izvēlētajam optimālajam pavedienu skaitam, kas iegūts no 1. darbības, un konkrētām datu bāzēm iestatiet katrai datubāzei maksimālo DOP vērtību, kas iegūta no 1. darbības.
- analizējiet vissmagākos vaicājumus un identificējiet vienlaicības ierobežošanas negatīvo ietekmi. Ja tā ir, tad vai nu pazeminiet paralēlisma vērtības izmaksu slieksni, vai atkārtojiet šī algoritma 1.–2.
Tas ir, OLTP sistēmām mēs pārejam no viena pavediena uz vairākpavedienu, un OLAP sistēmām, gluži pretēji, mēs pārejam no vairāku pavedienu uz viena pavediena. Tādējādi jūs varat izvēlēties optimālos paralēlisma iestatījumus gan konkrētai datubāzei, gan visai MS SQL Server instancei.
Ir arī svarīgi saprast, ka paralēlisma rekvizītu iestatījumi laika gaitā ir jāmaina, pamatojoties uz MS SQL Server veiktspējas uzraudzības rezultātiem.
Vadlīnijas izsekošanas karogu iestatīšanai
No savas un kolēģu pieredzes, lai nodrošinātu optimālu veiktspēju, es iesaku iestatīt šādus izsekošanas karogus MS SQL Server pakalpojuma darbības līmenī 2008.–2016. gada versijām:
- 610 - Samazināta ieliktņu reģistrēšana indeksētās tabulās. Var palīdzēt ar ievietošanu tabulās ar daudziem ierakstiem un daudzām transakcijām, bieži WRITELOG gaidot indeksu izmaiņas
- 1117 - ja fails failu grupā atbilst automātiskās izaugsmes sliekšņa prasībām, visi faili failu grupā palielinās
- 1118 — liek visiem objektiem atrasties dažādos apmēros (jauktu apmēru aizliegums), kas samazina vajadzību skenēt SGAM lapu, kas tiek izmantota jaukta apjoma izsekošanai.
- 1224 — atspējo slēdzenes eskalāciju, pamatojoties uz slēdzeņu skaitu. Tomēr pārmērīgs atmiņas lietojums var izraisīt bloķēšanas eskalāciju
- 2371 — maina fiksēto automātiskās statistikas atjaunināšanas slieksni uz dinamiskās automātiskās statistikas atjaunināšanas slieksni. Svarīgi, lai atjauninātu vaicājumu plānus lielām tabulām, kur nepareizs ierakstu skaits rada kļūdainus izpildes plānus
- 3226 — izslēdz dublēšanas panākumu ziņojumus kļūdu žurnālā
- 4199 — ietver izmaiņas vaicājumu optimizētājā, kas izlaistas CU un SQL Server servisa pakotnēs
- 6532-6534 — ietverti veiktspējas uzlabojumi vaicājumu darbībām ar telpisko datu veidiem
- 8048 — pārvērš NUMA sadalītos atmiņas objektus par CPU sadalītajiem objektiem
- 8780 — iespējo papildu laika piešķiršanu vaicājumu plānošanai. Daži pieprasījumi bez šī karoga var tikt noraidīti, jo tiem nav vaicājumu plāna (ļoti reta kļūda)
- 8780 - 9389 - Iespējo papildu dinamiskās piešķiršanas atmiņas buferi pakešrežīma paziņojumiem, kas ļauj pakešu režīma operatoram pieprasīt papildu atmiņu un izvairīties no datu pārvietošanas uz tempdb, ja ir pieejama papildu atmiņa.
Arī pirms 2016. gada ir lietderīgi iespējot izsekošanas karogu 2301, kas nodrošina uzlabotu lēmumu atbalsta optimizāciju un tādējādi palīdz izvēlēties pareizākus vaicājumu plānus. Tomēr no 2016. gada versijas tas bieži vien negatīvi ietekmē diezgan ilgu kopējo vaicājuma izpildes laiku.
Tāpat sistēmām ar daudz indeksu (piemēram, 1C datu bāzēm) iesaku iespējot izsekošanas karogu 2330, kas atspējo indeksa lietojuma apkopošanu, kas kopumā pozitīvi ietekmē sistēmu.
Papildinformāciju par izsekošanas karodziņiem skatiet
No iepriekš minētās saites ir svarīgi ņemt vērā arī MS SQL Server versijas un būvējumus, jo jaunākām versijām daži izsekošanas karodziņi ir iespējoti pēc noklusējuma vai tiem nav nekādas ietekmes.
Izsekošanas karogu var ieslēgt un izslēgt, izmantojot attiecīgi komandas DBCC TRACEON un DBCC TRACEOFF. Sīkāku informāciju skatiet
Izsekošanas karogu statusu var iegūt, izmantojot komandu DBCC TRACESTATUS:
Lai izsekošanas karodziņi tiktu iekļauti MS SQL Server pakalpojuma autostartē, jums ir jādodas uz SQL Server konfigurācijas pārvaldnieku un pakalpojuma rekvizītos jāpievieno šie izsekošanas karodziņi, izmantojot taustiņu -T:
Rezultāti
Šajā rakstā tika analizēti daži MS SQL Server uzraudzības aspekti, ar kuru palīdzību jūs varat ātri noteikt RAM un brīvā CPU laika trūkumu, kā arī vairākas citas mazāk acīmredzamas problēmas. Pārskatīti visbiežāk izmantotie izsekošanas karodziņi.
Avoti:
»
»
»
»
»
»
»
Avots: www.habr.com