🥇برخی از جنبه های نظارت بر MS SQL Server. دستورالعمل برای تنظیم پرچم های ردیابی

پیش گفتار

اغلب، کاربران، توسعه دهندگان و مدیران DBMS MS SQL Server با مشکلات عملکرد پایگاه داده یا DBMS به طور کلی مواجه می شوند، بنابراین نظارت بر MS SQL Server بسیار مرتبط است.
این مقاله افزودنی به مقاله است استفاده از Zabbix برای نظارت بر پایگاه داده MS SQL Server و برخی از جنبه های نظارت بر MS SQL Server را مورد بحث قرار خواهد داد، به ویژه: چگونگی تعیین سریع منابع از دست رفته، و همچنین توصیه هایی برای تنظیم پرچم های ردیابی.
برای اینکه اسکریپت های زیر کار کنند، باید یک طرح inf در پایگاه داده مورد نظر به صورت زیر ایجاد کنید:
ایجاد یک طرحواره inf

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

روشی برای تشخیص کمبود رم

اولین شاخص کمبود RAM زمانی است که یک نمونه از MS SQL Server تمام RAM اختصاص داده شده به آن را می خورد.
برای انجام این کار، نمایش زیر را از inf.vRAM ایجاد می کنیم:
ایجاد نمای 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;

سپس می توانید تعیین کنید که یک نمونه از MS SQL Server تمام حافظه اختصاص داده شده به آن را با کوئری زیر مصرف می کند:

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

اگر SQL_server_physical_memory_in_use_Mb به طور مداوم بزرگتر یا مساوی SQL_server_committed_target_Mb باشد، آمار انتظار باید بررسی شود.
برای تعیین کمبود RAM از طریق آمار انتظار، اجازه دهید نمای inf.vWaits را ایجاد کنیم:
ایجاد نمای 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];

در این صورت می توانید کمبود RAM را با کوئری زیر مشخص کنید:

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

در اینجا باید به اندیکاتورهای Percentage و AvgWait_S توجه کنید. اگر در مجموع آنها قابل توجه باشند، پس احتمال بسیار زیادی وجود دارد که RAM کافی برای نمونه MS SQL Server وجود ندارد. مقادیر قابل توجهی به صورت جداگانه برای هر سیستم تعیین می شود. با این حال، می توانید با موارد زیر شروع کنید: Percentage>=1 و AvgWait_S>=0.005.
برای خروجی نشانگرها به یک سیستم مانیتورینگ (به عنوان مثال Zabbix)، می توانید دو عبارت زیر را ایجاد کنید:

چند نوع انتظار توسط رم اشغال شده است (مجموع تمام انواع انتظار):

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

چه تعداد از انواع انتظار RAM در میلی ثانیه طول می کشد (حداکثر مقدار تمام تاخیرهای متوسط برای همه انواع انتظار):

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

بر اساس پویایی مقادیر به‌دست‌آمده برای این دو شاخص، می‌توان نتیجه گرفت که آیا RAM کافی برای یک نمونه از MS SQL Server وجود دارد یا خیر.

روش تشخیص اضافه بار CPU

برای شناسایی کمبود زمان پردازنده کافی است از نمای سیستم sys.dm_os_schedulers استفاده کنید. در اینجا، اگر runnable_tasks_count دائماً بیشتر از 1 باشد، احتمال زیادی وجود دارد که تعداد هسته‌ها برای نمونه MS SQL Server کافی نباشد.
برای خروجی نشانگر به یک سیستم مانیتورینگ (به عنوان مثال Zabbix)، می توانید پرس و جو زیر را ایجاد کنید:

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

بر اساس پویایی مقادیر به‌دست‌آمده برای این شاخص، می‌توان نتیجه گرفت که آیا زمان پردازنده (تعداد هسته‌های CPU) برای نمونه‌ای از MS SQL Server کافی است یا خیر.
با این حال، مهم است که این واقعیت را در نظر داشته باشید که خود درخواست ها می توانند چندین رشته را به طور همزمان درخواست کنند. و گاهی اوقات بهینه ساز نمی تواند به درستی پیچیدگی پرس و جو را تخمین بزند. سپس ممکن است به درخواست رشته های بسیار زیادی اختصاص داده شود که در زمان معین نمی توانند همزمان پردازش شوند. و این همچنین باعث نوعی انتظار مرتبط با کمبود زمان پردازنده و رشد صف برای زمانبندی هایی می شود که از هسته های CPU خاصی استفاده می کنند، به عنوان مثال، نشانگر runnable_tasks_count در چنین شرایطی رشد می کند.
در این حالت، قبل از افزایش تعداد هسته‌های CPU، لازم است که ویژگی‌های موازی خود نمونه MS SQL Server را به درستی پیکربندی کنید و از نسخه 2016، ویژگی‌های موازی پایگاه‌های داده مورد نیاز را به درستی پیکربندی کنید:

