אופטימיזציה של שימוש גבוה ב-CPU במופעים

‫MySQL | PostgreSQL | SQL Server

במאמר הזה מוסבר איך לבדוק ולבצע אופטימיזציה של מכונת Cloud SQL ל-SQL Server אם שירות המלצות על מכונות עם הקצאות-חסר מזהה במכונה שימוש גבוה במעבד (CPU).

אם לא מגדירים את הגודל של vCPU של מופע בצורה נכונה, הוא עלול להפוך למקור של מחלוקת. כדי לבדוק אם מעבד הוא צוואר בקבוק או שהוא לא מספיק חזק, משתמשים בשאילתות שבמסמך הזה.

שימוש בתובנות לגבי שאילתות כדי לזהות שאילתות עם צריכת CPU גבוהה

תובנות לגבי שאילתות עוזרות לכם לזהות, לאבחן ולמנוע בעיות בביצועים של שאילתות במסדי נתונים של Cloud SQL.

בדיקת מספר המשימות הממוצע

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


SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count],
GETDATE() AS [System Time]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);

קובעים אם צריך להוסיף עוד מעבדים וירטואליים

בתנאים מסוימים, יכול להיות שתרצו להגדיל את מספר ליבות ה-vCPU. אפשר להשתמש בשאילתה הזו כדי לקבוע אם יש צורך להוסיף עוד מעבדים וירטואליים.

-- Shows queries where max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
      qs.max_worker_time/1000 max_cpu_time_ms,
      (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
      qs.execution_count,
      q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle)
AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
       OR qs.max_worker_time > @cputime_threshold_microsec )
       AND execution_count > @execution_count
       ORDER BY qs.total_worker_time DESC

בדיקה אם יש אינדקסים חסרים

כדי לבדוק אם יש אינדקסים חסרים, משתמשים בשאילתה הבאה. כדאי לבדוק את האינדקסים האלה במופע שאינו מיועד לייצור כדי לראות איך הם משפיעים על ביצועי המעבד.

SELECT
CONVERT(
  decimal(18, 2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01))
  AS [index_advantage],
CONVERT(nvarchar(25), migs.last_user_seek, 20) AS [last_user_seek],
mid.[statement] AS [Database.Schema.Table],
COUNT(1) OVER (PARTITION BY mid.[statement]) AS [missing_indexes_for_table],
COUNT(1)
  OVER (PARTITION BY mid.[statement], mid.equality_columns)
  AS [similar_missing_indexes_for_table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.user_seeks,
CONVERT(decimal(18, 2), migs.avg_total_user_cost) AS [avg_total_user_cost],
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH(NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH(NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH(NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC

בדיקה של אינדקסים עם תקורה גבוהה מדי

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

SELECT
 SCHEMA_NAME(o.[schema_id]) AS [Schema Name],
 OBJECT_NAME(s.[object_id]) AS [TABLE Name],
 i.name AS [Index Name],
 i.index_id,
 i.is_disabled,
 i.is_hypothetical,
 i.has_filter,
 i.fill_factor,
 s.user_updates AS [Total Writes],
 s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
 s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH(NOLOCK)
INNER JOIN sys.indexes AS i WITH(NOLOCK) ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN sys.objects AS o WITH(NOLOCK) ON i.[object_id] = o.[object_id]
WHERE
 OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
 AND s.database_id = DB_ID()
 AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
 AND i.index_id > 1
 AND i.[type_desc] = N'NONCLUSTERED'
 AND i.is_primary_key = 0
 AND i.is_unique_constraint = 0
 AND i.is_unique = 0
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC

איך מוצאים את השאילתות המובילות שצורכות הכי הרבה משאבי CPU

בודקים את 20 השאילתות המובילות לפי שימוש ב-CPU או זמן עבודה. אלו השאילתות שמשתמשות ברוב המעבד, על סמך נתוני הביצוע של השאילתות. הנתונים הסטטיסטיים האלה מצטברים לאורך זמן ומקושרים לתוכניות במטמון.

SELECT
top 20
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time

בדיקה של המרות מרומזות בתוכניות שאילתות

הפעולה הזו יקרה, ובדרך כלל היא מופיעה כאזהרה בתוכנית הביצוע של השאילתה. בדרך כלל, ההודעה כוללת אזהרה שאומרת שהיא עשויה להשפיע על CardinalityEstimate הבחירה בתוכנית השאילתה. אפשר לזהות המרות מרומזות כשצופים בתוכניות השאילתות ב-SQL Server Management Studio (SSMS).

המאמרים הבאים

Google Cloud recommenders

אופטימיזציה של שימוש גבוה ב-CPU במופעים קל לארגן דפים בעזרת אוספים אפשר לשמור ולסווג תוכן על סמך ההעדפות שלך.