Quidam aspectus MS SQL Servo vigilantia. DIRECTORIA PRINCIPIA Ponentes Vestigium vexillum

praefatio

Saepius, utentes, tincidunt et administratores MS SQL Server DBMS occurrant problemata datorum vel DBMS totius, ita MS SQL Servo vigilantia valde pertinet.
Hic articulus est additamentum ad articulum Zabbix utens ad Monitor MS SQL Servo Database et aliquas facies vigilantia MS SQL Servo, in particulari: quomodo cito determinet quae facultates desunt, necnon commendationes ad vexilla collocanda.
Ad sequentia scripta ad operandum, schema inf in desiderato database hoc modo creare debes:
Schema inf creando

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

Modum deprehendendi indigentiam RAM

Primum signum inopiae RAM evenit cum instantia MS SQL Servo omnem RAM partita exedit.
Ad hoc, inf.vRAM sequentem repraesentationem creabimus;
Creando inf.vRAM visum

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;

Tunc determinare potes quod instantia MS SQL Servo omnem memoriam ab hac interrogatione partita sumat:

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

Si SQL_server_physical_memory_in_use_Mb constanter maior est quam vel aequalis SQL_server_committed_target_Mb, tunc mora statistica comprimenda est.
Determinare defectum RAM per insidias mutant, creare inf.vWaits sententiam:
Creando inf.vWaits View

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

Hoc in casu, defectum RAM facere potes cum interrogatione sequenti:

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

Hic debes indicibus CENTESIMA et AvgWait_S attendere. Si in sua universitate significantes, tunc probabilitas altissima est non satis RAM pro instantia MS SQL Server. Valores significantes singuli pro unaquaque systemate determinantur. Sed haec inire potes: CENTESIMA>=1 et AvgWait_S>=0.005.
Indices ad rationem vigilantiam (exempli gratia Zabbix) outputare potes duas sequentes interrogationes creare:

1. quot species exspectationum in cento ariete occupantur (summa omnium talium generum exspectationum);

   select coalesce(sum([Percentage]), 0.00) as [Percentage]
   from [inf].[vWaits]
          where [WaitType] in (
              'PAGEIOLATCH_XX',
              'RESOURCE_SEMAPHORE',
               'RESOURCE_SEMAPHORE_QUERY_COMPILE'
     );
   

2. quot RAM insidias genera capiunt in milliseconds (maximam vim omnium mediocris morarum omnium talium exspectationum genera);

   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'
     );
   

Ex dynamicis valorum consecutorum duobus his indicibus concludere possumus utrum satis sit RAM pro exemplo MS SQL Server.

CPU ONERO Deprehensio Methodo

Ad cognoscendum defectum processus temporis, satis est utendi ss.dm_os_schedulers systematis intuitu. Hic, si runnable_tasks_count plus quam 1 constanter est, tunc probabile est magnum numerum metretarum non sufficere pro instantia MS SQL Server.
Ad denotatum systematis vigilantiae (exempli gratia Zabbix), potes hanc interrogationem creare:

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

Ex dynamicis valorum huius indicatoris consecuti, concludere possumus utrum tempus sufficiat processus (numerus CPU nucleorum) pro instantia MS SQL Server.
Illud tamen memorare interest quod petitiones ipsae plures fila statim petere possunt. Interdum optimizer non recte aestimare potest multiplicitatem ipsius interrogationis. Tum petitio nimis multa stamina collocari potest quae dato tempore discurrere non possunt. Et hoc etiam facit genus insidiae cum defectu processoris temporis, et incrementum queue pro schedulis utentibus certis CPU nucleis, i.e. indicatorum currentium operum in talibus conditionibus crescet.
In hoc casu, antequam numerus CPU coros augeatur, necesse est ut proprietates ipsius instantiae MS SQL Server parallelismi, et ex versione MMXVI, recte conforment proprietates parallelismi databases requisiti recte configurare;



Hic attende debes parametros sequentes:

