Optimize high CPU usage in instances

MySQL  |  PostgreSQL  |  SQL Server

This document explains how to review and optimize a Cloud SQL for SQL Server instance if that instance is identifiedby the underprovisioned instance recommender as having high CPU usage.

If an instance vCPU is not sized properly, it can become a source of contention. To check if the CPU is abottleneck or underprovisioned, use the queries in this document.

Check the average task count

Execute this query a couple times to check the average task count.If the average task count is consistently high, then the instance might be facing CPU pressure.

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);

Determine if there is a need to add more vCPUs

Under certain conditions, you might want to increase the vCPU. Use this query to determine if there is a need to add more vCPUs.

-- Shows queries where max and average CPU time exceeds 200 ms and executed more than 1000 timesDECLARE @cputime_threshold_microsec INT = 200*1000DECLARE @execution_count INT = 1000SELECT 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 qWHERE (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

Check for missing indexes

Check for missing indexes using the following query. Test these indexes on anon-production instance to see how they affect CPU performance.

SELECTCONVERT(  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_impactFROM 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_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH(NOLOCK)ON mig.index_handle = mid.index_handleORDER BY index_advantage DESC

Check for indexes that have too much overhead

Adding an index can help with optimization but can also add a performanceoverhead contributing to higher usage in CPU and memory. Review the indexesreturned that have a very high number of writes compared to a very low number ofreads and consider removing them.

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_idINNER 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 = 0ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC

Find the top queries consuming most of the CPU

Review the top 20 queries by CPU usage or worker time. These are the queriesbased on the query execution stats that use most of the CPU. These stats areaggregated over time and are linked to the plans in the cache.

SELECTtop 20SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.TEXT)ELSE qs.statement_end_offsetEND - 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_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY qs.total_worker_time DESC -- CPU time

Check for implicit conversions in query plans

This operation is expensive and usually shows up as a warning in the query execution plan.The message usually has a warning that says it might affectCardinalityEstimatein the query plan choice. You can identify implicit conversions when viewing thequery plans inSQL Server Management Studio (SSMS).

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-14 UTC.