开发者社区> 金澎> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server性能优化之CPU

简介: 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改小
其他的等待类型不常见,如果有出现,可以网上查下对应等待类型产生的原因,根据原因采取对应的解决方案

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL批量SQL插入性能优化
       对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,可能每天花费在数据导入上的时间就会长达几个小时之久。
1034 0
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
835 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1487 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1667 0
ORACLE 创建DBlink访问SQL SERVER数据库
Oracle中使用透明网关链接到Sqlserver 在最近项目中需要从Oracle中访问SQL Server数据库, 自然想到了透明网关. 因为Oracle数据库是Linux上的, 而Linux上的Oracle9i不包括连接到SQL Server的透明网关.
1642 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
RDS SQL Server CPU高使用率性能优化
立即下载
SQL Sever迁移PG经验
立即下载
SQL Server 2017
立即下载