ProHoster > Blog > Administrasyon an > Gen kèk aspè nan siveyans MS SQL sèvè. Gid pou mete drapo tras

Gen kèk aspè nan siveyans MS SQL sèvè. Gid pou mete drapo tras

Avètisman

Byen souvan, itilizatè, devlopè ak administratè MS SQL Server DBMS rankontre pwoblèm pèfòmans nan baz done a oswa DBMS an antye, kidonk siveyans MS SQL Server trè enpòtan.
Atik sa a se yon adisyon nan atik la Sèvi ak Zabbix pou kontwole baz done MS SQL sèvè epi li pral kouvri kèk aspè nan siveyans MS SQL sèvè, an patikilye: ki jan yo byen vit detèmine ki resous ki manke, osi byen ke rekòmandasyon pou mete drapo tras.
Pou scripts sa yo travay, ou bezwen kreye yon chema inf nan baz done a vle jan sa a:
Kreye yon chema inf

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

Metòd pou detekte mank de RAM

Premye endikatè a nan mank de RAM se ka a lè yon egzanp nan MS SQL sèvè manje moute tout RAM ki resevwa lajan pou li.
Pou fè sa, nou pral kreye reprezantasyon sa a nan inf.vRAM:
Kreye gade nan 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;

Lè sa a, ou ka detèmine ke yon egzanp nan MS SQL sèvè konsome tout memwa yo atribye ba li pa rechèch sa a:

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

Si SQL_server_physical_memory_in_use_Mb toujou pi gran pase oswa egal a SQL_server_committed_target_Mb, Lè sa a, estatistik yo ta dwe tcheke.
Pou detèmine mank RAM atravè estatistik tann, ann kreye gade inf.vWaits la:
Kreye inf.vWaits View la

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];

Nan ka sa a, ou ka detèmine mank RAM ak rechèch sa a:

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

Isit la ou bezwen peye atansyon sou endikatè yo Pousantaj ak AvgWait_S. Si yo enpòtan nan totalite yo, Lè sa a, gen yon pwobabilite trè wo ke pa gen ase RAM pou egzanp nan MS SQL sèvè. Valè enpòtan yo detèmine endividyèlman pou chak sistèm. Sepandan, ou ka kòmanse ak sa ki annapre yo: Pousantaj>=1 ak AvgWait_S>=0.005.
Pou pwodwi endikatè nan yon sistèm siveyans (pa egzanp, Zabbix), ou ka kreye de demann sa yo:

  1. konbyen kalite datant yo okipe pa RAM an pousantaj (som tout kalite datant sa yo): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. konbyen kalite datant RAM pran an milisgond (valè maksimòm tout reta mwayèn pou tout kalite datant sa yo): 
    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'
  );

Baze sou dinamik valè yo jwenn pou de endikatè sa yo, nou ka konkli si gen ase RAM pou yon egzanp MS SQL sèvè.

CPU Surcharge Deteksyon Metòd

Pou idantifye mank de tan processeur, li ase pou itilize sistèm sys.dm_os_schedulers view. Isit la, si runnable_tasks_count la toujou pi gran pase 1, Lè sa a, gen yon gwo pwobabilite pou kantite nwayo a pa ase pou egzanp MS SQL Server la.
Pou pwodwi yon endikatè nan yon sistèm siveyans (pa egzanp, Zabbix), ou ka kreye rechèch sa a:

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

Ki baze sou dinamik valè yo jwenn pou endikatè sa a, nou ka konkli si gen ase tan processeur (kantite nwayo CPU) pou yon egzanp MS SQL sèvè.
Sepandan, li enpòtan sonje lefèt ke demann tèt yo ka mande plizyè fil nan yon fwa. Epi pafwa optimiseur a pa ka kòrèkteman estime konpleksite rechèch la tèt li. Lè sa a, demann lan ka resevwa twòp fil ki pa ka trete an menm tan an nan moman yo bay la. Ak sa a lakòz tou yon kalite datant ki asosye ak yon mank de tan processeur, ak kwasans nan keu la pou pwogramasyon ki itilize nwayo CPU espesifik, sa vle di endikatè runnable_tasks_count la ap grandi nan kondisyon sa yo.
Nan ka sa a, anvan ou ogmante kantite nwayo CPU, li nesesè kòrèkteman konfigirasyon pwopriyete paralelis nan egzanp MS SQL sèvè a li menm, ak soti nan vèsyon an 2016, kòrèkteman konfigirasyon pwopriyete paralèl nan baz done yo mande yo:
Gen kèk aspè nan siveyans MS SQL sèvè. Gid pou mete drapo tras

