🥇Mamwe maficha ekutarisisa MS SQL Server. Kurudziro yekuseta trace mireza

Foreword

Kazhinji, vashandisi, vanogadzira uye manejimendi eMS SQL Server DBMS vanosangana nematambudziko ekuita kwedhatabhesi kana iyo DBMS yakazara, saka kutarisa MS SQL Server kwakakosha.
Ichi chinyorwa chekuwedzera kuchinyorwa Kushandisa Zabbix kutarisa MS SQL Server Database uye ichaongorora zvimwe zvekutarisa MS SQL Server, kunyanya: nzira yekukurumidza kuona kuti ndezvipi zviwanikwa zvisipo, pamwe nekurudziro yekumisikidza mireza yekutevera.
Kuti magwaro anotevera ashande, iwe unofanirwa kugadzira inf schema mune inodiwa dhatabhesi sezvinotevera:
Kugadzira inf schema

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

Nzira yekuona kushomeka kwe RAM

Chiratidzo chekutanga chekushaikwa kwe RAM ndeye apo muenzaniso weMS SQL Server inodya yese RAM yakagoverwa kwairi.
Kuti uite izvi, gadzira inotevera inomiririra inf.vRAM:
Kugadzira iyo inf.vRAM maonero

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;

Ipapo iwe unogona kuona kuti chiitiko cheMS SQL Server chiri kupedza yese ndangariro yakagoverwa kwairi uchishandisa unotevera mubvunzo:

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

Kana iyo SQL_server_physical_memory_in_use_Mb chiratidzo chinogara chisiri pasi peSQL_server_committed_target_Mb, saka unofanirwa kutarisa nhamba dzekumirira.
Kuti uone kushomeka kwe RAM kuburikidza nehuwandu hwekumirira, ngatigadzire maonero inf.vWaits:
Kugadzira iyo inf.vWaits maonero

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];

Mune ino kesi, unogona kuona kushomeka kwe RAM uchishandisa unotevera mubvunzo:

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

Pano iwe unofanirwa kuterera kune iyo Percentage uye AvgWait_S zviratidzo. Kana iwo akakosha mukuzara kwawo, saka pane yakanyanya mukana wekuti MS SQL Server muenzaniso haina RAM yakakwana. Hunhu hwakakosha hunotemerwa mumwe nemumwe kune yega yega system. Nekudaro, unogona kutanga neinotevera chiratidzo: Percentage>=1 uye AvgWait_S>=0.005.
Kuburitsa zviratidzo kune yekutarisa sisitimu (semuenzaniso, Zabbix), unogona kugadzira inotevera mibvunzo miviri:

Ndeipi muzana yemhando dzekumirira dze RAM (huwandu hwemhando dzese dzekumirira dzakadai):

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

ingani maRAM ekumirira marudzi anotora mumamilliseconds (yakanyanya kukosha yeavhareji kunonoka kune ese akadai ekumirira marudzi):

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

Zvichienderana nemasimba emhando dzakawanikwa dzezviratidzo zviviri izvi, tinogona kugumisa kana paine RAM yakakwana yeMS SQL Server muenzaniso.

Nzira yekuona yakawandisa CPU mutoro

Kuti uone kushaikwa kweCPU nguva, ingoshandisa sys.dm_os_schedulers system view. Pano, kana iyo runnable_tasks_count chiratidzo ichiramba ichikura kupfuura 1, saka pane mukana wakakura wekuti nhamba yemacores haina kukwana kune MS SQL Server muenzaniso.
Kuti uratidze chiratidzo mune yekutarisa sisitimu (semuenzaniso, Zabbix), unogona kugadzira chinotevera chikumbiro:

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

Zvichienderana nemasimba emhando dzakawanikwa dzechiratidzo ichi, tinogona kugumisa kana paine yakakwana processor nguva (nhamba yeCPU cores) yeiyo MS SQL Server muenzaniso.
Nekudaro, zvakakosha kuyeuka chokwadi chekuti mibvunzo pachayo inogona kubvunza akawanda shinda panguva imwe chete. Uye dzimwe nguva iyo optimizer haigone kunyatso kufungidzira kuoma kwemubvunzo pachawo. Zvadaro chikumbiro chinogona kugoverwa tambo dzakawanda, iyo panguva yakatarwa isingagoni kugadziriswa panguva imwe chete. Uye izvi zvakare zvinokonzeresa rudzi rwekumirira rwakabatana nekushaikwa kwe processor nguva, uye kukura kwemutsara wevanoronga vanoshandisa chaiwo CPU cores, kureva, iyo runnable_tasks_count chiratidzo ichawedzera mumamiriro akadaro.
Muchiitiko ichi, usati wawedzera huwandu hweCPU cores, iwe unofanirwa kunyatso gadzirisa iyo parallelism zvivakwa zveMS SQL Server muenzaniso pachayo, uye kubva muvhezheni 2016, gadzirisa nenzira kwayo kufanana kwezvivakwa zvedhatabhesi inodiwa:

