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
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
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
Mutha kupeza mawonekedwe a mbendera pogwiritsa ntchito lamulo la DBCC TRACESTATUS:
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:
Β»
Β»
Β»
Β»
Β»
Β»
Β»
Source: www.habr.com