Daħla
Spiss, l-utenti, l-iżviluppaturi u l-amministraturi tal-MS SQL Server DBMS jiltaqgħu ma 'problemi ta' prestazzjoni tad-database jew id-DBMS kollha kemm hi, għalhekk il-monitoraġġ tal-MS SQL Server huwa rilevanti ħafna.
Dan l-artikolu huwa żieda għall-artikolu u se jkopri xi aspetti tal-monitoraġġ tal-MS SQL Server, b'mod partikolari: kif tiddetermina malajr liema riżorsi huma neqsin, kif ukoll rakkomandazzjonijiet biex jiġu stabbiliti bnadar ta 'traċċa.
Biex l-iskripts li ġejjin jaħdmu, għandek bżonn toħloq skema inf fid-database mixtieqa kif ġej:
Ħolqien ta' inf schema
use <имя_БД>;
go
create schema inf;
Metodu għall-iskoperta ta 'nuqqas ta' RAM
L-ewwel indikatur tan-nuqqas ta 'RAM huwa l-każ meta istanza ta' MS SQL Server tiekol l-RAM kollha allokati lilha.
Biex tagħmel dan, aħna se noħolqu r-rappreżentazzjoni li ġejja ta 'inf.vRAM:
Ħolqien tal-veduta 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;
Imbagħad tista 'tiddetermina li eżempju ta' MS SQL Server jikkonsma l-memorja kollha allokata lilha mill-mistoqsija li ġejja:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Jekk SQL_server_physical_memory_in_use_Mb huwa konsistentement akbar minn jew ugwali għal SQL_server_committed_target_Mb, allura l-istatistika ta' stennija għandha tiġi kkontrollata.
Biex tiddetermina n-nuqqas ta’ RAM permezz ta’ statistika ta’ stennija, ejja noħolqu l-veduta inf.vWaits:
Ħolqien tal-inf.vWaits View
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];
F'dan il-każ, tista 'tiddetermina n-nuqqas ta' RAM bil-mistoqsija li ġejja:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Hawnhekk għandek bżonn tagħti attenzjoni lill-indikaturi Perċentwali u AvgWait_S. Jekk huma sinifikanti fit-totalità tagħhom, allura hemm probabbiltà għolja ħafna li ma jkunx hemm biżżejjed RAM għall-istanza MS SQL Server. Valuri sinifikanti huma determinati individwalment għal kull sistema. Madankollu, tista 'tibda b'dan li ġej: Perċentwali>=1 u AvgWait_S>=0.005.
Biex toħroġ indikaturi għal sistema ta' monitoraġġ (pereżempju, Zabbix), tista' toħloq iż-żewġ mistoqsijiet li ġejjin:
- kemm tipi ta’ stennija huma okkupati mir-RAM f’perċentwali (is-somma ta’ dawn it-tipi kollha ta’ stennija):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - kemm-il tip ta' stennija RAM jieħu f'millisekondi (il-valur massimu tad-dewmien medju kollu għat-tipi kollha ta' stennija bħal dawn):
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' );
Ibbażat fuq id-dinamika tal-valuri miksuba għal dawn iż-żewġ indikaturi, nistgħu nikkonkludu jekk hemmx biżżejjed RAM għal eżempju ta 'MS SQL Server.
Metodu ta 'Sejbien ta' Tagħbija Eċċessiva tas-CPU
Biex tidentifika n-nuqqas ta 'ħin tal-proċessur, huwa biżżejjed li tuża l-veduta tas-sistema sys.dm_os_schedulers. Hawnhekk, jekk il-runnable_tasks_count huwa kontinwament akbar minn 1, allura hemm probabbiltà kbira li n-numru ta 'cores ma jkunx biżżejjed għall-istanza MS SQL Server.
Biex toħroġ indikatur għal sistema ta' monitoraġġ (per eżempju, Zabbix), tista' toħloq il-mistoqsija li ġejja:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Ibbażat fuq id-dinamika tal-valuri miksuba għal dan l-indikatur, nistgħu nikkonkludu jekk hemmx biżżejjed ħin tal-proċessur (in-numru ta 'qalba tas-CPU) għal eżempju ta' MS SQL Server.
Madankollu, huwa importanti li wieħed iżomm f'moħħu l-fatt li t-talbiet infushom jistgħu jitolbu ħjut multipli f'daqqa. U xi kultant l-ottimizzatur ma jistax jistma b'mod korrett il-kumplessità tal-mistoqsija nnifisha. Imbagħad it-talba tista 'tiġi allokata wisq ħjut li ma jistgħux jiġu pproċessati fl-istess ħin fil-ħin partikolari. U dan jikkawża wkoll tip ta 'stennija assoċjata ma' nuqqas ta 'ħin tal-proċessur, u tkabbir tal-kju għal skedaturi li jużaw cores CPU speċifiċi, jiġifieri l-indikatur runnable_tasks_count se jikber f'kundizzjonijiet bħal dawn.
F'dan il-każ, qabel ma jiżdied in-numru ta 'cores CPU, huwa meħtieġ li jiġu kkonfigurati b'mod korrett il-proprjetajiet tal-paralleliżmu tal-istanza MS SQL Server innifsu, u mill-verżjoni 2016, jiġi kkonfigurat b'mod korrett il-proprjetajiet tal-paralleliżmu tad-databases meħtieġa:
Hawnhekk għandek tagħti attenzjoni lill-parametri li ġejjin:
- Max Grad of Parallelism - jistabbilixxi n-numru massimu ta 'ħjut li jistgħu jiġu allokati għal kull talba (l-inadempjenza hija 0 - limitata biss mis-sistema operattiva nnifisha u l-edizzjoni ta' MS SQL Server)
- Limitu tal-Ispejjeż għall-Paralleliżmu - l-ispiża stmata tal-paralleliżmu (default huwa 5)
- Max DOP - jistabbilixxi n-numru massimu ta 'ħjut li jistgħu jiġu allokati għal kull mistoqsija fil-livell tad-database (iżda mhux aktar mill-valur tal-proprjetà "Grad Massimu ta' Paralleliżmu") (default huwa 0 - limitat biss mis-sistema operattiva nnifisha u l-edizzjoni tal-MS SQL Server, kif ukoll ir-restrizzjoni fuq il-proprjetà "Max Degree of Parallelism" tal-istanza sħiħa tal-MS SQL Server)
Hawnhekk huwa impossibbli li tagħti riċetta daqstant tajba għall-każijiet kollha, jiġifieri għandek bżonn tanalizza mistoqsijiet tqal.
Mill-esperjenza tiegħi stess, nirrakkomanda l-algoritmu ta 'azzjonijiet li ġej għal sistemi OLTP għat-twaqqif ta' proprjetajiet ta 'paralleliżmu:
- l-ewwel iddiżattiva l-paralleliżmu billi tistabbilixxi l-Grad Max ta' Paralleliżmu għall-istanza kollha għal 1
- tanalizza l-itqal talbiet u agħżel in-numru ottimali ta 'ħjut għalihom
- issettja l-Grad Max ta 'Paralleliżmu għan-numru ottimali magħżul ta' ħjut miksuba mill-pass 2, u għal databases speċifiċi waqqaf il-valur Max DOP miksub mill-pass 2 għal kull database
- tanalizza l-aktar talbiet itqal u identifika l-effett negattiv tal-multithreading. Jekk hu, allura żid il-Livell tal-Ispejjeż għall-Paralleliżmu.
Għal sistemi bħal 1C, Microsoft CRM u Microsoft NAV, fil-biċċa l-kbira tal-każijiet, il-projbizzjoni tal-multithreading hija adattata
Ukoll, jekk ikun hemm edizzjoni Standard, allura f'ħafna każijiet il-projbizzjoni ta 'multithreading hija adattata minħabba l-fatt li din l-edizzjoni hija limitata fin-numru ta' cores CPU.
Għas-sistemi OLAP, l-algoritmu deskritt hawn fuq mhuwiex adattat.
Mill-esperjenza tiegħi stess, nirrakkomanda l-algoritmu ta 'azzjonijiet li ġej għal sistemi OLAP għat-twaqqif ta' proprjetajiet ta 'paralleliżmu:
- tanalizza l-itqal talbiet u agħżel in-numru ottimali ta 'ħjut għalihom
- issettja l-Grad Max ta 'Paralleliżmu għan-numru ottimali magħżul ta' ħjut miksuba mill-pass 1, u għal databases speċifiċi waqqaf il-valur Max DOP miksub mill-pass 1 għal kull database
- tanalizza l-iktar mistoqsijiet itqal u identifika l-effett negattiv tal-limitazzjoni tal-konkorrenza. Jekk hu hekk, allura jew inaqqas il-Valur tal-Livell tal-Ispejjeż għall-Paralleliżmu, jew irrepeti l-passi 1-2 ta’ dan l-algoritmu
Jiġifieri, għal sistemi OLTP immorru minn single-threading għal multi-threading, u għal sistemi OLAP, għall-kuntrarju, immorru minn multi-threading għal single-threading. Għalhekk, tista 'tagħżel is-settings tal-paralleliżmu ottimali kemm għal database speċifika kif ukoll għall-istanza sħiħa ta' MS SQL Server.
Huwa wkoll importanti li wieħed jifhem li s-settings tal-proprjetajiet tal-paralleliżmu jeħtieġ li jinbidlu maż-żmien, ibbażati fuq ir-riżultati tal-monitoraġġ tal-prestazzjoni tal-MS SQL Server.
Linji gwida għall-Issettjar ta' Bnadar ta' Traċċa
Mill-esperjenza tiegħi stess u l-esperjenza tal-kollegi tiegħi, għal prestazzjoni ottimali, nirrakkomanda li nissettja l-bnadar ta 'traċċa li ġejjin fil-livell ta' tmexxija tas-servizz MS SQL Server għall-verżjonijiet 2008-2016:
- 610 - Tnaqqis tal-illoggjar ta' inserzjonijiet f'tabelli indiċjati. Jista 'jgħin b'inserzjonijiet f'tabelli b'ħafna rekords u ħafna tranżazzjonijiet, b'stennija twila ta' WRITELOG ta' spiss għal bidliet fl-indiċi
- 1117 - Jekk fajl fi filegroup jissodisfa r-rekwiżiti tal-limitu ta' tkabbir awtomatiku, il-fajls kollha fil-filegroup jikbru
- 1118 - Ġiegħel li l-oġġetti kollha jkunu lokalizzati f'estensjonijiet differenti (projbizzjoni ta' estensjonijiet imħallta), li jimminimizza l-ħtieġa li tiġi skennjata l-paġna SGAM, li tintuża biex issegwi estensjonijiet imħallta
- 1224 - Jiddiżattiva l-eskalazzjoni tal-lock ibbażata fuq in-numru ta 'serraturi. Madankollu, użu eċċessiv tal-memorja jista 'jikkawża eskalazzjoni tal-lock
- 2371 - Tibdel il-limitu fiss tal-aġġornament tal-istatistika awtomatika għal-limitu tal-aġġornament tal-istatistika awtomatika dinamika. Importanti għall-aġġornament tal-pjanijiet ta' mistoqsijiet għal tabelli kbar, fejn għadd mhux korrett ta' rekords jirriżulta fi pjanijiet ta' eżekuzzjoni żbaljati
- 3226 - Jrażżan il-messaġġi ta' suċċess tal-backup fil-ġurnal tal-iżbalji
- 4199 - Jinkludi bidliet għall-ottimizzatur tal-mistoqsijiet rilaxxati f'CUs u SQL Server Service Packs
- 6532-6534 - Jinkludi titjib fil-prestazzjoni għal operazzjonijiet ta' mistoqsija fuq tipi ta' dejta ġeografika
- 8048 - Jikkonverti oġġetti tal-memorja diviżorji NUMA għal dawk partizzjonati tas-CPU
- 8780 - Jippermetti allokazzjoni ta' ħin addizzjonali għall-ippjanar tal-mistoqsijiet. Xi talbiet mingħajr din il-marka jistgħu jiġu rrifjutati minħabba li m'għandhomx pjan ta' mistoqsija (bug rari ħafna)
- 8780 - 9389 - Jippermetti buffer tal-memorja tal-għotja dinamika addizzjonali għal dikjarazzjonijiet tal-modalità tal-lott, li jippermetti lill-operatur tal-modalità tal-lott li jitlob aktar memorja u jevita li jċaqlaq id-dejta għal tempdb jekk ikun hemm aktar memorja disponibbli
Qabel l-2016 ukoll, huwa utli li tiġi attivata l-bandiera tat-traċċar 2301, li tippermetti ottimizzazzjonijiet mtejba ta 'appoġġ ta' deċiżjonijiet u b'hekk jgħin fl-għażla ta 'pjanijiet ta' mistoqsijiet aktar korretti. Madankollu, mill-verżjoni 2016, ħafna drabi għandha effett negattiv fuq ħinijiet ta' eżekuzzjoni tal-mistoqsijiet ġenerali pjuttost twal.
Barra minn hekk, għal sistemi b'ħafna indiċi (per eżempju, għal databases 1C), nirrakkomanda li l-marka tat-traċċar 2330 tiġi attivata, li tiddiżattiva l-ġbir tal-użu tal-indiċi, li ġeneralment ikollu effett pożittiv fuq is-sistema.
Għal aktar informazzjoni dwar il-bnadar tat-traċċa, ara
Mill-link t'hawn fuq, huwa wkoll importanti li tikkunsidra verżjonijiet u builds ta 'MS SQL Server, peress li għal verżjonijiet aktar ġodda, xi bnadar ta' traċċa huma attivati awtomatikament jew m'għandhom l-ebda effett.
Tista' tixgħel u titfi l-bandiera tat-traċċa bil-kmandi DBCC TRACEON u DBCC TRACEOFF, rispettivament. Għal aktar dettalji ara
Tista' tikseb l-istatus tal-bnadar tat-traċċa billi tuża l-kmand DBCC TRACESTATUS:
Sabiex il-bnadar tat-traċċa jkunu inklużi fl-awtostart tas-servizz tal-MS SQL Server, trid tmur għand il-Maniġer tal-Konfigurazzjoni tal-SQL Server u żid dawn il-bnadar tat-traċċa permezz -T fil-proprjetajiet tas-servizz:
Riżultati ta '
F'dan l-artikolu, ġew analizzati xi aspetti tal-monitoraġġ tal-MS SQL Server, li bl-għajnuna tagħhom tista 'malajr tidentifika n-nuqqas ta' RAM u ħin liberu tas-CPU, kif ukoll għadd ta 'problemi oħra inqas ovvji. Ġew riveduti l-bnadar tat-traċċar li jintużaw l-aktar komunement.
Sorsi:
»
»
»
»
»
»
»
Sors: www.habr.com