Gen kèk aspè nan siveyans MS SQL sèvè. Gid pou mete drapo tras
Isit la ou ta dwe peye atansyon sou paramèt sa yo:

  1. Maksimòm Degre Paralelism - fikse kantite maksimòm fil ki ka asiyen nan chak demann (defo a se 0 - limite sèlman pa sistèm nan fonksyone tèt li ak edisyon MS SQL sèvè)
  2. Pri Papòt pou Paralelism - estime pri paralelism (default se 5)
  3. Max DOP - fikse kantite maksimòm fil ki ka asiyen nan chak rechèch nan nivo baz done a (men pa plis pase valè pwopriyete "Max Degre of Parallelism") (default se 0 - limite sèlman pa sistèm nan fonksyone tèt li ak edisyon MS SQL Server, osi byen ke restriksyon sou pwopriyete "Max Degre of Parallelism" nan tout egzanp MS SQL Server la)

Isit la li enposib bay yon resèt egalman bon pou tout ka, sa vle di ou bezwen analize demann lou.
Soti nan pwòp eksperyans mwen, mwen rekòmande algorithm sa a nan aksyon pou sistèm OLTP pou mete kanpe pwopriyete paralelis:

  1. premyèman enfim paralèlism lè w mete Degre Max Paralelis nan tout enstans la a 1
  2. analize demann ki pi lou yo epi chwazi pi bon kantite fil pou yo
  3. fikse Max Degre Paralelis la nan nimewo optimal chwazi fil yo jwenn nan etap 2, epi pou baz done espesifik mete valè Max DOP yo jwenn nan etap 2 pou chak baz done.
  4. analize demann ki pi lou yo epi idantifye efè negatif multithreading. Si li se, Lè sa a, ogmante Papòt la Pri pou Paralelism.
    Pou sistèm tankou 1C, Microsoft CRM ak Microsoft NAV, nan pifò ka yo, entèdi multithreading apwopriye.

Epitou, si gen yon edisyon Creole, Lè sa a, nan pifò ka yo entèdiksyon an nan multithreading se apwopriye akòz lefèt ke edisyon sa a limite nan kantite nwayo CPU.
Pou sistèm OLAP, algorithm ki dekri pi wo a pa apwopriye.
Soti nan pwòp eksperyans mwen, mwen rekòmande algorithm sa a nan aksyon pou sistèm OLAP pou mete kanpe pwopriyete paralelis:

  1. analize demann ki pi lou yo epi chwazi pi bon kantite fil pou yo
  2. fikse Max Degre Paralelis la nan nimewo optimal chwazi fil yo jwenn nan etap 1, epi pou baz done espesifik mete valè Max DOP yo jwenn nan etap 1 pou chak baz done.
  3. analize demann ki pi lou yo epi idantifye efè negatif limite konkourans. Si li se, Lè sa a, swa bese Papòt Pri a pou valè Paralelism, oswa repete etap 1-2 nan algorithm sa a.

Sa vle di, pou sistèm OLTP nou ale soti nan yon sèl-threading nan milti-threading, ak pou OLAP-sistèm, okontrè, nou ale soti nan milti-threading nan yon sèl-threading. Kidonk, ou ka chwazi paralèl la pi bon pou tou de yon baz done espesifik ak tout egzanp MS SQL sèvè.
Li enpòtan tou pou w konprann ke anviwònman yo nan pwopriyete yo paralelis bezwen chanje sou tan, ki baze sou rezilta yo nan siveyans pèfòmans nan MS SQL sèvè.

Gid pou mete drapo tras

