ProHoster > Blog > Administrasi > Sababaraha aspék ngawaskeun MS SQL Server. Tungtunan pikeun Nyetel Bandéra Trace

Sababaraha aspék ngawaskeun MS SQL Server. Tungtunan pikeun Nyetel Bandéra Trace

foreword

Seringna, pangguna, pamekar sareng pangurus MS SQL Server DBMS disanghareupan ku masalah kinerja database atanapi DBMS sacara gembleng, janten ngawaskeun MS SQL Server pisan relevan.
Artikel ieu mangrupa tambahan kana artikel Ngagunakeun Zabbix mun Monitor MS SQL Server Database sarta eta bakal nalungtik sababaraha aspék mantau MS SQL Server, hususna: kumaha gancang nangtukeun mana sumberdaya nu leungit, kitu ogé saran pikeun nyetél ngambah bandéra.
Pikeun skrip di handap ieu tiasa dianggo, anjeun kedah nyiptakeun skéma inf dina pangkalan data anu dipikahoyong sapertos kieu:
Nyiptakeun skéma inf

use <имя_БД>;
go
create schema inf;

Métode pikeun ngadeteksi kurangna RAM

Indikator mimiti kakurangan RAM nyaéta nalika hiji conto MS SQL Server ngahakan sadaya RAM anu dialokasikeun ka dinya.
Jang ngalampahkeun ieu, jieun representasi inf.vRAM di handap ieu:
Nyieun pintonan 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;

Teras anjeun tiasa nangtoskeun yén conto MS SQL Server ngonsumsi sadaya mémori anu dialokasikeun kana éta nganggo pamundut ieu:

select  SQL_server_physical_memory_in_use_Mb,  SQL_server_committed_target_Mb
from [inf].[vRAM];

Lamun indikator SQL_server_physical_memory_in_use_Mb terus teu kurang ti SQL_server_committed_target_Mb, mangka anjeun kudu pariksa statistik antosan.
Pikeun nangtukeun kurangna RAM ngaliwatan statistik antosan, hayu urang nyieun view inf.vWaits:
Nyieun pintonan 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];

Dina hal ieu, anjeun tiasa nangtukeun kakurangan RAM nganggo pamundut ieu:

SELECT [Percentage]
      ,[AvgWait_S]
  FROM [inf].[vWaits]
  where [WaitType] in (
    'PAGEIOLATCH_XX',
    'RESOURCE_SEMAPHORE',
    'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Di dieu anjeun kedah nengetan indikator Perséntase sareng AvgWait_S. Upami aranjeunna signifikan dina totalitasna, aya kamungkinan anu luhur pisan yén conto MS SQL Server henteu gaduh cukup RAM. Nilai ésénsial ditangtukeun masing-masing pikeun unggal sistem. Nanging, anjeun tiasa mimitian ku indikator ieu: Persentase>=1 sareng AvgWait_S>=0.005.
Pikeun kaluaran indikator kana sistem ngawaskeun (contona, Zabbix), anjeun tiasa nyiptakeun dua patarosan ieu:

  1. Sabaraha perséntase jinis ngantosan pikeun RAM (jumlah kanggo sadaya jinis ngantosan sapertos kitu): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. sabaraha jenis antosan RAM nyandak dina milliseconds (nilai maksimum sadaya telat rata pikeun sakabéh jenis antosan): 
    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'
  );

Dumasar dinamika nilai anu dicandak pikeun dua indikator ieu, urang tiasa nyimpulkeun naha aya cukup RAM pikeun conto MS SQL Server.

Métode pikeun ngadeteksi beban CPU kaleuleuwihan

Pikeun ngaidentipikasi kurangna waktu CPU, ngan make view sistem sys.dm_os_schedulers. Di dieu, lamun indikator runnable_tasks_count terus leuwih gede ti 1, lajeng aya kamungkinan luhur yén jumlah cores teu cukup pikeun conto MS SQL Server.
Pikeun ningalikeun indikator dina sistem ngawaskeun (contona, Zabbix), anjeun tiasa nyiptakeun pamundut ieu:

select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;

