Antaŭparolo
Sufiĉe ofte, uzantoj, programistoj kaj administrantoj de MS SQL Server DBMS alfrontas rendimentajn problemojn de la datumbazo aŭ la DBMS entute, do monitorado de MS SQL Server estas tre grava.
Ĉi tiu artikolo estas aldono al la artikolo kaj ĝi ekzamenos kelkajn aspektojn de monitorado de MS SQL-Servilo, precipe: kiel rapide determini, kiuj rimedoj mankas, kaj ankaŭ rekomendojn por starigi spurflagojn.
Por ke la sekvaj skriptoj funkciu, vi devas krei inf-skemon en la dezirata datumbazo jene:
Kreante inf-skemon
use <имя_БД>;
go
create schema inf;
Metodo por detekti mankon de RAM
La unua indikilo de manko de RAM estas kiam kazo de MS SQL Server manĝas la tutan RAM asignitan al ĝi.
Por fari tion, kreu la sekvan reprezenton inf.vRAM:
Kreante la inf.vRAM-vidon
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;
Tiam vi povas determini, ke ekzemplo de MS SQL Server konsumas la tutan memoron asignitan al ĝi uzante la jenan demandon:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Se la indikilo SQL_server_physical_memory_in_use_Mb estas konstante ne malpli ol SQL_server_committed_target_Mb, tiam vi devas kontroli la atendan statistikon.
Por determini la mankon de RAM per atendaj statistikoj, ni kreu vidon inf.vWaits:
Kreante la inf.vWaits-vidon
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];
En ĉi tiu kazo, vi povas determini la mankon de RAM uzante la sekvan demandon:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Ĉi tie vi devas atenti la indikilojn Procentage kaj AvgWait_S. Se ili estas signifaj en sia tutaĵo, tiam estas tre alta probableco, ke la petskribo de MS SQL Server ne havas sufiĉe da RAM. Esencaj valoroj estas determinitaj individue por ĉiu sistemo. Tamen, vi povas komenci per la sekva indikilo: Procento>=1 kaj AvgWait_S>=0.005.
Por eligi indikilojn al monitora sistemo (ekzemple, Zabbix), vi povas krei la jenajn du demandojn:
- Kio estas la procento de atendspecoj por RAM (sumo por ĉiuj tiaj atendspecoj):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - kiom da RAM atendspecoj prenas en milisekundoj (la maksimuma valoro de ĉiuj mezaj prokrastoj por ĉiuj tiaj atendspecoj):
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' );
Surbaze de la dinamiko de la akiritaj valoroj por ĉi tiuj du indikiloj, ni povas konkludi ĉu ekzistas sufiĉe da RAM por la kazo de MS SQL Server.
Metodo por detekti troan CPU-ŝarĝon
Por identigi mankon de CPU-tempo, simple uzu la sisteman vidon sys.dm_os_schedulers. Ĉi tie, se la indikilo runnable_tasks_count estas konstante pli granda ol 1, tiam estas alta probablo, ke la nombro da kernoj ne sufiĉas por la petskribo de MS SQL Server.
Por montri indikilon en monitora sistemo (ekzemple Zabbix), vi povas krei la jenan peton:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Surbaze de la dinamiko de la akiritaj valoroj por ĉi tiu indikilo, ni povas konkludi ĉu ekzistas sufiĉe da procesora tempo (nombro da CPU-kernoj) por la kazo de MS SQL Server.
Tamen, estas grave memori la fakton, ke la demandoj mem povas pridemandi plurajn fadenojn samtempe. Kaj foje la optimumigilo ne povas ĝuste taksi la kompleksecon de la demando mem. Tiam la peto povas esti asignita tro da fadenoj, kiuj en difinita tempo ne povas esti procesitaj samtempe. Kaj ĉi tio ankaŭ kaŭzas specon de atendado asociita kun manko de procesora tempo, kaj la kresko de la vosto por planistoj, kiuj uzas specifajn CPU-kernojn, tio estas, la indikilo runnable_tasks_count pliiĝos en tiaj kondiĉoj.
En ĉi tiu kazo, antaŭ ol pliigi la nombron da CPU-kernoj, vi devas ĝuste agordi la paralelecajn proprietojn de la MS SQL Server-instanco mem, kaj de versio 2016, ĝuste agordi la paralelajn ecojn de la bezonataj datumbazoj:
Ĉi tie vi devas atenti la jenajn parametrojn:
- Maksimuma Grado de Paralelismo - fiksas la maksimuman nombron da fadenoj kiuj povas esti asignitaj al ĉiu peto (la defaŭlta estas 0 - limigita nur de la operaciumo mem kaj la eldono de MS SQL Server)
- Kosta Sojlo por Paralelismo - laŭtaksa kosto de paraleleco (defaŭlte estas 5)
- Maksimuma DOP—metas la maksimuman nombron da fadenoj, kiuj povas esti asignitaj al ĉiu demando je la datumbaza nivelo (sed ne pli ol la valoro de la posedaĵo "Maksimuma Grado de Paralelismo") (defaŭlte ĝi estas 0—limigita nur de la operaciumo). mem kaj la eldono de MS SQL Server, kaj ankaŭ limigon al la posedaĵo "Maksimuma Grado de Paralelismo" de la tuta petskribo de MS SQL Server)
Ne eblas doni same bonan recepton por ĉiuj kazoj, tio estas, vi devas analizi malfacilajn demandojn.
Surbaze de mia propra sperto, mi rekomendas la sekvan algoritmon de agoj por OLTP-sistemoj por agordi paralelajn ecojn:
- unue malebligu paralelismon fiksante Maksimuma Grado de Paralelismo al 1 ĉe la nivelo de la tuta kazo
- analizu la plej pezajn demandojn kaj elektu la optimuman nombron da fadenoj por ili
- starigu la Maksimuma Grado de Paralelismo al la elektita optimuma nombro da fadenoj akiritaj de la paŝo 2, kaj ankaŭ por specifaj datumbazoj starigu la Maksimuman DOP-valoron akiritan de la paŝo 2 por ĉiu datumbazo.
- analizu la plej pezajn demandojn kaj identigu la negativan efikon de multifadenado. Se ĝi estas, tiam pliigu Kosto-Sojlon por Paralelismo.
Por sistemoj kiel ekzemple 1C, Microsoft CRM kaj Microsoft NAV, en la plej multaj kazoj malpermesi multifadenadon taŭgas
Ankaŭ, se vi havas la Norman eldonon, tiam plejofte malpermeso de plurfadenado taŭgas pro la fakto, ke ĉi tiu eldono estas limigita en la nombro da CPU-kernoj.
La algoritmo priskribita supre ne taŭgas por OLAP-sistemoj.
Surbaze de mia propra sperto, mi rekomendas la sekvan algoritmon de agoj por OLAP-sistemoj por agordi paralelajn ecojn:
- analizu la plej pezajn demandojn kaj elektu la optimuman nombron da fadenoj por ili
- starigu la Maksimuma Grado de Paralelismo al la elektita optimuma nombro da fadenoj akiritaj de la paŝo 1, kaj ankaŭ por specifaj datumbazoj starigu la Maksimuman DOP-valoron akiritan de la paŝo 1 por ĉiu datumbazo.
- analizu la plej pezajn demandojn kaj identigu la negativan efikon de limigado de samtempeco. Se ĝi estas, tiam aŭ malaltigu la Kostan Sojlon por Paralelismo-valoron, aŭ ripetu la paŝojn 1-2 de ĉi tiu algoritmo.
Tio estas, por OLTP-sistemoj ni iras de unu-fadenado al plur-fadenado, kaj por OLAP-sistemoj, male, ni iras de plur-fadenado al unu-fadenado. Tiel vi povas elekti la optimumajn paralelajn agordojn kaj por specifa datumbazo kaj por la tuta petskribo de MS SQL Server.
Ankaŭ gravas kompreni, ke agordoj de samtempaj proprietoj devas esti ŝanĝitaj laŭlonge de la tempo, surbaze de la rezultoj de monitorado de la agado de MS SQL Server.
Rekomendoj por agordi spurajn flagojn
Laŭ mia propra sperto kaj la sperto de miaj kolegoj, por optimuma rendimento, mi rekomendas agordi la jenajn spurajn flagojn ĉe la funkcinivelo de MS SQL Server por versioj 2008-2016:
- 610 - Redukti ensalutadon de enmetoj en indeksitajn tabelojn. Povas helpi kun enmetoj en tabelojn kun granda nombro da rekordoj kaj multaj transakcioj, kun oftaj longaj WRITELOG atendoj por ŝanĝoj en indeksoj
- 1117 - Se dosiero en dosiergrupo renkontas la aŭtomate-kreskan sojlon, ĉiuj dosieroj en la dosiergrupo estas kreskigitaj
- 1118 - Devigas ĉiujn objektojn situi en malsamaj ampleksoj (malpermesas miksitajn ampleksojn), kio minimumigas la bezonon skani la SGAM-paĝon, kiu estas uzata por spuri miksitajn ampleksojn.
- 1224 - Malebligas serurskaladon bazitan sur serurkalkulo. Tamen, troa uzado de memoro povas ebligi ŝlosadon
- 2371 - Ŝanĝas la fiksan sojlon de aŭtomata ĝisdatigo de statistiko al la sojlo de dinamika aŭtomata ĝisdatigo de statistiko. Grava por ĝisdatigi demandplanojn sur grandaj tabeloj kie malĝuste difini la nombron da rekordoj rezultigas erarajn ekzekutplanojn
- 3226 - Subpremas rezervajn sukcesajn mesaĝojn en la erarprotokolo
- 4199 - Inkluzivas ŝanĝojn al la konsultoptimumiganto publikigita en SQL Server ĝisdatigaj pakaĵoj kaj servaj pakoj
- 6532-6534 - Inkluzivas rendimentajn plibonigojn por demandoj kun spacaj datumtipoj
- 8048 - Konvertas NUMA-dispartigitajn memorobjektojn al CPU-dispartitaj
- 8780 - Ebligas plian tempo-asignon por konsultplanado. Iuj petoj sen ĉi tiu flago povas esti malakceptitaj ĉar ili ne havas demandplanon (tre malofta eraro)
- 8780 - 9389 - Ebligas aldonan dinamikan provizoran memorbufron por batreĝimfunkciigistoj, permesante al la batreĝimfunkciigisto peti kroman memoron kaj eviti transdoni datumojn al tempdb se kroma memoro estas havebla
Estas ankaŭ utile ebligi spurflagon 2016 antaŭ versio 2301, kiu ebligas altnivelan decidan subtenan optimumigon kaj tiel helpas elekti pli bonajn demandplanojn. Tamen, ekde versio 2016, ĝi ofte havas negativan efikon al sufiĉe longaj ĝeneralaj demandaj ekzekuttempoj.
Ankaŭ, por sistemoj kun multaj indeksoj (ekzemple, por 1C datumbazoj), mi rekomendas ebligi spurflagon 2330, kiu malŝaltas kolekton de indeksa uzado, kiu ĝenerale havas pozitivan efikon al la sistemo.
Vi povas lerni pli pri spurflagoj
De la supra ligilo, ankaŭ gravas konsideri la versiojn kaj konstruojn de MS SQL Server, ĉar por pli novaj versioj, iuj spurflagoj estas ebligitaj defaŭlte aŭ havas neniun efikon.
Vi povas ebligi aŭ malŝalti la spuron per la komandoj DBCC TRACEON kaj DBCC TRACEOFF respektive. Vidu pli da detaloj
Vi povas akiri la statuson de spurflagoj uzante la komandon DBCC TRACESTATUS:
Por ke spurflagoj estu inkluzivitaj en la aŭtomata starto de la servo MS SQL Server, vi devas iri al SQL Server Configuration Manager kaj aldoni ĉi tiujn spurflagojn per -T en la servopropraĵoj:
Rezultoj
Ĉi tiu artikolo ekzamenis kelkajn aspektojn de monitorado de MS SQL Server, kun la helpo de kiuj vi povas rapide identigi mankon de RAM kaj libera CPU-tempo, kaj ankaŭ kelkajn aliajn malpli evidentajn problemojn. La plej ofte uzataj spurflagoj estis reviziitaj.
Fontoj:
»
»
»
»
»
»
»
fonto: www.habr.com