Sql Server 常见Cpu 100%的原因

常见Cpu 100%的原因主要如下：

1. 编译和重编译

编译是 Sql Server 为指令生成执行计划的过程。Sql Server 要分析指令要做的事情，分析它所要访问的表格结构，也就是生成执行计划的过程。这个过程主要是在做各种计算，所以CPU 使用比较集中的地方。

执行计划生成后会被缓存在 内存中，以便重用。但是不是所有的都可以 被重用。在很多时候，由于数据量发生了变化，或者数据结构发生了变化，同样一句话执行，就要重编译。

2. 排序（sort） 和 聚合计算（aggregation）

在查询的时候，经常会做 order by、distinct 这样的操作，也会做 avg、sum、max、min 这样的聚合计算，在数据已经被加载到内存后，就要使用CPU把这些计算做完。所以这些操作的语句CPU 使用量会多一些。

3. 表格连接（Join）操作

当语句需要两张表做连接的时候，SQL Server 常常会选择 Nested Loop 或 Hash 算法。算法的完成要运行 CPU，所以 join 有时候也会带来 CPU 使用比较集中的地方。

4. Count(*) 语句执行的过于频繁

特别是对大表 Count(*) ，因为 Count(*) 后面如果没有条件，或者条件用不上索引，都会引起 全表扫描的，也会引起 CPU 的大量运算
在 SQL SERVER 这里，和 CPU 有关的设置却很有限，主要在 sp_configure 下，常见的设置有 Priority Boost 和 Lightweight Pooling
而对于 执行计划的观察,利用 sys.dm_exec_cached_plans 视图 会比较方便直观的观察，是很有的。

解决办法：

1. 紧急处理时，可以重启Sql Server 的服务，当然，如果 DB 是在线的 DB ，请谨慎此操作。

2. 利用 Sql Server Profiler 来抓去语句，找出耗性能的SQL 语句，优化点。可以说，Sql 语句就好比 Sql server 的性能之匙，80 % 以上的 SQL SERVER 性能问题都是 SQL 语句引起的。

3. 根据 累计 使用 CPU 资源最多的语句 和 最经常 需要编辑的存储过程 来优化 语句 和存储过程。

--使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。

例如下面的语句就可以列出前50名。

SELECT c.last_execution_time,
       c.execution_count,
       c.total_logical_reads,
       c.total_logical_writes,
       c.total_elapsed_time,
       c.last_elapsed_time,
       q.[text]
FROM (SELECT TOP 50 qs.*
      FROM sys.dm_exec_query_stats qs
      ORDER BY qs.total_worker_time DESC) AS c
     CROSS APPLY sys.dm_exec_sql_text (plan_handle) AS q
ORDER BY c.total_worker_time DESC
go

 

 

--我们也可以找到最经常做重编译的存储过程。

 

SELECT TOP 25 sql_text.text,
              sql_handle,
              plan_generation_num,
              execution_count,
              dbid,
              objectid
FROM sys.dm_exec_query_stats a
     CROSS APPLY sys.dm_exec_sql_text (sql_handle) AS sql_text
WHERE plan_generation_num > 1
ORDER BY plan_generation_num DESC
go

 

中文注释：

SELECT TOP 20
       total_worker_time / 1000 AS [总消耗CPU 时间(ms)],
       execution_count[运行次数],
       qs.total_worker_time / qs.execution_count / 1000 AS [平均消耗CPU 时间(ms)],
       last_execution_time AS [最后一次执行时间],
       max_worker_time / 1000 AS [最大执行时间(ms)],
       SUBSTRING
       (
          qt.text,
          qs.statement_start_offset / 2 + 1,
              (  CASE
                    WHEN qs.statement_end_offset = -1
                    THEN
                       DATALENGTH (qt.text)
                    ELSE
                       qs.statement_end_offset
                 END
               - qs.statement_start_offset)
            / 2
          + 1) AS [使用CPU的语法],
       qt.text[完整语法],
       dbname =
          db_name (qt.dbid),
       object_name (qt.objectid, qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH (NOLOCK)
     CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
WHERE execution_count > 1
ORDER BY max_worker_time DESC