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
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
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
Iwe unogona kuwana mamiriro ekutsvaga mireza uchishandisa iyo DBCC TRACESTATUS murairo:
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:
Β»
Β»
Β»
Β»
Β»
Β»
Β»
Source: www.habr.com