Isibikezelo
Imvamisa, abasebenzisi, abathuthukisi nabaphathi be-MS SQL Server DBMS babhekana nezinkinga zokusebenza kwesizindalwazi noma i-DBMS iyonke, ngakho ukuqapha i-MS SQL Server kubaluleke kakhulu.
Lesi sihloko siyisengezo esihlokweni
Ukuze imibhalo elandelayo isebenze, udinga ukwakha i-schema ye-inf kusizindalwazi oyifunayo ngendlela elandelayo:
Ukudala i-schema ye-inf
use <ΠΈΠΌΡ_ΠΠ>;
go
create schema inf;
Indlela yokuthola ukuntuleka kwe-RAM
Inkomba yokuqala yokuntuleka kwe-RAM yilapho isibonelo se-MS SQL Server idla yonke i-RAM eyabelwe yona.
Ukuze wenze lokhu, dala isethulo esilandelayo se-inf.vRAM:
Idala ukubuka kwe-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;
Khona-ke ungakwazi ukunquma ukuthi isibonelo se-MS SQL Server idla yonke inkumbulo eyabelwe yona kusetshenziswa lo mbuzo olandelayo:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Uma inkomba ye-SQL_server_physical_memory_in_use_Mb ihlala ingekho ngaphansi kuka-SQL_server_committed_target_Mb, kuzomele uhlole izibalo zokulinda.
Ukuze sinqume ukushoda kwe-RAM ngezibalo zokulinda, masidale ukubuka i-inf.vWaits:
Idala ukubuka kwe-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];
Kulokhu, unganquma ukuntuleka kwe-RAM usebenzisa lo mbuzo olandelayo:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Lapha udinga ukunaka Iphesenti nezinkomba ze-AvgWait_S. Uma zibalulekile ekupheleleni kwazo, khona-ke kukhona amathuba aphezulu kakhulu okuthi isibonelo se-MS SQL Server asinayo i-RAM eyanele. Amanani abalulekile anqunywa ngawodwana kusistimu ngayinye. Nokho, ungaqala ngenkomba elandelayo: Amaphesenti>=1 futhi AvgWait_S>=0.005.
Ukukhipha izinkomba ohlelweni lokuqapha (isibonelo, i-Zabbix), ungakha le mibuzo emibili elandelayo:
- Ayini amaphesenti ezinhlobo zokulinda ze-RAM (isamba sazo zonke izinhlobo ezinjalo zokulinda):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' );
- zingaki izinhlobo zokulinda ze-RAM ezithatha ngama-millisecond (inani eliphezulu lakho konke ukubambezeleka okumaphakathi kwazo zonke izinhlobo ezinjalo zokulinda):
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' );
Ngokusekelwe ku-dynamics yamanani atholiwe alezi zinkomba ezimbili, singaphetha ngokuthi ingabe ikhona i-RAM eyanele yesibonelo se-MS SQL Server.
Indlela yokuthola umthwalo we-CPU oweqile
Ukuze ubone ukushoda kwesikhathi se-CPU, vele usebenzise ukubuka kwesistimu ye-sys.dm_os_schedulers. Lapha, uma inkomba ye-runnable_tasks_count ihlala ingaphezu kuka-1, khona-ke kunethuba elikhulu lokuthi inani lama-cores azanele ku-MS SQL Server.
Ukuze ubonise inkomba ohlelweni lokuqapha (isibonelo, i-Zabbix), ungakha isicelo esilandelayo:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Ngokusekelwe ku-dynamics yamanani atholiwe ale nkomba, singaphetha ngokuthi ingabe sikhona yini isikhathi esanele sokucubungula (inani lama-CPU cores) sesibonelo se-MS SQL Server.
Kodwa-ke, kubalulekile ukukhumbula iqiniso lokuthi imibuzo ngokwayo ingabuza imicu eminingi ngesikhathi esisodwa. Futhi kwesinye isikhathi i-optimizer ayikwazi ukulinganisa kahle ubunkimbinkimbi bombuzo ngokwawo. Khona-ke isicelo singabelwa imicu eminingi kakhulu, okuthi ngesikhathi esithile ingakwazi ukucutshungulwa kanyekanye. Futhi lokhu kubangela uhlobo lokulinda oluhambisana nokuntuleka kwesikhathi sokucubungula, nokukhula komugqa wabahleli abasebenzisa ama-CPU cores athile, okungukuthi, inkomba ye-runnable_tasks_count izokwenyuka ezimweni ezinjalo.
Kulokhu, ngaphambi kokwandisa inani lama-CPU cores, udinga ukulungisa kahle izici zokufana zesibonelo se-MS SQL Server uqobo, futhi kusukela kunguqulo 2016, ulungiselele kahle izakhiwo zokufana zolwazi oludingekayo:
Lapha kufanele unake amapharamitha alandelayo:
- I-Max Degree of Parallelismβisetha inani eliphezulu lemicu enganikezwa esicelweni ngasinye (okuzenzakalelayo kungu-0βkukhawulwe isistimu yokusebenza ngokwayo kanye nohlelo lwe-MS SQL Server)
- I-Cost Threshold for Parallelism - izindleko ezilinganiselwe zokuhambisana (okuzenzakalelayo ngu-5)
- I-Max DOPβisetha inani eliphezulu lemicu engabelwe umbuzo ngamunye ezingeni lesizindalwazi (kodwa hhayi ngaphezu kwevelu yendawo ethi βMax Degree of Parallelismβ) (ngokuzenzakalelayo ingu-0βinqunyelwe isistimu yokusebenza kuphela ngokwayo kanye nohlelo lwe-MS SQL Server, kanye nomkhawulo endaweni ethi βMax Degree of Parallelismβ yaso sonke isibonelo se-MS SQL Server)
Akunakwenzeka ukunikeza iresiphi enhle ngokulinganayo kuwo wonke amacala, okungukuthi, udinga ukuhlaziya imibuzo enzima.
Ngokusekelwe kokuhlangenwe nakho kwami, ngincoma i-algorithm elandelayo yezenzo zezinhlelo ze-OLTP ukuze zilungiselele izakhiwo zokuhambisana:
- qala ukhubaze ukufana ngokumisa i-Max Degree of Parallelism ibe ngu-1 ezingeni laso sonke isenzakalo
- hlaziya imibuzo enzima kakhulu bese ukhetha inani elifanele lemicu yazo
- setha i-Max Degree of Parallelism enanini eliphezulu elikhethiwe lemicu etholwe esinyathelweni sesi-2, futhi kusizindalwazi esithile setha inani le-Max DOP elitholwe esinyathelweni sesi-2 kusizindalwazi ngasinye.
- hlaziya imibuzo enzima kakhulu futhi uhlonze umthelela ongemuhle wokufunda okuningiliziwe. Uma kunjalo, khulisa i-Cost Threshold for Parallelism.
Kuzinhlelo ezifana ne-1C, i-Microsoft CRM ne-Microsoft NAV, ezikhathini eziningi ukuvimbela ukufunda okuningi kufanelekile.
Futhi, uma une-Standard edition, lapho-ke ezimeni eziningi ukuvinjelwa kochungechunge oluningi kufanelekile ngenxa yokuthi lolu hlobo lukhawulelwe enanini lama-CPU cores.
I-algorithm echazwe ngenhla ayiwafanele amasistimu e-OLAP.
Ngokusekelwe kokuhlangenwe nakho kwami, ngincoma i-algorithm elandelayo yezenzo zezinhlelo ze-OLAP ukuze zilungiselele izakhiwo zokuhambisana:
- hlaziya imibuzo enzima kakhulu bese ukhetha inani elifanele lemicu yazo
- setha i-Max Degree of Parallelism enanini eliphezulu elikhethiwe lemicu etholwe esinyathelweni sesi-1, futhi kusizindalwazi esithile setha inani le-Max DOP elitholwe esinyathelweni sesi-1 kusizindalwazi ngasinye.
- hlaziya imibuzo enzima kakhulu futhi uhlonze umthelela ongemuhle wokukhawulela ukuhambisana kwemali. Uma kunjalo, yehlisa i-Cost Threshold ye-Parallelism value, noma phinda izinyathelo 1-2 zale algorithm.
Okusho ukuthi, kumasistimu e-OLTP sisuka ekuxhumeni okukodwa siye ekucushweni okuningi, futhi kumasistimu e-OLAP, ngokuphambene nalokho, sisuka ekucushweni okuningi siye kokukodwa. Ngale ndlela ungakhetha izilungiselelo ezifanele zokufana kokubili kusizindalwazi esithile kanye naso sonke isibonelo se-MS SQL Server.
Kubalulekile futhi ukuqonda ukuthi izilungiselelo zezakhiwo ezihambisanayo zidinga ukushintshwa ngokuhamba kwesikhathi, ngokusekelwe emiphumeleni yokuqapha ukusebenza kwe-MS SQL Server.
Izincomo zokusetha amafulegi okulandelela
Kusukela kokuhlangenwe nakho kwami ββkanye nolwazi lozakwethu, ukuze ngisebenze kahle, ngincoma ukusetha amafulegi alandelayo ku-MS SQL Server run level level yezinguqulo zika-2008-2016:
- 610 - Nciphisa ukungena kokufakiwe kumathebula anezikhombo. Ingasiza ngokufaka amathebula ngenani elikhulu lamarekhodi kanye nokwenziwe okuningi, okuvame ukulinda i-WRITELOG ende ukuze kube noshintsho ezinkombeni
- 1117 - Uma ifayela eliseqenjini lefayela lihlangabezana nomkhawulo wokukhula okuzenzakalelayo, wonke amafayela kuqembu lamafayela ayakhuliswa
- 1118 - Iphoqa zonke izinto ukuthi zibekwe ngezilinganiso ezihlukene (ayivumeli ububanzi obuxubile), okunciphisa isidingo sokuskena ikhasi le-SGAM, elisetshenziselwa ukulandelela ububanzi obuxubile.
- 1224 - Ikhubaza ukukhuphuka kokukhiya okusekelwe ekubalweni kokukhiya. Nokho, ukusetshenziswa kwenkumbulo ngokweqile kunganika amandla ukukhuphuka kokukhiya
- 2371 - Ishintsha umkhawulo wokubuyekeza izibalo ezizenzakalelayo ongashintshi ukuze ube umkhawulo wokubuyekeza izibalo ezizenzakalelayo. Kubalulekile ekubuyekezeni amapulani emibuzo kumathebula amakhulu lapho ukuchaza ngokungalungile inani lamarekhodi kuphumela ezinhlelweni zokwenziwa eziyiphutha
- 3226 - Icindezela imilayezo yempumelelo yesipele kulogi yephutha
- 4199 - Ihlanganisa izinguquko ku-optimizer yemibuzo ekhishwe ku-SQL Server update rollups namaphakethe wesevisi
- 6532-6534 - Ifaka ukuthuthukiswa kokusebenza kwemibuzo enezinhlobo zedatha yendawo
- 8048 - Iguqula izinto zememori ehlukaniswe i-NUMA zibe ezihlukaniswe nge-CPU
- 8780 - Inika amandla isabelo sesikhathi esengeziwe sokuhlela imibuzo. Ezinye izicelo ezingenalo leli fulegi zinganqatshwa ngoba azinalo uhlelo lombuzo (iphutha elingandile kakhulu)
- 8780 - 9389 - Inika amandla isilondolozi sememori yesikhashana esingeziwe esishintshashintshayo sama-opharetha emodi ye-batch, ivumela u-opharetha wemodi ye-batch ukuthi acele inkumbulo eyengeziwe futhi agweme ukudlulisa idatha ku-temdb uma imemori eyengeziwe itholakala.
Kuyasiza futhi ukunika amandla ifulegi lokulandela umkhondo 2016 ngaphambi kwenguqulo 2301, okuvumela ukwenziwa ngcono kokusekela izinqumo futhi ngaleyo ndlela kusize ekukhetheni izinhlelo ezingcono zemibuzo. Kodwa-ke, kusukela enguqulweni ka-2016, ivamise ukuba nomthelela omubi ezikhathini ezinde kakhulu zokwenza imibuzo.
Futhi, kumasistimu anezinkomba eziningi (isibonelo, kusizindalwazi se-1C), ngincoma ukunika amandla ifulegi lokulandelela 2330, elikhubaza ukuqoqwa kokusetshenziswa kwenkomba, ngokuvamile okuba nomthelela omuhle kusistimu.
Ungafunda kabanzi mayelana namafulegi okulandelela
Kusukela kusixhumanisi esingenhla, kubalulekile futhi ukucabangela izinguqulo nokwakhiwa kwe-MS SQL Server, njengezinguqulo ezintsha, amanye amafulegi okulandelela anikwe amandla ngokuzenzakalela noma awanawo umthelela.
Unganika amandla noma ukhubaze ifulegi lokulandelela usebenzisa imiyalo ye-DBCC TRACEON kanye ne-DBCC TRACEOFF, ngokulandelanayo. Bona imininingwane eyengeziwe
Ungathola isimo sokulandela umkhondo wamafulegi usebenzisa umyalo we-DBCC TRACESTATUS:
Ukuze ukulandelela amafulegi kufakwe ekuqaliseni ngokuzenzakalela kwesevisi ye-MS SQL Server, udinga ukuya ku-SQL Server Configuration Manager futhi wengeze lawa mafulegi okulandelela ngokusebenzisa -T kuzakhiwo zesevisi:
Imiphumela
Lesi sihloko sihlolisise izici ezithile zokuqapha i-MS SQL Server, ngosizo ongakwazi ukuhlonza ngokushesha ukushoda kwe-RAM nesikhathi samahhala se-CPU, kanye nenani lezinye izinkinga ezingacacile. Amafulegi okulandelela asetshenziswa kakhulu abuyekeziwe.
Imithombo:
Β»
Β»
Β»
Β»
Β»
Β»
Β»
Source: www.habr.com