Dumasar kana dinamika nilai anu diala pikeun indikator ieu, urang tiasa nyimpulkeun naha aya cukup waktos prosésor (jumlah inti CPU) pikeun conto MS SQL Server.
Sanajan kitu, hal anu penting pikeun nginget kanyataan yén queries sorangan bisa query sababaraha threads sakaligus. Sarta kadangkala optimizer nu teu bisa neuleu estimasi pajeulitna query sorangan. Teras pamenta tiasa dialokasikeun seueur teuing benang, anu dina waktos anu ditangtukeun henteu tiasa diolah sakaligus. Sarta ieu ogé ngabalukarkeun hiji tipe nungguan pakait sareng kurangna waktu processor, sarta tumuwuhna antrian pikeun schedulers anu ngagunakeun cores CPU husus, nyaeta, indikator runnable_tasks_count bakal ningkat dina kaayaan kitu.
Dina hal ieu, sateuacan ningkatkeun jumlah inti CPU, anjeun kedah leres ngonpigurasikeun sipat paralelisme tina conto MS SQL Server sorangan, sareng ti versi 2016, leres ngonpigurasikeun sipat paralelisme tina pangkalan data anu diperyogikeun:
Sababaraha aspék ngawaskeun MS SQL Server. Tungtunan pikeun Nyetel Bandéra Trace

Sababaraha aspék ngawaskeun MS SQL Server. Tungtunan pikeun Nyetel Bandéra Trace
Di dieu anjeun kedah nengetan parameter di handap ieu:

  1. Max Degree of Parallelism-nyetélkeun jumlah maksimum benang anu tiasa dialokasikeun ka unggal pamundut (standarna nyaéta 0-dugi ukur ku sistem operasi sorangan sareng édisi MS SQL Server)
  2. Ambang Biaya pikeun Paralélisme - perkiraan biaya paralélisme (standar nyaéta 5)
  3. Max DOP-nyetél jumlah maksimum threads nu bisa dialokasikeun ka unggal query di tingkat database (tapi teu leuwih ti nilai sipat "Max Degree of Parallelism") (sacara standar 0 - ngan ukur ku sistem operasi. sorangan sareng édisi MS SQL Server, ogé watesan dina harta "Max Degree of Parallelism" tina sakabéh conto MS SQL Server)

Teu mungkin pikeun masihan resep anu sami pikeun sadaya kasus, nyaéta, anjeun kedah nganalisis patarosan anu sesah.
Dumasar pangalaman kuring sorangan, kuring nyarankeun algoritma tindakan pikeun sistem OLTP pikeun ngonpigurasikeun sipat paralelisme:

  1. Pareuman heula paralelisme ku netepkeun Max Degree of Parallelism ka 1 dina tingkat sakabéh conto.
  2. nganalisis queries heaviest tur pilih jumlah optimal threads pikeun aranjeunna
  3. Nyetél Max Degree of Parallelism kana jumlah optimal benang anu dipilih tina léngkah 2, sareng ogé pikeun pangkalan data khusus, atur nilai Max DOP anu dicandak tina léngkah 2 pikeun tiap database.
  4. nganalisis queries heaviest sarta nangtukeun éfék négatif multithreading. Upami kitu, teras ningkatkeun Ambang Biaya pikeun Paralelisme.
    Pikeun sistem sapertos 1C, Microsoft CRM sareng Microsoft NAV, dina kalolobaan kasus larangan multithreading cocog.

Ogé, upami anjeun gaduh édisi Standar, maka di hal nu ilahar larangan dina multi-threading luyu alatan kanyataan yén édisi ieu diwatesan ku jumlah cores CPU.
Algoritma anu dijelaskeun di luhur henteu cocog pikeun sistem OLAP.
Dumasar pangalaman kuring sorangan, kuring nyarankeun algoritma tindakan pikeun sistem OLAP pikeun ngonpigurasikeun sipat paralelisme:

  1. nganalisis queries heaviest tur pilih jumlah optimal threads pikeun aranjeunna
  2. Nyetél Max Degree of Parallelism kana jumlah optimal benang anu dipilih tina léngkah 1, sareng ogé pikeun pangkalan data khusus, atur nilai Max DOP anu dicandak tina léngkah 1 pikeun tiap database.
  3. nganalisa patarosan anu paling beurat sareng ngaidentipikasi pangaruh négatip tina ngawatesan konkurensi. Upami kitu, teras nurunkeun Ambang Biaya pikeun nilai Paralelisme, atanapi malikan deui léngkah 1-2 tina algoritma ieu

Nyaéta, pikeun sistem OLTP urang angkat tina single-threading ka multi-threading, sareng pikeun sistem OLAP, sabalikna, urang angkat tina multi-threading ka single-threading. Ku cara ieu Anjeun bisa milih setelan paralelisme optimal boh pikeun database husus jeung sakabéh conto MS SQL Server.
Éta ogé penting pikeun ngarti yén setélan sipat concurrency kudu dirobah kana waktu, dumasar kana hasil ngawas kinerja MS SQL Server.

Rekomendasi pikeun netepkeun bandéra ngambah

