ProHoster > Блог > Administrasi > Sawetara aspèk ngawasi MS SQL Server. Pedoman kanggo Nyetel Gendéra Tilak

Sawetara aspèk ngawasi MS SQL Server. Pedoman kanggo Nyetel Gendéra Tilak

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 Nggunakake Zabbix kanggo ngawasi MS SQL Server Database lan bakal nutupi sawetara aspèk ngawasi MS SQL Server, utamané: carane cepet nemtokake sumber daya sing ilang, uga Rekomendasi kanggo nyetel panji tilak.
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:

  1. 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'
  );
  2. 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:
Sawetara aspèk ngawasi MS SQL Server. Pedoman kanggo Nyetel Gendéra Tilak

Sawetara aspèk ngawasi MS SQL Server. Pedoman kanggo Nyetel Gendéra Tilak
Ing kene sampeyan kudu menehi perhatian marang paramèter ing ngisor iki:

  1. 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)
  2. Ambang Biaya kanggo Paralelisme - kira-kira biaya paralelisme (standar yaiku 5)
  3. 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:

  1. mateni paralelisme pisanan kanthi nyetel Derajat Paralelisme Max-wide instance dadi 1
  2. nganalisa panjalukan sing paling abot lan pilih jumlah benang sing paling optimal
  3. 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.
  4. 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:

  1. nganalisa panjalukan sing paling abot lan pilih jumlah benang sing paling optimal
  2. 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.
  3. 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:

  1. 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
  2. 1117 - Yen file ing filegroup nyukupi syarat ambang autogrowth, kabeh file ing filegroup bakal tuwuh.
  3. 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.
  4. 1224 - Mateni eskalasi kunci adhedhasar jumlah kunci. Nanging, panggunaan memori sing berlebihan bisa nyebabake eskalasi kunci
  5. 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
  6. 3226 - Nolak pesen sukses serep ing log kesalahan
  7. 4199 - Kalebu owah-owahan menyang pangoptimal pitakon sing dirilis ing CU lan Paket Layanan SQL Server
  8. 6532-6534 - Kalebu perbaikan kinerja kanggo operasi pitakon babagan jinis data spasial
  9. 8048 - Ngonversi obyek memori partisi NUMA dadi partisi CPU
  10. 8780 - Mbisakake alokasi wektu tambahan kanggo perencanaan pitakon. Sawetara panjaluk tanpa gendera iki bisa ditolak amarga ora duwe rencana pitakon (bug langka banget)
  11. 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 kene
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 kene
Sampeyan bisa entuk status panji tilak nggunakake printah DBCC TRACESTATUS: luwih akeh info
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:
Sawetara aspèk ngawasi MS SQL Server. Pedoman kanggo Nyetel Gendéra Tilak

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:

» Statistik tunggu SQL Server
» SQL Server ngenteni statistik utawa tulung kula ngendi iku lara
» Tampilan sistem sys.dm_os_schedulers
» Nggunakake Zabbix kanggo ngawasi MS SQL Server Database
» Gaya urip SQL
» Jejak Gendéra
» sql.ru

Source: www.habr.com

Add a comment