本文档介绍了如何在 Cloud SQL for SQL Server 实例被预配不足的实例 Recommender 识别为 CPU 使用率高时查看和优化该实例。
如果实例 vCPU 的大小不合适，就可能会成为争用问题的根源。如需检查 CPU 是否存在瓶颈或是否预配不足，请使用本文档中的查询。
查看平均任务数
执行此查询几次，以检查平均任务数。如果平均任务数一直很高，则实例可能面临 CPU 压力。
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
在某些情况下，您可能需要增加 vCPU。您可以使用此查询来确定是否需要添加更多 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
检查是否缺少索引
使用以下查询检查是否缺少索引。在非生产实例上测试这些索引，以了解它们对 CPU 性能有何影响。
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
检查开销过大的索引
添加索引有助于进行优化，但也会增加性能开销，导致 CPU 和内存的使用率更高。查看返回的索引（这些索引的写入次数非常多，而读取次数非常少），并考虑移除它们。
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 最多的查询
按 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) 中查看查询计划时，您可以轻松识别隐式转换。