Pambuka
Cukup asring, pangguna, pangembang lan administrator saka MS SQL Server DBMS nemokke masalah kinerja database utawa DBMS minangka kabèh, supaya MS SQL Server ngawasi banget cocog.
Artikel iki minangka tambahan kanggo artikel kasebut
Supaya skrip ing ngisor iki bisa digunakake, sampeyan kudu nggawe skema inf ing basis data sing dikarepake kaya ing ngisor iki:
Nggawe skema inf
use <ΠΈΠΌΡ_ΠΠ>;
go
create schema inf;
Cara kanggo ndeteksi lack of RAM
Indikator pisanan saka lack of RAM iku cilik nalika Kayata saka MS SQL Server mangan munggah kabeh RAM diparengake kanggo iku.
Kanggo nindakake iki, kita bakal nggawe perwakilan inf.vRAM ing ngisor iki:
Nggawe tampilan 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;
Banjur sampeyan bisa nemtokake manawa conto MS SQL Server nggunakake kabeh memori sing diparengake kanthi pitakon ing ngisor iki:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Yen SQL_server_physical_memory_in_use_Mb terus-terusan luwih gedhe tinimbang utawa padha karo SQL_server_committed_target_Mb, banjur statistik ngenteni kudu dicenthang.
Kanggo nemtokake kekurangan RAM liwat statistik Enteni, ayo nggawe tampilan inf.vWaits:
Nggawe Tampilan 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];
Ing kasus iki, sampeyan bisa nemtokake kekurangan RAM kanthi pitakon ing ngisor iki:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Ing kene sampeyan kudu menehi perhatian marang indikator Persentase lan AvgWait_S. Yen padha pinunjul ing totalitas, banjur ana kemungkinan dhuwur sing ora cukup RAM kanggo MS SQL Server Kayata. Nilai sing penting ditemtokake kanthi individu kanggo saben sistem. Nanging, sampeyan bisa miwiti karo ing ngisor iki: Persentase>=1 lan AvgWait_S>=0.005.
Kanggo ngasilake indikator menyang sistem ngawasi (contone, Zabbix), sampeyan bisa nggawe rong pitakon ing ngisor iki:
- pinten jinis tunggu sing dikuwasani dening RAM kanthi persentase (jumlah kabeh jinis ngenteni):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' );
- pirang-pirang jinis ngenteni RAM njupuk milliseconds (nilai maksimum kabeh wektu tundha rata-rata kanggo kabeh jinis tunggu kasebut):
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' );
Adhedhasar dinamika nilai sing dipikolehi kanggo rong indikator kasebut, kita bisa nyimpulake manawa ana cukup RAM kanggo conto MS SQL Server.
Metode Deteksi Kakehan CPU
Kanggo ngenali lack saka wektu prosesor, iku cukup nggunakake tampilan sistem sys.dm_os_schedulers. Ing kene, yen runnable_tasks_count terus-terusan luwih saka 1, mula ana kemungkinan gedhe yen jumlah inti ora cukup kanggo conto MS SQL Server.
Kanggo ngasilake indikator menyang sistem ngawasi (contone, Zabbix), sampeyan bisa nggawe pitakon ing ngisor iki:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Adhedhasar dinamika nilai sing dipikolehi kanggo indikator iki, kita bisa nyimpulake manawa ana cukup wektu prosesor (jumlah inti CPU) kanggo conto MS SQL Server.
Nanging, penting kanggo elinga yen panjaluk dhewe bisa njaluk macem-macem utas sekaligus. Lan kadhangkala pangoptimal ora bisa ngira kanthi bener kerumitan pitakon kasebut dhewe. Banjur panyuwunan bisa diparengake akeh banget thread sing ora bisa diproses bebarengan ing wektu tartamtu. Lan iki uga nimbulakΓ© jinis Enteni gadhah lack saka wektu prosesor, lan wutah saka antrian kanggo schedulers sing nggunakake inti CPU tartamtu, IE indikator runnable_tasks_count bakal tuwuh ing kahanan kuwi.
Ing kasus iki, sadurunge nambah jumlah intine CPU, perlu kanggo ngatur sifat paralelisme saka conto MS SQL Server dhewe, lan saka versi 2016, kanthi bener ngatur sifat paralelisme saka basis data sing dibutuhake:
Ing kene sampeyan kudu menehi perhatian marang paramèter ing ngisor iki:
- Max Degree of Parallelism - nyetel jumlah maksimum utas sing bisa dialokasikan kanggo saben panjalukan (standar 0 - diwatesi mung dening sistem operasi dhewe lan edisi MS SQL Server)
- Ambang Biaya kanggo Paralelisme - kira-kira biaya paralelisme (standar yaiku 5)
- Max DOP - nyetel jumlah maksimum Utas sing bisa diparengake kanggo saben pitakonan ing tingkat database (nanging ora luwih saka Nilai saka "Max Degree of Parallelism" property) (standar punika 0 - winates mung dening sistem operasi dhewe lan edisi MS SQL Server, uga watesan ing properti "Max Degree of Parallelism" saka kabeh conto MS SQL Server)
Ing kene ora bisa menehi resep sing padha kanggo kabeh kasus, yaiku sampeyan kudu nganalisa pitakon sing abot.
Saka pengalamanku dhewe, aku nyaranake algoritma tumindak ing ngisor iki kanggo sistem OLTP kanggo nyetel sifat paralelisme:
- mateni paralelisme pisanan kanthi nyetel Derajat Paralelisme Max-wide instance dadi 1
- nganalisa panjalukan sing paling abot lan pilih jumlah benang sing paling optimal
- nyetel Max Degree of Parallelism menyang nomer optimal benang sing dipikolehi saka langkah 2, lan kanggo database tartamtu nyetel nilai Max DOP sing dipikolehi saka langkah 2 kanggo saben database.
- njelasno panjalukan paling abot lan ngenali efek negatif saka multithreading. Yen ya, banjur tambahake Ambang Biaya kanggo Paralelisme.
Kanggo sistem kayata 1C, Microsoft CRM lan Microsoft NAV, ing umume kasus, nglarang multithreading cocok
Uga, yen ana edition Standard, banjur ing sawetara kasus, larangan multithreading cocok amarga kasunyatan sing edition iki diwatesi ing nomer intine CPU.
Kanggo sistem OLAP, algoritma sing diterangake ing ndhuwur ora cocok.
Saka pengalamanku dhewe, aku nyaranake algoritma tumindak ing ngisor iki kanggo sistem OLAP kanggo nyetel sifat paralelisme:
- nganalisa panjalukan sing paling abot lan pilih jumlah benang sing paling optimal
- nyetel Max Degree of Parallelism menyang nomer optimal benang sing dipikolehi saka langkah 1, lan kanggo database tartamtu nyetel nilai Max DOP sing dipikolehi saka langkah 1 kanggo saben database.
- njelasno pitakonan paling abot lan ngenali efek negatif saka concurrency matesi. Yen ya, banjur murahake Ambang Biaya kanggo Nilai Paralelisme, utawa baleni langkah 1-2 saka algoritma iki
Sing, kanggo sistem OLTP kita pindhah saka siji-threading kanggo multi-threading, lan kanggo OLAP-sistem, ing nalisir, kita pindhah saka multi-threading kanggo single-threading. Mangkono, sampeyan bisa milih setelan paralelisme optimal kanggo loro database tartamtu lan kabeh Kayata MS SQL Server.
Sampeyan uga penting kanggo ngerti yen setelan saka sifat paralelisme kudu diganti liwat wektu, adhedhasar asil ngawasi kinerja MS SQL Server.
Pedoman kanggo Nyetel GendΓ©ra Tilak
Saka pengalamanku dhewe lan pengalaman saka kanca-kancaku, kanggo kinerja sing optimal, aku nyaranake nyetel tandha jejak ing ngisor iki ing tingkat run layanan MS SQL Server kanggo versi 2008-2016:
- 610 - Ngurangi logging sisipan menyang tabel sing diindeks. Bisa mbantu nglebokake ing tabel kanthi akeh cathetan lan akeh transaksi, kanthi WRITELOG sing kerep ngenteni owah-owahan ing indeks
- 1117 - Yen file ing filegroup nyukupi syarat ambang autogrowth, kabeh file ing filegroup bakal tuwuh.
- 1118 - Meksa kabeh obyek dumunung ing macem-macem ombone (larangan saka ukuran campuran), sing nyilikake kabutuhan kanggo mindhai kaca SGAM, sing digunakake kanggo nglacak jembar campuran.
- 1224 - Mateni eskalasi kunci adhedhasar jumlah kunci. Nanging, panggunaan memori sing berlebihan bisa nyebabake eskalasi kunci
- 2371 - Ngganti ambang nganyari statistik otomatis tetep dadi ambang nganyari statistik otomatis dinamis. Penting kanggo nganyari rencana pitakon kanggo tabel gedhe, yen jumlah cathetan sing salah nyebabake rencana eksekusi sing salah
- 3226 - Nolak pesen sukses serep ing log kesalahan
- 4199 - Kalebu owah-owahan menyang pangoptimal pitakon sing dirilis ing CU lan Paket Layanan SQL Server
- 6532-6534 - Kalebu perbaikan kinerja kanggo operasi pitakon babagan jinis data spasial
- 8048 - Ngonversi obyek memori partisi NUMA dadi partisi CPU
- 8780 - Mbisakake alokasi wektu tambahan kanggo perencanaan pitakon. Sawetara panjaluk tanpa gendera iki bisa ditolak amarga ora duwe rencana pitakon (bug langka banget)
- 8780 - 9389 - Mbisakake buffer memori hibah dinamis tambahan kanggo statement mode kumpulan, sing ngidini operator mode kumpulan njaluk memori luwih akeh lan supaya data pindhah menyang tempdb yen kasedhiya memori luwih akeh.
Uga sadurunge 2016, migunani kanggo ngaktifake trace flag 2301, sing ngidini optimasi dhukungan keputusan sing luwih apik lan mbantu milih rencana pitakon sing luwih bener. Nanging, ing versi 2016, asring duwe efek negatif ing wektu eksekusi query sakabèhé sing cukup dawa.
Uga, kanggo sistem sing akeh indeks (contone, kanggo database 1C), aku nyaranake ngaktifake trace flag 2330, sing mateni koleksi panggunaan indeks, sing umume duwe pengaruh positif ing sistem kasebut.
Kanggo informasi luwih lengkap babagan panji tilak, ndeleng
Saka pranala ing ndhuwur, iku uga penting kanggo nimbang versi lan mbangun MS SQL Server, minangka kanggo versi anyar, sawetara tilak gendera diaktifake minangka standar utawa ora duwe efek.
Sampeyan bisa nguripake lan mateni flag tilak kanthi printah DBCC TRACEON lan DBCC TRACEOFF. Kanggo rincian liyane ndeleng
Sampeyan bisa entuk status panji tilak nggunakake printah DBCC TRACESTATUS:
Supaya panji tilak bisa dilebokake ing wiwitan otomatis layanan MS SQL Server, sampeyan kudu pindhah menyang Manajer Konfigurasi SQL Server lan nambahake panji tilak iki liwat -T ing properti layanan:
Hasil
Ing artikel iki, sawetara aspek ngawasi MS SQL Server dianalisis, kanthi bantuan sampeyan bisa kanthi cepet ngenali kekurangan RAM lan wektu CPU gratis, uga sawetara masalah liyane sing kurang jelas. GendΓ©ra jejak sing paling umum digunakake wis dideleng.
Sumber:
Β»
Β»
Β»
Β»
Β»
Β»
Β»
Source: www.habr.com