در اینجا باید به پارامترهای زیر توجه کنید:

حداکثر درجه موازی - حداکثر تعداد رشته هایی را که می توان به هر درخواست اختصاص داد را تنظیم می کند (پیش فرض 0 است - فقط توسط خود سیستم عامل و نسخه MS SQL Server محدود شده است)
آستانه هزینه برای موازی سازی - هزینه تخمینی موازی سازی (پیش فرض 5 است)
Max DOP - حداکثر تعداد رشته هایی را که می توان به هر پرس و جو در سطح پایگاه داده تخصیص داد (اما نه بیشتر از مقدار ویژگی "Max Degree of Parallelism") تنظیم می کند (پیش فرض 0 است - فقط توسط خود سیستم عامل محدود می شود و نسخه MS SQL Server، و همچنین محدودیت در ویژگی "Max Degree of Parallelism" کل نمونه MS SQL Server)

در اینجا نمی توان یک دستور العمل به همان اندازه خوب برای همه موارد ارائه داد، یعنی شما باید پرس و جوهای سنگین را تجزیه و تحلیل کنید.
از تجربه خودم، الگوریتم اقدامات زیر را برای سیستم های OLTP برای تنظیم ویژگی های موازی توصیه می کنم:

ابتدا موازی سازی را با تنظیم حداکثر درجه موازی در سطح نمونه روی 1 غیرفعال کنید.
سنگین ترین درخواست ها را تجزیه و تحلیل کنید و تعداد بهینه موضوعات را برای آنها انتخاب کنید
حداکثر درجه موازی سازی را روی تعداد بهینه نخ های انتخاب شده از مرحله 2 تنظیم کنید و برای پایگاه های داده خاص مقدار Max DOP را که از مرحله 2 به دست آمده برای هر پایگاه داده تنظیم کنید.
سنگین ترین درخواست ها را تجزیه و تحلیل کنید و تأثیر منفی چند رشته ای را شناسایی کنید. اگر چنین است، آستانه هزینه برای موازی سازی را افزایش دهید.
برای سیستم هایی مانند 1C، Microsoft CRM و Microsoft NAV، در بیشتر موارد، ممنوعیت چند رشته ای مناسب است.

همچنین، اگر نسخه استاندارد وجود داشته باشد، در اکثر موارد ممنوعیت چند رشته ای مناسب است، زیرا این نسخه از نظر تعداد هسته های CPU محدود است.
برای سیستم های OLAP، الگوریتم شرح داده شده در بالا مناسب نیست.
از تجربه خودم، الگوریتم اقدامات زیر را برای سیستم های OLAP برای تنظیم ویژگی های موازی توصیه می کنم:

سنگین ترین درخواست ها را تجزیه و تحلیل کنید و تعداد بهینه موضوعات را برای آنها انتخاب کنید
حداکثر درجه موازی سازی را روی تعداد بهینه نخ های انتخاب شده از مرحله 1 تنظیم کنید و برای پایگاه های داده خاص مقدار Max DOP را که از مرحله 1 به دست آمده برای هر پایگاه داده تنظیم کنید.
سنگین ترین پرسش ها را تجزیه و تحلیل کنید و تأثیر منفی محدود کردن همزمانی را شناسایی کنید. اگر چنین است، یا آستانه هزینه برای مقدار موازی سازی را کاهش دهید، یا مراحل 1-2 این الگوریتم را تکرار کنید.

یعنی برای سیستم های OLTP از تک رشته ای به چند رشته ای می رویم و برای سیستم های OLAP برعکس، از چند رشته ای به تک رشته ای می رویم. بنابراین، می‌توانید تنظیمات موازی بهینه را هم برای یک پایگاه داده خاص و هم برای کل نمونه MS SQL Server انتخاب کنید.
همچنین درک این نکته مهم است که تنظیمات خصوصیات موازی باید در طول زمان، بر اساس نتایج نظارت بر عملکرد MS SQL Server تغییر کند.

دستورالعمل برای تنظیم پرچم های ردیابی

با توجه به تجربه خودم و همکارانم، برای عملکرد بهینه، توصیه می‌کنم پرچم‌های ردیابی زیر را در سطح اجرای سرویس MS SQL Server برای نسخه‌های 2008-2016 تنظیم کنید:

