In diesem Dokument wird erläutert, wie Sie eine Cloud SQL for SQL Server-Instanz prüfen und optimieren, wenn diese Instanz laut dem Recommender für unterdimensionierte Instanzen eine hohe CPU-Nutzung hat.
Wenn eine Instanz-vCPU nicht ordnungsgemäß dimensioniert ist, kann dies eine Konfliktquelle sein. Verwenden Sie die Abfragen in diesem Dokument, um zu prüfen, ob die CPU einen Engpass darstellt oder unterdimensioniert ist.
Durchschnittliche Anzahl der Aufgaben prüfen
Führen Sie diese Abfrage mehrmals aus, um die durchschnittliche Anzahl an Aufgaben zu prüfen. Wenn die durchschnittliche Aufgabenanzahl konstant hoch ist, kann die Instanz unter CPU-Druck stehen.
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);
Ermitteln, ob zusätzliche vCPUs hinzugefügt werden müssen
Unter bestimmten Umständen sollten Sie die vCPU-Anzahl erhöhen. Verwenden Sie diese Abfrage, um festzustellen, ob weitere vCPUs hinzugefügt werden müssen.
-- 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
Auf fehlende Indexe prüfen
Suchen Sie mit der folgenden Abfrage nach fehlenden Indexen. Testen Sie diese Indexe auf einer Nicht-Produktionsinstanz, um zu sehen, wie sie sich auf die CPU-Leistung auswirken.
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
Auf Indexe mit zu viel Aufwand prüfen
Das Hinzufügen eines Index kann zur Optimierung beitragen, kann aber auch einen Leistungsaufwand verursachen, der zu einer höheren CPU- und Arbeitsspeichernutzung führt. Prüfen Sie die zurückgegebenen Indexe mit einer sehr hohen Anzahl von Schreibvorgängen im Vergleich zu einer sehr niedrigen Anzahl von Lesevorgängen und ziehen Sie in Betracht, sie zu entfernen.
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
Top-Abfragen ermitteln, die die größte CPU-Nutzung verursachen
Sehen Sie sich die 20 Top-Abfragen nach CPU-Nutzung oder Worker-Zeit an. Dies sind die Abfragen basierend auf den Statistiken zur Abfrageausführung, die die größte CPU-Nutzung verursachen. Diese Statistiken werden im Laufe der Zeit zusammengestellt und mit den Plänen im Cache verknüpft.
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
Auf implizite Konvertierungen in Abfrageplänen prüfen
Dieser Vorgang ist teuer und wird normalerweise als Warnung im Abfrageausführungsplan angezeigt. Die Nachricht enthält in der Regel eine Warnung, dass CardinalityEstimate in der Auswahl des Abfrageplans beeinträchtigt sein könnte. Sie können implizite Konvertierungen einfach in den Abfrageplänen in SQL Server Management Studio (SSMS) erkennen.