SQL Server性能優化之CPU

CPU打高的常見原因

1、掃描量大的慢SQL【最常見】

2、業務高并發,QPS高【偶爾可見】

3、實體機等其他原因【極其少見】

排查思路：

1、先檢視CPU打滿的時間範圍

2、根據CPU打滿的時間檢視慢日志，關注掃描量大的【一般超過10W的，幾萬的也要關注下】

3、如果沒有掃描量大的慢請求，檢視等待類型，然後根據占比高的等待類型分析瓶頸在哪【常見的主要是CXPACKET】

4、如果2和3都沒有異常，看下QPS監控，與CPU監控是否一緻，一緻的話，考慮更新配置

慢SQL優化：

1、擷取到掃描量大的慢SQL

SELECT TOP 50

[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,

[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,

[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,

[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,

qs.execution_count,

[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,

[Total I/O] = total_logical_reads + total_logical_writes,

Query = 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

),

Batch = qt.[text],

[DB] = DB_NAME(qt.[dbid]),

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

where qs.execution_count > 5 --more than 5 occurences

ORDER BY [Total MultiCore/CPU time(sec)] DESC

慢SQL優化思路

1、先檢視SQL執行計劃，主要關注是否有table scan，index scan，書簽查找，要盡量避免scan操作

2、檢視索引資訊

聚集索引很珍貴【非聚集索引會存聚集索引鍵，否則存RID】，不要浪費

非聚集索引：一個SQL中的一個表隻能用一個索引，排序字段【order by,group by,distinct,join】最好跟where條件篩選字段一緻，可以避免排序操作

索引設計：

https://docs.microsoft.com/zh-cn/sql/2014-toc/sql-server-index-design-guide?view=sql-server-2014

3、檢視統計資訊,統計資訊陳舊，需要更新

SQL Server預設更新統計資訊的政策，表資料行數發生變化時更新

a，行數從0變為1

b，行數少于500行時，增加到500行+

c，行數多于500行時，資料的變化量大于500+20%*表中資料行數

統計資訊問題一般會出現c的情況，資料量變化很大，但是統計資訊陳舊，不适用了，需要手動更新下，或者可以用agent建job定時更新

4、索引碎片：

https://yq.aliyun.com/articles/696250?spm=a2c4e.11155435.0.0.3d413312axuFQA

等待類型優化：

WITH [Waits] AS

(SELECT [wait_type],

[wait_time_ms] / 1000.0 AS [WaitS],
    ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
    [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 [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',
    -- Maybe uncomment these four if you have mirroring issues
    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',
    -- Maybe uncomment these six if you have AG issues
    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'MEMORY_ALLOCATION_EXT',
    N'ONDEMAND_TASK_QUEUE',
    N'PREEMPTIVE_XE_GETTARGETSTATE',
    N'PWAIT_ALL_COMPONENTS_INITIALIZED',
    N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
    N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
    N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
    N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
    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_RECOVERY',
    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')
AND [waiting_tasks_count] > 0
)           

SELECT MAX ([W1].[wait_type]) AS [WaitType],

CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([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]

HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold

GO

CXPACKET居多，解決方法，将MAXDOP改小

其他的等待類型不常見，如果有出現，可以網上查下對應等待類型産生的原因，根據原因采取對應的解決方案