مقدمة
في كثير من الأحيان ، يواجه المستخدمون والمطورون والمسؤولون في MS SQL Server DBMS مشاكل في أداء قاعدة البيانات أو DBMS ككل ، لذا فإن مراقبة MS SQL Server مهمة للغاية.
هذه المقالة هي إضافة إلى المقال
لكي تعمل البرامج النصية التالية ، تحتاج إلى إنشاء مخطط inf في قاعدة البيانات المطلوبة على النحو التالي:
إنشاء مخطط inf
use <имя_БД>;
go
create schema inf;
طريقة الكشف عن نقص ذاكرة الوصول العشوائي
المؤشر الأول لنقص ذاكرة الوصول العشوائي هو الحالة عندما يلتهم مثيل MS SQL Server كل ذاكرة الوصول العشوائي المخصصة له.
للقيام بذلك ، سننشئ التمثيل التالي لـ 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 باستمرار ، فيجب التحقق من إحصائيات الانتظار.
لتحديد نقص ذاكرة الوصول العشوائي من خلال إحصائيات الانتظار ، دعنا ننشئ طريقة العرض 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];
في هذه الحالة ، يمكنك تحديد نقص ذاكرة الوصول العشوائي باستخدام الاستعلام التالي:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
هنا تحتاج إلى الانتباه إلى المؤشرات المئوية و AvgWait_S. إذا كانت مهمة في مجملها ، فهناك احتمال كبير جدًا بعدم وجود ذاكرة وصول عشوائي كافية لمثيل MS SQL Server. يتم تحديد القيم المهمة بشكل فردي لكل نظام. ومع ذلك ، يمكنك البدء بما يلي: النسبة المئوية> = 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' );
استنادًا إلى ديناميكيات القيم التي تم الحصول عليها لهذين المؤشرين ، يمكننا استنتاج ما إذا كانت هناك ذاكرة وصول عشوائي كافية لمثيل MS SQL Server.
طريقة الكشف عن التحميل الزائد لوحدة المعالجة المركزية
لتحديد نقص وقت المعالج ، يكفي استخدام طريقة عرض النظام 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;
استنادًا إلى ديناميكيات القيم التي تم الحصول عليها لهذا المؤشر ، يمكننا استنتاج ما إذا كان هناك وقت معالج كافٍ (عدد مراكز وحدة المعالجة المركزية) لمثيل MS SQL Server.
ومع ذلك ، من المهم أن تضع في اعتبارك حقيقة أن الطلبات نفسها يمكنها طلب سلاسل رسائل متعددة في وقت واحد. وأحيانًا لا يستطيع المُحسِّن تقدير مدى تعقيد الاستعلام نفسه بشكل صحيح. بعد ذلك ، قد يتم تخصيص عدد كبير جدًا من سلاسل الرسائل للطلب والتي لا يمكن معالجتها في نفس الوقت في الوقت المحدد. وهذا أيضًا يتسبب في نوع من الانتظار المرتبط بنقص وقت المعالج ، ونمو قائمة الانتظار للمجدولين الذين يستخدمون نوى وحدة معالجة مركزية معينة ، أي أن مؤشر runnable_tasks_count سينمو في مثل هذه الظروف.
في هذه الحالة ، قبل زيادة عدد أنوية وحدة المعالجة المركزية ، من الضروري تكوين خصائص التوازي بشكل صحيح لمثيل 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 ، ولقواعد البيانات المحددة ، قم بتعيين قيمة DOP القصوى التي تم الحصول عليها من الخطوة 2 لكل قاعدة بيانات
- تحليل الطلبات الثقيلة وتحديد التأثير السلبي لتعدد مؤشرات الترابط. إذا كان الأمر كذلك ، فقم بزيادة حد التكلفة للتوازي.
بالنسبة لأنظمة مثل 1C و Microsoft CRM و Microsoft NAV ، في معظم الحالات ، يكون حظر تعدد مؤشرات الترابط مناسبًا
أيضًا ، إذا كان هناك إصدار قياسي ، ففي معظم الحالات يكون حظر تعدد مؤشرات الترابط مناسبًا نظرًا لحقيقة أن هذا الإصدار محدود في عدد نوى وحدة المعالجة المركزية.
بالنسبة لأنظمة OLAP ، فإن الخوارزمية الموضحة أعلاه غير مناسبة.
من تجربتي الخاصة ، أوصي بخوارزمية الإجراءات التالية لأنظمة OLAP لإعداد خصائص التوازي:
- تحليل الطلبات الأثقل وتحديد العدد الأمثل من سلاسل الرسائل لها
- اضبط أقصى درجة للتوازي على العدد الأمثل المحدد للخيوط التي تم الحصول عليها من الخطوة 1 ، ولقواعد البيانات المحددة ، قم بتعيين قيمة 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 - يشمل التغييرات على مُحسِّن الاستعلام الذي تم إصداره في CUs و SQL Server Service Packs
- 6532-6534 - يتضمن تحسينات في الأداء لعمليات الاستعلام على أنواع البيانات المكانية
- 8048 - تحويل كائنات الذاكرة NUMA المقسمة إلى كائنات مقسمة إلى وحدة المعالجة المركزية
- 8780 - يتيح تخصيص وقت إضافي لتخطيط الاستعلام. قد يتم رفض بعض الطلبات التي لا تحتوي على هذه العلامة نظرًا لعدم وجود خطة استعلام لديهم (خطأ نادر جدًا)
- 8780-9389 - تمكين المخزن المؤقت الإضافي لذاكرة المنحة الديناميكية لبيانات الوضع الدفعي ، والذي يسمح لمشغل الوضع الدفعي بطلب ذاكرة إضافية وتجنب نقل البيانات إلى tempdb في حالة توفر ذاكرة إضافية
قبل عام 2016 أيضًا ، من المفيد تمكين علامة التتبع 2301 ، والتي تتيح تحسينات دعم القرار المحسّن وبالتالي تساعد في اختيار المزيد من خطط الاستعلام الصحيحة. ومع ذلك ، اعتبارًا من الإصدار 2016 ، غالبًا ما يكون له تأثير سلبي على أوقات تنفيذ الاستعلام الإجمالية الطويلة جدًا.
أيضًا ، بالنسبة للأنظمة التي تحتوي على الكثير من الفهارس (على سبيل المثال ، لقواعد بيانات 1C) ، أوصي بتمكين علامة التتبع 2330 ، والتي تعطل تجميع استخدام الفهرس ، والذي يكون له تأثير إيجابي بشكل عام على النظام.
لمزيد من المعلومات حول تتبع العلامات ، راجع
من الرابط أعلاه ، من المهم أيضًا مراعاة إصدارات وبنيات MS SQL Server ، كما هو الحال بالنسبة للإصدارات الأحدث ، يتم تمكين بعض علامات التتبع افتراضيًا أو ليس لها أي تأثير.
يمكنك تشغيل علامة التتبع وإيقاف تشغيلها باستخدام أوامر DBCC TRACEON و TRACEOFF DBCC ، على التوالي. لمزيد من التفاصيل انظر
يمكنك الحصول على حالة علامات التتبع باستخدام الأمر DBCC TRACESTATUS:
لكي يتم تضمين علامات التتبع في التشغيل التلقائي لخدمة MS SQL Server ، يجب عليك الانتقال إلى SQL Server Configuration Manager وإضافة علامات التتبع هذه عبر -T في خصائص الخدمة:
نتائج
في هذه المقالة ، تم تحليل بعض جوانب مراقبة MS SQL Server ، والتي يمكنك من خلالها التعرف بسرعة على نقص ذاكرة الوصول العشوائي ووقت وحدة المعالجة المركزية المجاني ، بالإضافة إلى عدد من المشكلات الأخرى الأقل وضوحًا. تمت مراجعة أعلام التتبع الأكثر استخدامًا.
مصادر:
»
»
»
»
»
»
»
المصدر: www.habr.com