Soti nan eksperyans pwòp mwen ak eksperyans kòlèg mwen yo, pou pèfòmans optimal, mwen rekòmande pou mete drapo tras sa yo nan nivo kouri nan sèvis MS SQL sèvè a pou vèsyon 2008-2016:

  1. 610 - Redwi anrejistreman nan foure nan tab endèks. Ka ede ak foure nan tab ak anpil dosye ak anpil tranzaksyon, ak souvan tan WRITELOG tann pou chanjman nan endèks
  2. 1117 - Si yon fichye nan yon gwoup fichye satisfè kondisyon papòt autogrowth, tout fichye nan gwoup fichye a grandi.
  3. 1118 - Fòs tout objè yo dwe lokalize nan diferan limit (entèdiksyon ekstansyon melanje), ki minimize bezwen pou eskane paj SGAM la, ki itilize pou swiv ekstansyon melanje.
  4. 1224 - Enfim eskalasyon fèmen ki baze sou kantite kadna. Sepandan, itilizasyon twòp memwa ka deklanche eskalade fèmen
  5. 2371 - Chanje papòt fiks otomatik estatistik aktyalizasyon papòt la dinamik otomatik estatistik aktyalizasyon papòt la. Enpòtan pou mete ajou plan rechèch pou gwo tab, kote yon konte kòrèk nan dosye rezilta nan plan ekzekisyon inègza.
  6. 3226 - Siprime mesaj siksè backup nan boutèy la erè
  7. 4199 - Gen chanjman nan optimisateur rechèch la pibliye nan CUs ak SQL Server Service Packs
  8. 6532-6534 - Gen ladan amelyorasyon pèfòmans pou operasyon rechèch sou kalite done espasyal
  9. 8048 - Konvèti objè memwa partisyone NUMA nan objè patisyon CPU
  10. 8780 - Pèmèt alokasyon tan adisyonèl pou planifikasyon demann. Gen kèk demann san drapo sa a ka rejte paske yo pa gen yon plan rechèch (ensèk ki ra anpil)
  11. 8780 - 9389 - Pèmèt tanpon memwa sibvansyon dinamik adisyonèl pou deklarasyon mòd pakèt, ki pèmèt operatè mòd pakèt la mande memwa adisyonèl epi evite deplase done nan tempdb si memwa adisyonèl disponib.

Epitou anvan 2016, li itil pou pèmèt tras drapo 2301, ki pèmèt optimize sipò desizyon amelyore e konsa ede nan chwazi plan rechèch ki pi kòrèk. Sepandan, apati vèsyon 2016, li souvan gen yon efè negatif sou tan egzekisyon an jeneral byen long.
Epitou, pou sistèm ki gen anpil endèks (pa egzanp, pou baz done 1C), mwen rekòmande pou pèmèt tras drapo 2330, ki enfim koleksyon itilizasyon endèks, ki jeneralman gen yon efè pozitif sou sistèm nan.
Pou plis enfòmasyon sou drapo tras, gade isit la
Soti nan lyen ki anwo a, li enpòtan tou pou konsidere vèsyon ak bati MS SQL sèvè, menm jan pou vèsyon pi nouvo yo, kèk drapo tras yo aktive pa default oswa pa gen okenn efè.
Ou ka vire drapo tras la ak koupe ak kòmandman DBCC TRACEON ak DBCC TRACEOFF, respektivman. Pou plis detay gade isit la
Ou ka jwenn estati drapo tras yo lè l sèvi avèk lòd DBCC TRACESTATUS la: plis
Nan lòd pou drapo tras yo dwe enkli nan autostart nan sèvis MS SQL sèvè a, ou dwe ale nan SQL Server Configuration Manager epi ajoute tras drapo sa yo atravè -T nan pwopriyete sèvis yo:
Gen kèk aspè nan siveyans MS SQL sèvè. Gid pou mete drapo tras

Rezilta

Nan atik sa a, kèk aspè nan siveyans MS SQL sèvè yo te analize, avèk èd nan ki ou ka byen vit idantifye mank de RAM ak tan CPU gratis, osi byen ke yon kantite lòt pwoblèm mwens evidan. Yo revize drapo tras ki pi souvan itilize yo.

Sous:

» Statistik datant SQL sèvè
» SQL Server tann estatistik oswa tanpri di m 'kote li fè mal
» Sistèm wè sys.dm_os_schedulers
» Sèvi ak Zabbix pou kontwole baz done MS SQL sèvè
» SQL Lifestyle
» Trace Flags
» sql.ru

Sous: www.habr.com

Add nouvo kòmantè