🥇Magawo ena owunikira MS SQL Server. Malangizo pakukhazikitsa mbendera

Maulosi

Nthawi zambiri, ogwiritsa ntchito, oyambitsa ndi oyang'anira a MS SQL Server DBMS amakumana ndi zovuta zogwirira ntchito pankhokwe kapena DBMS yonse, kotero kuyang'anira MS SQL Server ndikofunikira kwambiri.
Nkhaniyi ndi yowonjezera ku nkhaniyi Kugwiritsa ntchito Zabbix kuyang'anira database ya MS SQL Server ndipo iwona mbali zina zowunikira MS SQL Server, makamaka: momwe mungadziwire mwachangu zomwe zikusowa, komanso malingaliro okhazikitsa mbendera.
Kuti zolemba zotsatirazi zigwire ntchito, muyenera kupanga inf schema mu nkhokwe yomwe mukufuna motere:
Kupanga inf schema

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

Njira yodziwira kusowa kwa RAM

Chizindikiro choyamba cha kusowa kwa RAM ndi pamene chitsanzo cha MS SQL Server chimadya RAM yonse yomwe yapatsidwa.
Kuti muchite izi, pangani chithunzi chotsatira cha inf.vRAM:
Kupanga mawonekedwe a 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;

Kenako mutha kudziwa kuti gawo la MS SQL Server likugwiritsa ntchito zokumbukira zonse zomwe zaperekedwa pogwiritsa ntchito funso ili:

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

Ngati chizindikiro cha SQL_server_physical_memory_in_use_Mb sichikhala chocheperapo kuposa SQL_server_committed_target_Mb, ndiye kuti muyenera kuyang'ana ziwerengero zodikirira.
Kuti mudziwe kusowa kwa RAM kudzera mu ziwerengero zodikirira, tiyeni tipange zowonera inf.vWaits:
Kupanga mawonekedwe a 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];

Pankhaniyi, mutha kudziwa kusowa kwa RAM pogwiritsa ntchito funso ili:

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

Apa muyenera kulabadira Zizindikiro za Peresenti ndi AvgWait_S. Ngati ali ofunikira pakukwanira kwawo, ndiye kuti pali mwayi waukulu kuti chitsanzo cha MS SQL Server chilibe RAM yokwanira. Makhalidwe ofunikira amatsimikiziridwa payekhapayekha padongosolo lililonse. Komabe, mukhoza kuyamba ndi chizindikiro zotsatirazi: Peresenti>=1 ndi AvgWait_S>=0.005.
Kuti mutulutse zisonyezo pamakina owunikira (mwachitsanzo, Zabbix), mutha kupanga mafunso awiri awa:

Kodi kuchuluka kwa mitundu yodikirira RAM ndi chiyani (chiwerengero chamitundu yonse yodikirira):

select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

ndi mitundu ingati yodikirira ya RAM yomwe imatenga ma milliseconds (mtengo wokwera wa kuchedwa konse kwa mitundu yonse yodikirira):

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'
  );

Kutengera mphamvu zomwe zapezeka pazizindikiro ziwirizi, titha kunena ngati pali RAM yokwanira pamtundu wa MS SQL Server.

Njira yodziwira kuchuluka kwa CPU

Kuti mudziwe kusowa kwa nthawi ya CPU, ingogwiritsani ntchito sys.dm_os_schedulers system view. Apa, ngati runnable_tasks_count index nthawi zonse imakhala yayikulu kuposa 1, ndiye kuti pali kuthekera kwakukulu kuti kuchuluka kwa ma cores sikukwanira pa MS SQL Server.
Kuti muwonetse chizindikiro pamakina owunikira (mwachitsanzo, Zabbix), mutha kupanga zotsatirazi:

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

Kutengera mphamvu zomwe zapezedwa pa chizindikirochi, titha kunena ngati pali nthawi yokwanira ya purosesa (chiwerengero cha ma CPU cores) pamtundu wa MS SQL Server.
Komabe, ndikofunikira kukumbukira kuti mafunsowo amatha kufunsa maulusi angapo nthawi imodzi. Ndipo nthawi zina optimizer sangayerekeze molondola zovuta za funso lomwelo. Ndiye pempholo likhoza kuperekedwa ulusi wochuluka kwambiri, womwe pa nthawi yake sungathe kukonzedwa panthawi imodzi. Ndipo izi zimapangitsanso mtundu wa kuyembekezera wokhudzana ndi kusowa kwa nthawi ya purosesa, ndi kukula kwa mzere wa okonza mapulogalamu omwe amagwiritsa ntchito ma CPU enieni, ndiko kuti, runnable_tasks_count indicator idzawonjezeka muzochitika zoterezi.
Pankhaniyi, musanawonjezere kuchuluka kwa ma CPU cores, muyenera kukonza zofananira zamtundu wa MS SQL Server yokha, ndipo kuchokera mu mtundu wa 2016, sinthani moyenera mawonekedwe amtundu wazomwe zimafunikira:

Apa muyenera kulabadira magawo otsatirawa:

Max Degree of Parallelism - imayika kuchuluka kwa ulusi womwe ukhoza kuperekedwa ku pempho lililonse (zosakhazikika ndi 0-zochepa ndi makina opangira okha komanso kusindikiza kwa MS SQL Server)
Cost Threshold for Parallelism - mtengo woyerekeza wa kufanana (zosasintha ndi 5)
Max DOP - imayika kuchuluka kwa ulusi womwe ungaperekedwe ku funso lililonse pamlingo wa database (koma osaposa mtengo wa katundu wa "Max Degree of Parallelism") (mwachisawawa ndi 0-zochepa ndi opareshoni lokha ndi kusindikiza kwa MS SQL Server, komanso malire pa "Max Degree of Parallelism" katundu wa MS SQL Server yonse)

Sizingatheke kupereka njira yabwino yochitira zochitika zonse, ndiye kuti, muyenera kusanthula mafunso ovuta.
Kutengera zomwe ndakumana nazo, ndikupangira njira zotsatirazi zamakina a OLTP kuti akhazikitse mawonekedwe ofanana:

choyamba zimitsani kufanana pokhazikitsa Max Degree of Parallelism ku 1 pamlingo wa zochitika zonse
yang'anani mafunso olemera kwambiri ndikusankha ulusi woyenera kwambiri
ikani Max Degree of Parallelism ku ulusi wosankhidwa bwino womwe wasankhidwa kuchokera ku sitepe 2, komanso pamasamba enaake ikani mtengo wa Max DOP wopezedwa kuchokera pagawo 2 pa database iliyonse.
santhulani mafunso olemera kwambiri ndikuwona zotsatira zoyipa za ma multithreading. Ngati ndi choncho, onjezerani Mtengo wa Kufanana kwa Parallelism.
Kwa machitidwe monga 1C, Microsoft CRM ndi Microsoft NAV, nthawi zambiri kuletsa multithreading ndikoyenera.

Komanso, ngati muli ndi Standard edition, ndiye kuti nthawi zambiri kuletsa maulendo angapo kumakhala koyenera chifukwa chakuti kope ili ndilochepa mu chiwerengero cha CPU cores.
Ma algorithm omwe tafotokozawa siwoyenera machitidwe a OLAP.
Kutengera zomwe ndakumana nazo, ndikupangira njira zotsatirazi zamakina a OLAP kuti asinthe mawonekedwe ofanana:

yang'anani mafunso olemera kwambiri ndikusankha ulusi woyenera kwambiri
ikani Max Degree of Parallelism ku ulusi wosankhidwa bwino womwe wasankhidwa kuchokera ku sitepe 1, komanso pamasamba enaake ikani mtengo wa Max DOP wopezedwa kuchokera pagawo 1 pa database iliyonse.
santhulani mafunso olemera kwambiri ndikuwona zotsatira zoyipa zakuchepetsa kugwirizanitsa. Ngati ndi choncho, tsitsani mtengo wamtengo wapatali wa Parallelism, kapena bwerezani masitepe 1-2 a aligorivimu iyi.

Ndiko kuti, pamakina a OLTP timachoka kuchoka pamtundu umodzi kupita kuzinthu zambiri, ndipo kwa machitidwe a OLAP, m'malo mwake, timachoka kuzinthu zambiri mpaka kumodzi. Mwanjira iyi mutha kusankha zokonda zofananira zonse pankhokwe inayake komanso pamtundu wonse wa MS SQL Server.
Ndikofunikiranso kumvetsetsa kuti masinthidwe a katundu wa concurrency akuyenera kusinthidwa pakapita nthawi, kutengera zotsatira zowunika momwe MS SQL Server ikuyendera.

Malangizo pakukhazikitsa mbendera

Kuchokera pa zomwe ndakumana nazo komanso zomwe anzanga, kuti agwire bwino ntchito, ndikupangira kukhazikitsa mbendera zotsatirazi pa MS SQL Server service run level for 2008-2016:

