Parathënie libri
Shumë shpesh, përdoruesit, zhvilluesit dhe administratorët e MS SQL Server DBMS hasin probleme të performancës së bazës së të dhënave ose DBMS në tërësi, kështu që monitorimi i MS SQL Server është shumë i rëndësishëm.
Ky artikull është një shtesë e artikullit dhe do të mbulojë disa aspekte të monitorimit të MS SQL Server, në veçanti: mënyrën e përcaktimit të shpejtë të burimeve që mungojnë, si dhe rekomandimet për vendosjen e flamujve të gjurmës.
Që skriptet e mëposhtme të funksionojnë, duhet të krijoni një skemë inf në bazën e të dhënave të dëshiruar si më poshtë:
Krijimi i një skeme info
use <имя_БД>;
go
create schema inf;
Metoda për zbulimin e mungesës së RAM-it
Treguesi i parë i mungesës së RAM-it është rasti kur një shembull i MS SQL Server ha të gjithë RAM-in e alokuar për të.
Për ta bërë këtë, ne do të krijojmë paraqitjen e mëposhtme të inf.vRAM:
Krijimi i pamjes 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;
Pastaj mund të përcaktoni që një shembull i MS SQL Server konsumon të gjithë memorien e alokuar për të nga pyetja e mëposhtme:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Nëse SQL_server_physical_memory_in_use_Mb është vazhdimisht më i madh ose i barabartë me SQL_server_committed_target_Mb, atëherë statistikat e pritjes duhet të kontrollohen.
Për të përcaktuar mungesën e RAM-it përmes statistikave të pritjes, le të krijojmë pamjen inf.vWaits:
Krijimi i pamjes 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];
Në këtë rast, ju mund të përcaktoni mungesën e RAM-it me pyetjen e mëposhtme:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Këtu duhet t'i kushtoni vëmendje treguesve Përqindje dhe AvgWait_S. Nëse ato janë domethënëse në tërësinë e tyre, atëherë ka një probabilitet shumë të lartë që nuk ka mjaftueshëm RAM për shembullin MS SQL Server. Vlerat e rëndësishme përcaktohen individualisht për secilin sistem. Megjithatë, mund të filloni me sa vijon: Përqindja>=1 dhe AvgWait_S>=0.005.
Për të nxjerrë tregues në një sistem monitorimi (për shembull, Zabbix), mund të krijoni dy pyetjet e mëposhtme:
- sa lloje pritjesh janë zënë nga RAM në përqindje (shuma e të gjitha llojeve të tilla të pritjeve):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - sa lloje pritjeje RAM marrin në milisekonda (vlera maksimale e të gjitha vonesave mesatare për të gjitha llojet e tilla të pritjes):
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' );
Bazuar në dinamikën e vlerave të marra për këta dy tregues, mund të konkludojmë nëse ka mjaft RAM për një shembull të MS SQL Server.
Metoda e zbulimit të mbingarkesës së CPU
Për të identifikuar mungesën e kohës së procesorit, mjafton të përdorni pamjen e sistemit sys.dm_os_schedulers. Këtu, nëse runnable_tasks_count është vazhdimisht më i madh se 1, atëherë ekziston një probabilitet i lartë që numri i bërthamave të mos jetë i mjaftueshëm për shembullin MS SQL Server.
Për të nxjerrë një tregues në një sistem monitorimi (për shembull, Zabbix), mund të krijoni pyetjen e mëposhtme:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Bazuar në dinamikën e vlerave të marra për këtë tregues, mund të konkludojmë nëse ka kohë të mjaftueshme të procesorit (numri i bërthamave të CPU) për një shembull të MS SQL Server.
Megjithatë, është e rëndësishme të kihet parasysh fakti që vetë kërkesat mund të kërkojnë shumë fije njëherësh. Dhe ndonjëherë optimizuesi nuk mund të vlerësojë saktë kompleksitetin e vetë pyetjes. Më pas, kërkesës mund t'i alokohen shumë tema që nuk mund të përpunohen në të njëjtën kohë në kohën e caktuar. Dhe kjo shkakton gjithashtu një lloj pritjeje të lidhur me mungesën e kohës së procesorit dhe rritje të radhës për programuesit që përdorin bërthama specifike të CPU-së, d.m.th. treguesi runnable_tasks_count do të rritet në kushte të tilla.
Në këtë rast, përpara se të rritet numri i bërthamave të CPU, është e nevojshme të konfiguroni saktë vetitë e paralelizmit të vetë shembullit të MS SQL Server, dhe nga versioni 2016, të konfiguroni saktë vetitë e paralelizmit të bazave të të dhënave të kërkuara:
Këtu duhet t'i kushtoni vëmendje parametrave të mëposhtëm:
- Shkalla maksimale e paralelizmit - përcakton numrin maksimal të thread-ve që mund t'i ndahen çdo kërkese (parazgjedhja është 0 - e kufizuar vetëm nga vetë sistemi operativ dhe edicioni i MS SQL Server)
- Pragu i kostos për paralelizmin - kostoja e vlerësuar e paralelizmit (parazgjedhja është 5)
- Max DOP - vendos numrin maksimal të thread-eve që mund t'i ndahen çdo pyetjeje në nivelin e bazës së të dhënave (por jo më shumë se vlera e vetive "Max Degree of Parallelism") (parazgjedhja është 0 - e kufizuar vetëm nga vetë sistemi operativ dhe edicioni i MS SQL Server, si dhe kufizimi në vetinë "Max Degree of Parallelism" të të gjithë instancës së MS SQL Server)
Këtu është e pamundur të jepet një recetë po aq e mirë për të gjitha rastet, d.m.th. ju duhet të analizoni pyetje të rënda.
Nga përvoja ime, unë rekomandoj algoritmin e mëposhtëm të veprimeve për sistemet OLTP për vendosjen e vetive të paralelizmit:
- së pari çaktivizoni paralelizmin duke vendosur shkallën maksimale të paralelizmit në të gjithë shembullin në 1
- analizoni kërkesat më të rënda dhe zgjidhni numrin optimal të fijeve për to
- vendosni shkallën maksimale të paralelizmit në numrin optimal të përzgjedhur të thread-ve të marra nga hapi 2, dhe për bazat e të dhënave specifike vendosni vlerën Max DOP të marrë nga hapi 2 për secilën bazë të dhënash
- analizoni kërkesat më të rënda dhe identifikoni efektin negativ të multithreading. Nëse është, atëherë rrisni pragun e kostos për paralelizmin.
Për sisteme të tilla si 1C, Microsoft CRM dhe Microsoft NAV, në shumicën e rasteve, ndalimi i multithreading është i përshtatshëm
Gjithashtu, nëse ekziston një botim Standard, atëherë në shumicën e rasteve ndalimi i multithreading është i përshtatshëm për faktin se ky botim është i kufizuar në numrin e bërthamave të CPU.
Për sistemet OLAP, algoritmi i përshkruar më sipër nuk është i përshtatshëm.
Nga përvoja ime, unë rekomandoj algoritmin e mëposhtëm të veprimeve për sistemet OLAP për vendosjen e vetive të paralelizmit:
- analizoni kërkesat më të rënda dhe zgjidhni numrin optimal të fijeve për to
- vendosni shkallën maksimale të paralelizmit në numrin optimal të përzgjedhur të thread-ve të marra nga hapi 1, dhe për bazat e të dhënave specifike vendosni vlerën Max DOP të marrë nga hapi 1 për secilën bazë të dhënash
- analizoni pyetjet më të rënda dhe identifikoni efektin negativ të kufizimit të konkurencës. Nëse është, atëherë ose ulni pragun e kostos për vlerën e paralelizmit, ose përsëritni hapat 1-2 të këtij algoritmi
Kjo do të thotë, për sistemet OLTP ne kalojmë nga një fillesë në multi-threading, dhe për sistemet OLAP, përkundrazi, kalojmë nga multi-threading në një fijezim. Kështu, ju mund të zgjidhni cilësimet optimale të paralelizmit si për një bazë të dhënash specifike, ashtu edhe për të gjithë shembullin e MS SQL Server.
Është gjithashtu e rëndësishme të kuptohet se cilësimet e vetive të paralelizmit duhet të ndryshohen me kalimin e kohës, bazuar në rezultatet e monitorimit të performancës së MS SQL Server.
Udhëzime për vendosjen e flamujve të gjurmëve
Nga përvoja ime dhe përvoja e kolegëve të mi, për performancë optimale, unë rekomandoj vendosjen e shenjave të mëposhtme të gjurmës në nivelin e ekzekutimit të shërbimit MS SQL Server për versionet 2008-2016:
- 610 - Regjistrimi i reduktuar i inserteve në tabelat e indeksuara. Mund të ndihmojë me futjet në tabela me shumë regjistrime dhe shumë transaksione, me pritje të shpeshta të gjata WRITELOG për ndryshime në indekse
- 1117 - Nëse një skedar në një grup skedari plotëson kërkesat e pragut të rritjes automatike, të gjithë skedarët në grupin e skedarëve rriten
- 1118 - Detyron të gjitha objektet të vendosen në shtrirje të ndryshme (ndalimi i shtrirjeve të përziera), gjë që minimizon nevojën për të skanuar faqen SGAM, e cila përdoret për të gjurmuar shtrirje të përziera
- 1224 - Çaktivizon përshkallëzimin e bllokimit bazuar në numrin e bravave. Megjithatë, përdorimi i tepërt i kujtesës mund të shkaktojë përshkallëzimin e bllokimit
- 2371 - Ndryshon pragun fiks të përditësimit automatik të statistikave në pragun dinamik të përditësimit automatik të statistikave. E rëndësishme për përditësimin e planeve të pyetjeve për tabela të mëdha, ku një numërim i gabuar i të dhënave rezulton në plane ekzekutimi të gabuara
- 3226 - Mbyll mesazhet e suksesit të rezervimit në regjistrin e gabimeve
- 4199 - Përfshin ndryshime në optimizuesin e pyetjeve të lëshuara në CU dhe Paketat e Shërbimit të Serverit SQL
- 6532-6534 - Përfshin përmirësime të performancës për operacionet e pyetjeve në llojet e të dhënave hapësinore
- 8048 - Konverton objektet e memories të ndarë NUMA në ato të ndara në CPU
- 8780 - Aktivizon ndarjen shtesë të kohës për planifikimin e pyetjeve. Disa kërkesa pa këtë flamur mund të refuzohen sepse nuk kanë një plan pyetjesh (gabim shumë i rrallë)
- 8780 - 9389 - Aktivizon bufer shtesë dinamike të memories së grantit për deklaratat e modalitetit të grupit, i cili lejon operatorin e modalitetit të grupit të kërkojë memorie shtesë dhe të shmangë lëvizjen e të dhënave në tempdb nëse disponohet memorie shtesë
Gjithashtu, përpara vitit 2016, është e dobishme të aktivizoni flamurin e gjurmës 2301, i cili mundëson optimizime të zgjeruara të mbështetjes së vendimeve dhe kështu ndihmon në zgjedhjen e planeve më të sakta të pyetjeve. Sidoqoftë, që nga versioni 2016, ai shpesh ka një efekt negativ në kohët e përgjithshme mjaft të gjata të ekzekutimit të pyetjeve.
Gjithashtu, për sistemet me shumë indekse (për shembull, për bazat e të dhënave 1C), unë rekomandoj aktivizimin e gjurmës së flamurit 2330, i cili çaktivizon mbledhjen e përdorimit të indeksit, gjë që përgjithësisht ka një efekt pozitiv në sistem.
Për më shumë informacion rreth flamujve të gjurmës, shihni
Nga lidhja e mësipërme, është gjithashtu e rëndësishme të merren parasysh versionet dhe ndërtimet e MS SQL Server, pasi për versionet më të reja, disa flamuj gjurmë janë aktivizuar si parazgjedhje ose nuk kanë efekt.
Mund ta aktivizoni dhe çaktivizoni flamurin e gjurmës me komandat DBCC TRACEON dhe DBCC TRACEOFF, përkatësisht. Për më shumë detaje shihni
Ju mund të merrni statusin e flamujve të gjurmës duke përdorur komandën DBCC TRACESTATUS:
Në mënyrë që flamujt e gjurmimit të përfshihen në fillimin automatik të shërbimit MS SQL Server, duhet të shkoni te SQL Server Configuration Manager dhe të shtoni këto flamuj gjurmësh nëpërmjet -T në vetitë e shërbimit:
Rezultatet e
Në këtë artikull, u analizuan disa aspekte të monitorimit të MS SQL Server, me ndihmën e të cilave mund të identifikoni shpejt mungesën e RAM-it dhe kohën e lirë të CPU-së, si dhe një numër problemesh të tjera më pak të dukshme. Flamujt gjurmë më të përdorur janë rishikuar.
Burimet:
»
»
»
»
»
»
»
Burimi: www.habr.com