Pano iwe unofanirwa kubhadhara kune zvinotevera parameters:

Max Degree yeParallelism-inoisa huwandu hwepamusoro hwetambo dzinogona kugoverwa kune yega yega chikumbiro (iyo yakasarudzika ndeye 0-inoganhurwa chete neiyo inoshanda sisitimu pachayo uye edition yeMS SQL Server)
Cost Threshold yeParallelism - inofungidzirwa mutengo weparallelism (default ndeye 5)
Max DOP- inoseta huwandu hwakanyanya hweshinda dzinogona kupihwa kune yega mubvunzo padanho redatabase (asi isingapfuure kukosha kwe "Max Degree yeParallelism" pfuma) (nekudaro ndeye 0-inoganhurwa chete neiyo inoshanda sisitimu. pachayo uye neshanduro yeMS SQL Server, pamwe nekudzikisira pa "Max Degree yeParallelism" pfuma yeMS SQL Server yese muenzaniso)

Hazvibviri kupa yakaenzana yakanaka resipi kune ese kesi, ndiko kuti, iwe unofanirwa kuongorora yakaoma mibvunzo.
Zvichienderana neruzivo rwangu, ini ndinokurudzira inotevera algorithm yezviito zveOLTP masisitimu kugadzirisa parallelism zvivakwa:

tanga kudzima parallelism nekuisa Max Degree yeParallelism kune 1 padanho rechiitiko chese
ongorora mibvunzo inorema uye sarudza iyo yakakwana nhamba yeshinda kwavari
isa iyo Max Degree yeParallelism kune yakasarudzwa yakakwana nhamba yeshinda yakawanikwa kubva padanho 2, uye zvakare kune chaiyo dhatabhesi isa iyo Max DOP kukosha yakawanikwa kubva padanho 2 kune yega yega dhatabhesi.
ongorora mibvunzo inorema uye woona iyo yakaipa mhedzisiro yemultithreading. Kana zvirizvo, wobva wawedzera Mutengo weParallelism.
Kune masisitimu akadai se1C, Microsoft CRM uye Microsoft NAV, kazhinji kurambidza multithreading kwakakodzera.

Zvakare, kana iwe uine Standard edition, saka kazhinji kurambidzwa kweakawanda-shinda kwakakodzera nekuda kwekuti iyi edition inogumira muhuwandu hweCPU cores.
Iyo algorithm inotsanangurwa pamusoro haina kukodzera kuOLAP masisitimu.
Zvichienderana neruzivo rwangu, ini ndinokurudzira inotevera algorithm yezviito zveOLAP masisitimu kugadzirisa parallelism zvivakwa:

ongorora mibvunzo inorema uye sarudza iyo yakakwana nhamba yeshinda kwavari
isa iyo Max Degree yeParallelism kune yakasarudzwa yakakwana nhamba yeshinda yakawanikwa kubva padanho 1, uye zvakare kune chaiyo dhatabhesi isa iyo Max DOP kukosha yakawanikwa kubva padanho 1 kune yega yega dhatabhesi.
ongorora mibvunzo inorema uye uone kuipa kwekugadzirisa concurrency. Kana zvirizvo, saka kana kudzikisa Mutengo weMutengo weParallelism kukosha, kana kudzokorora nhanho 1-2 yeiyi algorithm.

Kureva kuti, kune masisitimu eOLTP tinoenda kubva ku-single-threading kuenda kune-multi-threading, uye kune OLAP masisitimu, pane zvinopesana, isu tinoenda kubva kune-multi-threading kuenda kune imwechete-shinda. Nenzira iyi iwe unogona kusarudza iyo yakakwana parallelism zvigadziriso zvese kune yakatarwa dhatabhesi uye yeiyo yese MS SQL Server muenzaniso.
Izvo zvakakoshawo kuti unzwisise kuti concurrency zvivakwa marongero anofanirwa kuchinjwa nekufamba kwenguva, zvichibva pamhedzisiro yekutarisa mashandiro eMS SQL Server.

Kurudziro yekuseta mireza yekutevera

Kubva paruzivo rwangu uye ruzivo rwevamwe vandinoshanda navo, kuitira kunyatsoita basa, ndinokurudzira kuseta mareza anotevera paMS SQL Server service run level ye2008-2016 shanduro:

610 - Deredza matanda ekuisa mumatafura ane indexed. Inogona kubatsira nekuisa mumatafura ane nhamba huru yemarekodhi uye akawanda matransaction, neanogara akareba WRITELOG inomirira shanduko muma indexes.
1117 - Kana faira riri muboka refaira richisangana ne auto-kukura chikumbaridzo, mafaera ese ari muboka refaira anokura.
1118 - Inomanikidza zvinhu zvese kuti zviwanikwe munzvimbo dzakasiyana (zvisingabvumiri zvakasanganiswa zviyero), izvo zvinoderedza kukosha kwekutarisa peji reSGAM, iro rinoshandiswa kutevedzera zvakasanganiswa zviyero.
1224 - Inodzima kukiya kukwira zvichienderana nekukiya kuverenga. Nekudaro, kunyanya kushandiswa kwendangariro kunogona kugonesa kukiya kukwira
2371 - Inoshandura iyo yakagadziriswa otomatiki nhamba yekuvandudza chikumbaridzo kune inoshanduka otomatiki nhamba yekuvandudza chikumbaridzo. Zvakakosha pakugadzirisa zvirongwa zvemubvunzo pamatafura makuru apo kutsanangura zvisizvo huwandu hwemarekodhi kunokonzeresa hurongwa hwekuuraya.
3226 - Inodzvanya mameseji ekubudirira mugiyo yekukanganisa
4199 - Inosanganisira shanduko kune yemubvunzo optimizer yakaburitswa muSQL Server yekuvandudza rollups uye sevhisi mapaketi.
6532-6534 - Inosanganisira kuvandudzwa kwekuita kwemibvunzo ine spatial data mhando
8048 - Inoshandura NUMA-yakakamurwa memory zvinhu kuita CPU-yakakamurwa
8780 - Inogonesa imwe nguva yekugovera yekuronga mibvunzo. Zvimwe zvikumbiro zvisina mureza uyu zvinogona kurambwa nekuti hazvina hurongwa hwekubvunza (chikanganiso chisingawanzoitiki)
8780 - 9389 - Inogonesa imwe inosimba yenguva pfupi yekurangarira buffer kune batch mode vanoshanda, ichibvumira batch mode opareta kukumbira imwe ndangariro uye kudzivirira kuendesa data ku tempdb kana imwe ndangariro iripo.

Izvo zvinobatsirawo kugonesa kuteedzera mureza 2016 isati yasvika vhezheni 2301, iyo inogonesa yepamusoro sarudzo yekutsigira optimization uye nekudaro inobatsira mukusarudza zvirinani zvirongwa zvemubvunzo. Nekudaro, kubvira vhezheni 2016, inowanzova nemhedzisiro yakaipa pane yakarebesa yakazara bvunzo nguva dzekuita.
Zvakare, kune masisitimu ane akawanda indexes (somuenzaniso, ye1C dhatabhesi), ini ndinokurudzira kugonesa trace mureza 2330, iyo inodzima kuunganidzwa kwekushandisa index, iyo inowanzova nemhedzisiro yakanaka pahurongwa.
Iwe unogona kudzidza zvakawanda nezve trace mireza pano
Kubva pane chinongedzo chiri pamusoro, zvakakoshawo kufunga nezve mavhezheni uye anovaka eMS SQL Server, sezvitsva shanduro, mamwe mareza mareza anogoneswa nekusarudzika kana asina mhedzisiro.
Unogona kugonesa kana kudzima mureza wekutsvaga uchishandisa DBCC TRACEON uye DBCC TRACEOFF mirairo, zvichiteerana. Ona mamwe mashoko pano
Iwe unogona kuwana mamiriro ekutsvaga mireza uchishandisa iyo DBCC TRACESTATUS murairo: mamwe mashoko
Kuti trace mireza ibatanidzwe mune autostart yeMS SQL Server sevhisi, unofanirwa kuenda kuSQL Server Configuration Manager uye wowedzera aya mareza mareza kuburikidza -T mune sevhisi zvivakwa:

Migumisiro

Ichi chinyorwa chakaongorora mamwe maitiro ekutarisa MS SQL Server, nerubatsiro rwaunogona kukurumidza kuona kushaikwa kwe RAM uye yemahara CPU nguva, pamwe nemamwe akati wandei mamwe matambudziko asina kujeka. Iwo ainyanya kushandiswa trace mireza akaongororwa.

Sources:

» SQL Server Wait Statistics
» SQL Server mirira nhamba kana ndapota ndiudze painorwadza
» System maonero sys.dm_os_schedulers
» Kushandisa Zabbix kutarisa MS SQL Server Database
» SQL Mararamiro
» Tsvaga mireza
» sql.ru

Source: www.habr.com

Zvimwe zvinhu zveMS SQL Server yekutarisa. Nhungamiro yekuseta Trace Flags