610 - Chepetsani kudula mitengo m'magome omwe ali ndi indexed. Itha kuthandizira ndikuyika m'matebulo okhala ndi ma rekodi ambiri ndi zochitika zambiri, nthawi zambiri WRITELOG imadikirira kusintha kwama index.
1117 - Ngati fayilo yomwe ili mugulu la mafayilo ikukumana ndi zomwe zikukulirakulira, mafayilo onse mugulu la mafayilo amakula
1118 - Imakakamiza kuti zinthu zonse zizipezeka mosiyanasiyana (salola kusakanikirana), zomwe zimachepetsa kufunika kosanthula tsamba la SGAM, lomwe limagwiritsidwa ntchito kutsata milingo yosakanikirana.
1224 - Imalepheretsa kukwera kwa loko kutengera kuchuluka kwa loko. Komabe, kugwiritsa ntchito kwambiri kukumbukira kumatha kuthandizira kukweza loko
2371 - Imasintha ziwerengero zokhazikika zokhazikika kuti zikhale zosintha zokha. Zofunikira pakukonzanso mapulani amafunso pamatebulo akulu pomwe kufotokozera molakwika kuchuluka kwa zolembedwa kumabweretsa mapulani olakwika
3226 - Imakanikiza mauthenga opambana pakusunga zolakwika
4199 - Ikuphatikizanso zosintha pamafunso omwe amatulutsidwa muzosintha za SQL Server ndi mapaketi antchito
6532-6534 - Ikuphatikizanso kukonza magwiridwe antchito pamafunso okhala ndi mitundu ya data ya malo
8048 - Imasintha zinthu za kukumbukira zogawidwa ndi NUMA kukhala zogawika za CPU
8780 - Imathandizira kugawa nthawi yowonjezereka pokonzekera mafunso. Zopempha zina zopanda mbendera iyi zitha kukanidwa chifukwa zilibe dongosolo lamafunso (cholakwika chosowa kwambiri)
8780 - 9389 - Imathandizira kukumbukira kwakanthawi kochepa kwa ogwiritsira ntchito batch mode, kulola woyendetsa batch mode kupempha kukumbukira kowonjezera ndikupewa kusamutsa deta ku tempdb ngati kukumbukira kwina kulipo.

Ndizothandizanso kuthandizira kutsata mbendera 2016 isanafike 2301, zomwe zimathandizira kukhathamiritsa kwachigamulo chapamwamba motero zimathandizira posankha malingaliro abwinoko. Komabe, kuyambira mtundu wa 2016, nthawi zambiri zimakhala ndi zotsatira zoyipa pamafunso ambiri nthawi yayitali.
Komanso, pamakina omwe ali ndi ma index ambiri (mwachitsanzo, ma database a 1C), ndimalimbikitsa kuti muzitha kutsatira mbendera 2330, yomwe imalepheretsa kusonkhanitsa kagwiritsidwe ka index, komwe kamakhala ndi zotsatira zabwino padongosolo.
Mutha kudziwa zambiri za trace flags apa
Kuchokera pa ulalo womwe uli pamwambapa, ndikofunikiranso kulingalira zamitundu ndi zomanga za MS SQL Server, monga zamitundu yatsopano, mbendera zina zimayatsidwa mwachisawawa kapena zilibe kanthu.
Mutha kuloleza kapena kuletsa kutsatira mbendera pogwiritsa ntchito malamulo a DBCC TRACEON ndi DBCC TRACEOFF, motsatana. Onani zambiri apa
Mutha kupeza mawonekedwe a mbendera pogwiritsa ntchito lamulo la DBCC TRACESTATUS: zambiri
Kuti kusakatula mbendera kuphatikizidwe mu autostart ya ntchito ya MS SQL Server, muyenera kupita ku SQL Server Configuration Manager ndikuwonjezera ma trace mbendera kudzera -T muzothandizira:

Zotsatira

Nkhaniyi ikuyang'ana mbali zina za kuwunika kwa MS SQL Server, mothandizidwa ndi zomwe mungathe kuzindikira mwamsanga kusowa kwa RAM ndi nthawi ya CPU yaulere, komanso mavuto ena angapo osadziwika bwino. Ma trace flags omwe amagwiritsidwa ntchito kwambiri adawunikidwanso.

Zotsatira:

» SQL Server Wait Statistics
» SQL Server dikirani ziwerengero kapena chonde ndiuzeni komwe kumapweteka
» Kawonedwe kadongosolo sys.dm_os_schedulers
» Kugwiritsa ntchito Zabbix kuyang'anira database ya MS SQL Server
» Moyo wa SQL
» Tsatani mbendera
» sql.ru

Source: www.habr.com

Zina zowunikira MS SQL Server. Malangizo pakukhazikitsa mbendera