Tina pangalaman kuring sorangan sareng pangalaman kolega kuring, pikeun pagelaran optimal, kuring nyarankeun netepkeun umbul-umbul di handap ieu dina tingkat ngajalankeun layanan MS SQL Server pikeun vérsi 2008-2016:

  1. 610 - Ngurangan logging of inserts kana tabel indéks. Bisa mantuan ku inserts kana tabel kalawan angka nu gede ngarupakeun rékaman jeung loba transaksi, kalawan WRITELOG sering ngantosan parobahan dina indéks.
  2. 1117 - Upami file dina grup file nyumponan ambang pertumbuhan otomatis, sadaya file dina grup file bakal ningkat.
  3. 1118 - Maksakeun sadaya obyék pikeun ayana dina lega anu béda-béda (henteu ngijinkeun legana campuran), anu ngaminimalkeun kabutuhan pikeun nyeken halaman SGAM, anu dianggo pikeun ngalacak jarak anu dicampur.
  4. 1224 - Nonaktipkeun eskalasi konci dumasar kana jumlah konci. Tapi, pamakean mémori anu kaleuleuwihan tiasa ngaktipkeun escalation konci
  5. 2371 - Ngarobah bangbarung apdet statistik otomatis tetep jadi bangbarung update statistik otomatis dinamis. Penting pikeun ngapdet rencana pamundut dina tabel ageung dimana salah nangtukeun jumlah rékaman nyababkeun rencana palaksanaan anu salah.
  6. 3226 - Suppresses pesen kasuksésan cadangan dina log kasalahan
  7. 4199 - Ngawengku parobihan kana pangoptimal pamundut anu dileupaskeun dina rollup apdet SQL Server sareng bungkus jasa
  8. 6532-6534 - Ngawengku perbaikan kinerja pikeun queries kalawan tipe data spasial
  9. 8048 - Ngarobih objék mémori anu dipartisi NUMA jadi anu dipartisi CPU
  10. 8780 - Aktipkeun alokasi waktu tambahan pikeun tata query. Sababaraha pamundut tanpa bandéra ieu tiasa ditolak sabab henteu gaduh rencana pamundut (kasalahan anu jarang pisan)
  11. 8780 - 9389 - Aktipkeun panyangga mémori samentawis dinamis tambahan pikeun operator mode angkatan, ngamungkinkeun operator mode angkatan menta mémori tambahan sareng ngahindarkeun mindahkeun data ka tempdb upami mémori tambahan sayogi.

Éta ogé kapaké pikeun ngaktipkeun trace flag 2016 sateuacan vérsi 2301, anu ngamungkinkeun optimasi dukungan kaputusan canggih sahingga ngabantosan dina milih rencana pamundut anu langkung saé. Nanging, ti saprak vérsi 2016, éta sering gaduh pangaruh négatip dina waktos palaksanaan query anu cukup panjang.
Ogé, pikeun sistem anu seueur indéks (contona, pikeun pangkalan data 1C), kuring nyarankeun ngaktifkeun bandéra trace 2330, anu nganonaktipkeun koleksi pamakean indéks, anu umumna gaduh pangaruh anu positif kana sistem.
Anjeun tiasa leuwih jéntré ngeunaan bandéra renik di dieu
Tina tautan di luhur, éta ogé penting pikeun mertimbangkeun versi sareng ngawangun MS SQL Server, sareng pikeun versi anu langkung énggal, sababaraha bandéra ngalacak diaktipkeun sacara standar atanapi henteu aya pangaruhna.
Anjeun tiasa ngaktipkeun atanapi nganonaktipkeun bandéra ngambah nganggo paréntah DBCC TRACEON sareng DBCC TRACEOFF masing-masing. Tempo leuwih rinci di dieu
Anjeun tiasa kéngingkeun status bandéra ngalacak nganggo paréntah DBCC TRACESTATUS: deui
Supados ngambah bandéra bisa kaasup dina autostart sahiji layanan MS SQL Server, Anjeun kudu indit ka SQL Server Konfigurasi Manajer tur nambahkeun ngambah bandéra ieu via -T dina sipat jasa:
Sababaraha aspék ngawaskeun MS SQL Server. Tungtunan pikeun Nyetel Bandéra Trace

hasil

Artikel ieu nalungtik sababaraha aspék mantau MS SQL Server, kalayan bantuan nu bisa gancang ngaidentipikasi kurangna RAM jeung waktu CPU bébas, kitu ogé sababaraha masalah kirang atra lianna. Bandéra jejak anu paling sering dianggo diulas.

sumber:

» SQL Server Antosan Statistik
» Statistik antosan SQL Server atanapi punten wartosan kuring dimana nyerina
» System view sys.dm_os_schedulers
» Ngagunakeun Zabbix mun Monitor MS SQL Server Database
» Gaya hirup SQL
» Ngambah bandéra
» sql.ru

sumber: www.habr.com

Tambahkeun komentar