610 - کاهش ثبت درج ها در جداول نمایه شده. می تواند با درج کردن جداول با رکوردهای زیاد و تراکنش های زیاد، با انتظارهای طولانی مدت WRITELOG برای تغییرات در شاخص ها کمک کند.
1117 - اگر یک فایل در یک گروه فایل با شرایط آستانه رشد خودکار مطابقت داشته باشد، همه فایل های موجود در گروه فایل رشد می کنند.
1118 - همه اشیاء را مجبور می کند در گستره های مختلف قرار گیرند (ممنوعیت وسعت های مختلط) که نیاز به اسکن صفحه SGAM را که برای ردیابی گستره های مختلط استفاده می شود به حداقل می رساند.
1224 - تشدید قفل را بر اساس تعداد قفل غیرفعال می کند. با این حال، استفاده بیش از حد از حافظه می تواند باعث تشدید قفل شود
2371 - آستانه به روز رسانی آمار خودکار ثابت را به آستانه به روز رسانی آمار خودکار پویا تغییر می دهد. برای به‌روزرسانی طرح‌های پرس و جو برای جداول بزرگ، که در آن تعداد نادرست رکوردها منجر به برنامه‌های اجرایی اشتباه می‌شود، مهم است.
3226 - پیام های موفقیت آمیز پشتیبان را در گزارش خطا سرکوب می کند
4199 - شامل تغییرات بهینه ساز پرس و جو منتشر شده در بسته های خدمات CU و SQL Server
6532-6534 - شامل بهبود عملکرد برای عملیات پرس و جو در انواع داده های مکانی
8048 - اشیاء حافظه پارتیشن بندی شده NUMA را به موارد پارتیشن شده توسط CPU تبدیل می کند.
8780 - تخصیص زمان اضافی را برای برنامه ریزی پرس و جو فعال می کند. برخی از درخواست‌های بدون این پرچم ممکن است رد شوند زیرا طرح پرس و جو ندارند (اشکال بسیار نادر)
8780 - 9389 - بافر اضافی پویا حافظه اعطایی را برای بیانیه‌های حالت دسته‌ای فعال می‌کند، که به اپراتور حالت دسته‌ای اجازه می‌دهد تا حافظه بیشتری درخواست کند و در صورت وجود حافظه بیشتر از انتقال داده‌ها به tempdb اجتناب کند.

همچنین قبل از سال 2016، فعال کردن پرچم ردیابی 2301 مفید است که بهینه‌سازی‌های پشتیبانی تصمیم‌گیری را فعال می‌کند و بنابراین به انتخاب طرح‌های پرس و جوی صحیح‌تر کمک می‌کند. با این حال، از نسخه 2016، اغلب تأثیر منفی بر زمان اجرای کلی پرس و جو بسیار طولانی دارد.
همچنین، برای سیستم‌هایی که فهرست‌های زیادی دارند (مثلاً برای پایگاه‌های داده 1C)، توصیه می‌کنم trace flag 2330 را فعال کنید، که جمع‌آوری استفاده از فهرست را غیرفعال می‌کند، که به طور کلی تأثیر مثبتی روی سیستم دارد.
برای اطلاعات بیشتر در مورد پرچم های ردیابی، نگاه کنید اینجا
از پیوند بالا، در نظر گرفتن نسخه‌ها و ساخت‌های سرور MS SQL نیز مهم است، زیرا برای نسخه‌های جدیدتر، برخی از پرچم‌های ردیابی به‌طور پیش‌فرض فعال هستند یا هیچ تأثیری ندارند.
می توانید پرچم ردیابی را به ترتیب با دستورات DBCC TRACEON و DBCC TRACEOFF روشن و خاموش کنید. برای جزئیات بیشتر مراجعه کنید اینجا
با استفاده از دستور DBCC TRACESTATUS می توانید وضعیت پرچم های ردیابی را دریافت کنید: بیشتر
برای اینکه پرچم های ردیابی در شروع خودکار سرویس MS SQL Server گنجانده شوند، باید به SQL Server Configuration Manager بروید و این پرچم های ردیابی را از طریق -T در ویژگی های سرویس اضافه کنید:

نمایش نتایج: از

در این مقاله برخی از جنبه های نظارت بر MS SQL Server مورد تجزیه و تحلیل قرار گرفت که با کمک آنها می توانید به سرعت کمبود RAM و زمان آزاد CPU و همچنین تعدادی از مشکلات کمتر آشکار دیگر را شناسایی کنید. رایج ترین پرچم های ردیابی مورد استفاده بررسی شده اند.

منابع:

» آمار انتظار SQL Server
» آمار انتظار SQL Server یا لطفاً به من بگویید که کجا درد دارد
» نمای سیستم sys.dm_os_schedulers
» استفاده از Zabbix برای نظارت بر پایگاه داده MS SQL Server
» سبک زندگی SQL
» پرچم های ردیابی
» sql.ru

منبع: www.habr.com

برخی از جنبه های نظارت بر MS SQL Server. دستورالعمل برای تنظیم پرچم های ردیابی