כמה היבטים של ניטור MS SQL Server. הנחיות להגדרת דגלי מעקב

פְּתִיחַ

לעתים קרובות, משתמשים, מפתחים ומנהלים של MS SQL Server DBMS נתקלים בבעיות ביצועים של מסד הנתונים או ה-DBMS בכללותו, ולכן ניטור MS SQL Server רלוונטי מאוד.
מאמר זה הוא תוספת למאמר שימוש ב-Zabbix לניטור מסד נתונים של שרת MS SQL והוא יכסה כמה היבטים של ניטור MS SQL Server, בפרט: כיצד לקבוע במהירות אילו משאבים חסרים, כמו גם המלצות להגדרת דגלי מעקב.
כדי שהסקריפטים הבאים יפעלו, עליך ליצור סכימת מידע במסד הנתונים הרצוי באופן הבא:
יצירת סכימת מידע

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

שיטה לאיתור חוסר ב-RAM

האינדיקטור הראשון למחסור ב-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'
  );

כאן אתה צריך לשים לב לאינדיקטורים אחוז ו-AvgWait_S. אם הם משמעותיים במכלול שלהם, אז יש סבירות גבוהה מאוד שאין מספיק RAM עבור מופע MS SQL Server. ערכים משמעותיים נקבעים בנפרד עבור כל מערכת. עם זאת, אתה יכול להתחיל עם הדברים הבאים: אחוז>=1 ו-AvgWait_S>=0.005.
כדי להוציא מחוונים למערכת ניטור (לדוגמה, Zabbix), אתה יכול ליצור את שתי השאילתות הבאות:

1. כמה סוגים של המתנה תפוסים על ידי RAM באחוזים (הסכום של כל סוגי המתנה כאלה):

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

2. כמה סוגי המתנה של 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.

שיטת זיהוי עומס יתר של מעבד

כדי לזהות את המחסור בזמן המעבד, מספיק להשתמש בתצוגת המערכת 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, להגדיר בצורה נכונה את מאפייני המקבילות של מסדי הנתונים הנדרשים:



כאן כדאי לשים לב לפרמטרים הבאים:

1. Max Grade of Parallelism - מגדיר את המספר המרבי של שרשורים שניתן להקצות לכל בקשה (ברירת המחדל היא 0 - מוגבל רק על ידי מערכת ההפעלה עצמה ומהדורת MS SQL Server)
2. סף עלות עבור מקביליות - עלות משוערת של מקביליות (ברירת המחדל היא 5)
3. Max DOP - מגדיר את המספר המרבי של שרשורים שניתן להקצות לכל שאילתה ברמת מסד הנתונים (אך לא יותר מהערך של המאפיין "Max Degree of Parallelism") (ברירת המחדל היא 0 - מוגבל רק על ידי מערכת ההפעלה עצמה ו המהדורה של MS SQL Server, כמו גם ההגבלה על המאפיין "דרגה מקסימלית של מקביליות" של כל המופע של MS SQL Server)

כאן אי אפשר לתת מתכון טוב באותה מידה לכל המקרים, כלומר צריך לנתח שאילתות כבדות.
מניסיוני האישי, אני ממליץ על אלגוריתם הפעולות הבא עבור מערכות OLTP להגדרת מאפייני מקביליות:

1. תחילה השבת את המקביליות על ידי הגדרת דרגת המקביליות המקסימלית לכל המופע ל-1
2. נתח את הבקשות הכבדות ביותר ובחר את המספר האופטימלי של שרשורים עבורן
3. הגדר את דרגת המקביליות המקסימלית למספר השרשורים האופטימלי שנבחר המתקבל משלב 2, ולמסדי נתונים ספציפיים הגדר את ערך ה-DOP המקסימלי המתקבל משלב 2 עבור כל מסד נתונים
4. לנתח את הבקשות הכבדות ביותר ולזהות את ההשפעה השלילית של ריבוי השרשורים. אם כן, הגדל את סף העלויות עבור מקביליות.
   עבור מערכות כמו 1C, Microsoft CRM ו-Microsoft NAV, ברוב המקרים, איסור על ריבוי הליכי שרשור מתאים

כמו כן, אם יש מהדורה סטנדרטית, אז ברוב המקרים איסור ריבוי ההליכים מתאים בשל העובדה שמהדורה זו מוגבלת במספר ליבות המעבד.
עבור מערכות OLAP, האלגוריתם המתואר לעיל אינו מתאים.
מניסיוני האישי, אני ממליץ על אלגוריתם הפעולות הבא עבור מערכות OLAP להגדרת מאפייני מקביליות:

1. נתח את הבקשות הכבדות ביותר ובחר את המספר האופטימלי של שרשורים עבורן
2. הגדר את דרגת המקביליות המקסימלית למספר השרשורים האופטימלי שנבחר המתקבל משלב 1, ולמסדי נתונים ספציפיים הגדר את ערך ה-DOP המקסימלי המתקבל משלב 1 עבור כל מסד נתונים
3. לנתח את השאילתות הכבדות ביותר ולזהות את ההשפעה השלילית של הגבלת מקיפות. אם כן, או הוריד את ערך סף העלות עבור מקביליות, או חזור על שלבים 1-2 של אלגוריתם זה

כלומר, עבור מערכות OLTP אנו עוברים מ-single-threading ל-multi-threading, ועבור מערכות OLAP, להיפך, אנו עוברים מ-single-threading ל-single-threading. לפיכך, אתה יכול לבחור את הגדרות ההקבלה האופטימליות הן עבור מסד נתונים ספציפי והן עבור כל המופע של MS SQL Server.
כמו כן, חשוב להבין כי יש לשנות את ההגדרות של מאפייני המקביליות לאורך זמן, בהתבסס על תוצאות ניטור הביצועים של MS SQL Server.

הנחיות להגדרת דגלי מעקב

מניסיוני האישי ומנסיונם של עמיתיי, לביצועים מיטביים, אני ממליץ להגדיר את דגלי המעקב הבאים ברמת הריצה של שירות MS SQL Server עבור גרסאות 2008-2016:

1. 610 - רישום מופחת של תוספות לטבלאות שנוספו לאינדקס. יכול לעזור בהוספות לטבלאות עם רשומות רבות ועסקאות רבות, עם המתנה תכופה ארוכת WRITELOG לשינויים באינדקסים
2. 1117 - אם קובץ בקבוצת קבצים עומד בדרישות סף הצמיחה האוטומטית, כל הקבצים בקבוצת הקבצים גדלים
3. 1118 - מאלץ את כל האובייקטים להיות ממוקמים במידות שונות (איסור על היקפים מעורבים), מה שממזער את הצורך לסרוק את דף SGAM, המשמש למעקב אחר היקפים מעורבים
4. 1224 - משבית את הסלמה של מנעולים על סמך מספר המנעולים. עם זאת, שימוש מוגזם בזיכרון יכול לעורר הסלמה של נעילה
5. 2371 - משנה את סף עדכון הסטטיסטיקה האוטומטי הקבוע לסף עדכון הסטטיסטיקה האוטומטי הדינמי. חשוב לעדכון תוכניות שאילתות עבור טבלאות גדולות, כאשר ספירה לא נכונה של רשומות גורמת לתוכניות ביצוע שגויות
6. 3226 - מדכא הודעות הצלחה של גיבוי ביומן השגיאות
7. 4199 - כולל שינויים למיטוב השאילתות ששוחרר ב-CUs ו-SQL Server Service Packs
8. 6532-6534 - כולל שיפורים בביצועים עבור פעולות שאילתה על סוגי נתונים מרחביים
9. 8048 - ממיר אובייקטי זיכרון מחולקים NUMA לאובייקטי זיכרון מחולקים ל-CPU
10. 8780 - מאפשר הקצאת זמן נוספת לתכנון שאילתות. חלק מהבקשות ללא דגל זה עשויות להידחות מכיוון שאין להן תוכנית שאילתות (באג נדיר מאוד)
11. 8780 - 9389 - מאפשר מאגר זיכרון הענקת דינמי נוסף עבור הצהרות מצב אצווה, המאפשר למפעיל מצב האצווה לבקש יותר זיכרון ולהימנע מהעברת נתונים ל-tempdb אם יש יותר זיכרון זמין

גם לפני 2016, כדאי להפעיל את דגל מעקב 2301, המאפשר אופטימיזציות משופרות של תמיכת החלטות ובכך עוזר בבחירת תוכניות שאילתות נכונות יותר. עם זאת, נכון לגרסה 2016, לעתים קרובות יש לזה השפעה שלילית על זמני ביצוע כלליים ארוכים למדי של שאילתה.
כמו כן, עבור מערכות עם הרבה אינדקסים (לדוגמה, עבור מסדי נתונים 1C), אני ממליץ להפעיל את דגל המעקב 2330, אשר משבית איסוף של שימוש באינדקס, אשר בדרך כלל משפיע לטובה על המערכת.
למידע נוסף על דגלי מעקב, ראה כאן
מהקישור שלמעלה, חשוב לשקול גם גרסאות וגירסאות של MS SQL Server, שכן עבור גרסאות חדשות יותר, כמה דגלי מעקב מופעלים כברירת מחדל או שאין להם השפעה.
אתה יכול להפעיל ולכבות את דגל המעקב עם הפקודות 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
» סגנון חיים של SQL
» דגלים עקבות
» sql.ru

מקור: www.habr.com