1. Max Gradus Parallelismi - maximum numerum staminum ponit, qui ad singulas preces collocari potest (defaltus est 0 - limitatus solum ab ipsa systemate operante et editione MS SQL Server)
2. Pretium Limen parallelismi - aestimatur sumptus parallelismi (per annum 5 est)
3. Max DOP - numerus maximus relatorum qui cuilibet interrogationi in gradu datorum collocari potest (sed non plus quam valor "max Gradus Parallelismi" proprietatis (defectus est 0 - limitata solum ab ipsa systemate operante ac ed. MS SQL Servo, necnon restrictio "Max Gradus Parallelismi" totius instantiae MS SQL Server)

Hic non potest dare aequale bonum pro omnibus casibus, i.e., debes quaestionem gravem resolvere.
Ex mea experientia commendo sequens algorithmum actionum pro systematibus OLTP ad parallelismum proprietatum constituendum;

1. Primum inactivare parallelismus ponendo instantia lata Max Gradus Parallelismi ad 1
2. gravissimas petitiones resolvere et eligere meliorem numerum staminum pro eis
3. Max Gradum Parallelismi ad delectum numerum filorum ex gradu 2 impetratorum meliorem constitue, et pro certis databases pone valorem Max DOP a gradu 2 pro singulis datorum
4. gravissimas petitiones resolvere et negativam multiplicationis effectum cognoscere. Si est, sumptus limen pro parallelismo auge.
   Ad systemata ut 1C, Microsoft CRM et Microsoft NAV, in pluribus, vetans multithreading apta est.

Item, si vulgata est editio, in pluribus vetitum multiplicandi convenit, quod haec editio in numero CPU nucleorum finitur.
Ad systemata OLAP, algorithmus de quo supra dictum est, idoneum non est.
Ex mea experientia commendo sequens algorithmum actionum pro systematibus OLAP ad parallelismum proprietatum constituendum;

1. gravissimas petitiones resolvere et eligere meliorem numerum staminum pro eis
2. Max Gradum Parallelismi ad delectum numerum filorum ex gradu 1 impetratorum meliorem constitue, et pro certis databases pone valorem Max DOP a gradu 1 pro singulis datorum
3. resolvere inquisitiones gravissimas et cognoscere effectum negativum limitandi concurrentiae. Si est, vel limen pro valore parallelismo demitte, vel vestigia 1-2 huius algorithmi repete.

Hoc est, pro OLTP systemata ab uno ad multi- plicando itur, et pro OLAP systemata, contra, ex multi- plicando ad singulas stadia imus. Sic eligere potes optimam parallelismum uncinis tam datorum specificorum quam totius instantiae MS SQL Server.
Gravis etiam est intelligere quod occasus proprietatum parallelismi tempore mutari oportet, secundum eventus vigilantiae observantiae MS SQL Server.

DIRECTORIA PRINCIPIA Ponentes Vestigium vexillum

Ex mea propria experientia et experientia collegarum meorum, ad optimalem observantiam, commendo signum sequentia vexilla in procursu graduum MS SQL Servo servitii pro versionibus 2008-2016:

1. 610 - Reducitur colligationem insertorum in tabulis notatis. Adiuvare potest cum insertis tabulis cum multis monumentis et multis transactionibus, cum crebris diuturnis WRITELOG mutationes in indicibus observat.
2. 1117 - Si fasciculus in globulo glomeratus limen autogrowth occurrit, omnes fasciculi in globuli glomerantur
3. 1118 - Copiae omnes obiectae in diversis extensionibus collocandae (prohibitionis extensionum mixtarum), quae minimizat necessitatem paginae SGAM scandendi, quae extensiones mixtas vestigare consuevit.
4. 1224 - PROPAGATIO crinem inactivare secundum numerum cincinnorum. Sed superfluus usus memoriae felis clausum propagationis potest
5. 2371 - Mutationes statisticae statisticae fixae ad limen renovationis dynamicae mutant automatico limine renovationis. Gravis est adaequationis interrogationi consilia de magnis tabulis, in quibus incor- datus historiarum eventus in erroneis consiliis exsecutioni mandandis
6. 3226 - Reprimit tergum victoria epistulae in errore log
7. 4199 - Includes mutationes interrogationis optimizeris in Cus et SQL Server Service Packs
8. 6532-6534 - Emendationes perficiendas includit pro inquisitione operationes in data speciebus localibus
9. 8048 - Conversorum NUMA obiecta memoriae partita ad CPU partita
10. 8780 - Dat tempus additicium destinationis pro interrogatione consilio. Quaedam petitiones sine hoc vexillo reici possunt quia consilium quaesitum non habent (bug admodum rara)
11. 8780 - 9389 - Additional dat dynamica dat memoriam quiddam ad batch modum enuntiationum, quae batch modus operantis permittit ut plus memoriam petat et notitias mobiles ad tempdb vitet, si memoria plus suppetat.

Etiam ante MMXVI, utile est vexillum 2016 vestigare, quod decisionem optimizationum adiuvat auctam adiuvat et sic adiuvat ad consilia interrogationis rectiora eligenda. Nihilominus, sicut versionis 2301, saepe effectum negativum habet in altiore interrogatione temporis exsecutionis satis.
Etiam, systemata cum multis indicibus (exempli gratia, pro 1C databases), suadeo ut vexillum 2330 vestigium, quod collectionem index usuum, qui plerumque in systematis effectum positivum habet, disables.
Plura de vexillis vestigii vide hic
Ex nexu superiore, etiam magni momenti est videre versiones et aedificationes MS SQL Servo, sicut in recentioribus versionibus, quaedam vexilla vestigium per defaltam vel nullum effectum habent.
Vexillum vestigium vertere potes cum DBCC TRACEON et DBCC TRACEOFF mandata, respective. For more details see hic
Status vestigii vexilla licebit utendo imperio DBCC TRACESTATUS: magis
Ut vexilla vestigii in autostart servitii MS SQL servientis includantur, oportet te ad Procurator SQL Servo Configurationis accedere et vexilla per -T vestigium addere in proprietatibus servitii:

results

In hoc articulo nonnullae rationes vigilantiae MS SQL Servo evolutae sunt, quarum ope cito RAM et temporis CPU inopiam cognoscere potes, ac nonnullae aliae difficultates minus perspicuae. Vestigium vexillorum vulgatis adhibitum est recognitum.

fontes:

» SQL Servo insidias Statistics
» SQL Servo insidias mutant vel indicare mihi placet ubi dolet
» Systema visum sys.dm_os_schedulers
» Zabbix utens ad Monitor MS SQL Servo Database
» SQL Lifestyle
» Trace vexillum
» sql.ru

Source: www.habr.com