《Troubleshooting SQL Server》读书笔记-CPU使用率过大(下)

第三章 High CPU
Utilization.

CPU使用率过大之科普原因

 
查询优化器会尽量从CPU,IO和内存资源成本可是小的角度,找到最高效的数目看模式。即使无科学的目录,或者写的讲话本身就是会晤忽略索引,

 
又或者无精确的总计音信至极情形下,查询计划或未是无限妙的。

 
有些查询计划或对才对某种条件下的查询是高效,而休是兼具条件下仍然。

缺少失索引

   
索引的少,会造成查询处理的行数大大出乎必要之行数,从而强化CPU和IO的载荷。简单的事例:

SELECT  per .FirstName  , 
        per.LastName , 
        p.Name  , 
        p.ProductNumber , 
        OrderDate , 
        LineTotal , 
        soh.TotalDue  
FROM    Sales.SalesOrderHeader  AS soh 
         INNER  JOIN Sales.SalesOrderDetail sod  
                       ON soh.SalesOrderID = sod.SalesOrderID  
         INNER  JOIN Production.Product  AS p  ON sod.ProductID  = p.ProductID 
         INNER  JOIN Sales.Customer AS c  ON soh.CustomerID = c.CustomerID  
         INNER  JOIN Person.Person AS per 
                       ON c.PersonID = per.BusinessEntityID 
WHERE    LineTotal > 25000

SQL Server parse and compile time:    
   CPU time = 0 ms, elapsed time = 0 ms. 

SQL Server Execution Times: 
   CPU time = 452 ms,  elapsed time = 458 ms.

点的询问利用AdventureWorks2008数据库,字段LineTotal上没索引,会招致SalesOrderDetail全表扫描。然后创立而下索引后,改善好显然:

CREATENONCLUSTEREDINDEX
idx_SalesOrde 
ON
Sales.SalesOrderDetail (LineTotal)
SQL Server parse and compile time:  
   CPU time = 0 ms, elapsed time = 0 ms. 

SQL Server Execution Times: 
   CPU time = 0 ms,  elapsed time = 8 ms.

 

过的总括信息

    
查询优化器使用总计音讯总计各类查询操作的基数(开销)。查询操作的血本(cost)又操了查询计划的财力。过期的统计音讯会招生成非最非凡的查询计划,

比方预估成本大没有,但实际成本很高的计划。

太常见就是预估行数十分少,并精选了那么些符合少量多少的操作(如嵌套循环,LookUp),但当实际施行时倘诺处理的行数却多,查询效用就是易得死没有。

可透过SSMS或者set
statistics profile
on为索引查找和扫描操作,重回实际行数与预估行数开比。假若两岸反差较生,就卓殊有或总括信息过期了。

过时,可以使update
statistics tableName更新表上具备的总计音信,update statistics tableName
statisticsName更新指定总括信息。

为防范总结新闻过期的题目,有如下三栽艺术:

   a.
开启数据库的Auto_Update_Statistics选项或者用定时作业更新全库的总结消息。

   b.
倘诺某些索引的自动更新总结音讯为禁用,则需指定STATISTICS_NORECOMPUTE=OFF重建索引开启。

   c.
对于某些日常因总括音信过期而致使性问题之总括音讯,可以创制定时作业频繁地革新她。

 

非SAGR谓词

SAGR=Search
Agrument.简单说尽管是力所能及选择索引查找的谓词。列应该一向和说明式进行比则抱SAGR,如WHERE 
SomeFunction(Column) = @Value就合,    

WHERE Column =
SomeOtherFunction(@Value) 则符合。注意LIKE和BETWEEN也是SAGR谓词。

非SAGR会导致表或者找引围观,它的震慑跟缺失索引类似。使得CPU处理大量非必需的数据行。下边查询会招致索引围观:

SELECT  soh .SalesOrderID , 
        OrderDate , 
        DueDate , 
        ShipDate  , 
        Status  , 
        SubTotal  , 
        TaxAmt  , 
        Freight , 
        TotalDue 
FROM    Sales.SalesOrderheader  AS soh 
         INNER  JOIN Sales.SalesOrderDetail  AS sod   
                     ON soh.SalesOrderID = sod.SalesOrderID  
WHERE     CONVERT(VARCHAR(10), sod.ModifiedDate , 101) = '01/01/2010'

转写成如下则会下索引查找:

SELECT  soh .SalesOrderID , 
        OrderDate , 
        DueDate , 
        ShipDate  , 
        Status  , 
        SubTotal  , 
        TaxAmt  , 
        Freight , 
        TotalDue 
FROM    Sales.SalesOrderheader  AS soh 
         INNER  JOIN Sales.SalesOrderDetail  AS sod  ON soh.SalesOrderID = sod.SalesOrderID  
WHERE    sod.ModifiedDate >= '2010/01/01'  
         AND  sod.ModifiedDate < '2010/01/02'

UPPER,LOWER,LTRIM,RTRIM,ISNULL这些平常会叫滥用,甚至用于WHERE和JOIN条件被。

在不分轻重缓急写排序规则中,大小写给视为相等的,像UPPER,LOWER这种拖累性能的函数就不必要就此了。

SQL中字符串相比会忽略末尾空格,所以RTRIM也没有必要就此。

下边两独过滤条件,前者,字段NULL值转换成0从如被解除;后者被,其实NULL值与其余价值相比操作都非谋面回TURE,而为排除。

NULL值只于IS NULL或者IS NOT
NULL检查时才可能回TRUE。所以是一致的,但后者才会采纳索引查找。

WHERE 
ISNULL(SomeCol,0) > 0
WHERE 
SomeCol > 0

 

隐式转换

    
隐式转换暴发在比少单不等数据类型时。SQL不克对两样序列数据进行较,所以查询优化器会在可比操作前将低优先级的数据类型转换成为高优先级的数据类型再于。

立时和非SARG谓词一样,将非能够利用Index
Seek,从而处理过剩免必要的多寡举行,扩大CPU开销。最常见例子是接纳NVARCHAR类型的参数和VARCHAR类型的排列进行比。如:

 

SELECTp .FirstName , p.LastName , c.AccountNumber 
FROM
Sales.Customer ASc 
INNER JOINPerson.Person AS p 
ON c.PersonID = p.BusinessEntityID 
 WHERE AccountNumber = N'AW00029594'

上面的查询导致一个非聚集索引扫描,在Filter操作中会看有一个COVERT_IMPLICIT。

为防止隐式转换:

    1.
JOIN之排列,数据类型尽量相同

    2.
与列于常,任何参数,变量和常量的门类要和排的项目相同

    3.
当参数,变量或常量的色和如比的排不同时,探讨地运用类型转换函数,使其以及列类型相同

    4.
微数据看组件和开框架会管字符串类型默认地安装为NVARCHAR

 

参数探测(Parameter
Sniffing)

      参数探测是SQL
Server为存储过程,函数和参数化查询成立查询计划时用到之处理形式。当首差编译查询计划时,SQL
Server会检测或者探测输入参数的价值并组成总计信

住,预估为影响之行数,

连为之预计查询计划成本。当遵照传入的参数值创造查询计划,得到的让影响行数不是非凡的动静时常,就爆发问题了。参数探测只现出于编译和重编译时,之后的存储过程,函数和

参数化查询,

相会用此询问计划。最初编译时仅发输入参数的值会被探测到,本地变量是从未价值的。尽管批处理着的语句为还编译,则参数与变量将会见被赋值并探测到。示例如下:

CREATEPROCEDUREuser_GetCustomerShipDates 
( @ShipDateStart DATETIME
, @ShipDateEnd DATETIME
) 
AS
SELECT CustomerID , SalesOrderNumber 
FROM Sales.SalesOrderHeader 
WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd 
GO

Sales.SalesOrderHeader表的ShipDate字段范围是2004-08-07~2011-08-07,并创建非聚集索引: 

CREATENONCLUSTEREDINDEX IDX_ShipDate_ASC 
ON Sales.SalesOrderHeader (ShipDate ) 
GO
首先我们执行两次SP,并用DBCC FREEPROCCACHE在运行前清空计划缓存: 
DBCC FREEPROCCACHE 
EXEC user_GetCustomerShipDates '2001/07/08', '2004/01/01' 
EXEC user_GetCustomerShipDates '2001/07/10', '2001/07/20'

查询计划如图: 

询问并不曾行使ShipDate列非聚集索引,因为其不是一个掩索引,并且让实施时,查询优化器按照参数值结合总计音讯预估的行数很多,使用IndexSeek和LookUp的整合成本

 

太高。再观察STATISTICS
IO&TIME:

==FIRST EXECUTION (LARGE DATE RANGE)===  

(Table 'SalesOrderHeader'. Scan count 1, logical reads 686, physical reads 0.  

 SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 197 ms. 

 SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 197 ms. 

==SECOND EXECUTION (SMALL DATE RANGE)=== 

Table 'SalesOrderHeader'. Scan count 1, logical reads 686, physical reads 0. 

 SQL Server Execution Times: 
   CPU time = 15 ms,  elapsed time = 5 ms. 

 SQL Server Execution Times: 
   CPU time = 15 ms,  elapsed time = 5 ms.

双面的纯CPU时间和IO是中央相同,因为前者需要处理的数据量多广大,所以CPU消耗时间累加一些。接下来,交换几个实施SP的逐条,再举行:

DBCC FREEPROCCACHE 
EXEC user_GetCustomerShipDates '2001/07/10', '2001/07/20' 
EXEC user_GetCustomerShipDates '2001/07/08', '2004/01/01'

==FIRST EXECUTION (SMALL DATE RANGE)===  

Table 'SalesOrderHeader'. Scan count 1, logica
ahead reads 0, lob logical reads 0, lob physic

 SQL Server Execution Times: 
   CPU time = 0 ms,  elapsed time = 0 ms.  

==SECOND EXECUTION (LARGE DATE RANGE)=== 

Table 'SalesOrderHeader'. Scan count 1, logica

 SQL Server Execution Times: 
   CPU time = 47 ms,  elapsed time = 182 ms.

这次两者性能差别就大醒目了。参数探测导致优化器采取了适合少量数目标KeyLookUp操作,而第二赖查询重用了这些询问计划,可是实际上它用处理大量数额,

 

这会儿KeyLookUp就招致了彰着的特性问题,需要相当的IO和CPU资源。 

 

据悉具体的条件暨SQL
Server版本,有多拍卖参数探测的点子:

  1. 盯住标志4136

     
此跟踪标志使得SQLServer实例不再行使参数探测,而是用列平均重复个数(=总行数/列的非重复值个数)来打量为影响行数。
这样的算计值是休纯粹的。

 

启用那些标志将会面教那多少个对的参数探测的图景,变得无标准,带来负面影响。所以应当举办吧最后的招。

 

适用于SQL Server 2008 SP1
CU7,SQL Server 2008 R2 CU2,SQL Server 2005 in SP3 CU9。 

 

  1. 动用OPTIMIZE FOR查询指示SQLServer
    2005和后续版本中,能够呢查询优化器编译查询计划时指定参数的价。如:

    CREATE PROCEDURE user_GetCustomerShipDates

     ( 
       @ShipDateStart DATETIME  , 
       @ShipDateEnd DATETIME  
     ) 
    

    AS

     SELECT  CustomerID , 
             SalesOrderNumber 
     FROM    Sales.SalesOrderHeader 
     WHERE    ShipDate  BETWEEN @ShipDateStart AND  @ShipDateEnd 
     OPTION   ( OPTIMIZE  FOR ( @ShipDateStart = '2001/07/08' , 
                               @ShipDateEnd = '2004/01/01'  ) ) 
    

    GO

每当2008中尚可以OPTIMIZE FOR
UNKNOWN使得优化器不用参数探测,这个跟T-4136平,只不过是语句级。

        

  1. 重编译选项   

        
在创设存储过程可以指定WITH
RECOMPILE重编译选项。指定后SP每一次执行时会基于当前参数值重新编译,同时为不缓存执行计划。可是这样会增加执行处理时。

CREATE PROCEDURE user_GetCustomerShipDates 
                   ( 
                     @ShipDateStart DATETIME  , 
                     @ShipDateEnd DATETIME  
                   ) 
                   WITH RECOMPILE 
                   AS   
                   SELECT  CustomerID ,SalesOrderNumber 
                   FROM    Sales.SalesOrderHeader 
                   WHERE    ShipDate  BETWEEN @ShipDateStart AND  @ShipDateEnd

   当SP中的大都单话,只是有语句会发生参数探测问题,则好针对之话以OPTION(RECOMPILE)查询提醒。这样每一趟执行时然则碰面指向这话重编译,

    而休像WITH
RECOMPILE对任何SP重编译。假使可能尽量以查询提醒,减弱重复编译的熏陶范围与开。

CREATE PROCEDURE user_GetCustomerShipDates 
             ( 
               @ShipDateStart DATETIME  , 
               @ShipDateEnd DATETIME  
             ) 
             AS   
              SELECT  CustomerID , 
                     SalesOrderNumber 
              FROM    Sales.SalesOrderHeader 
              WHERE    ShipDate  BETWEEN @ShipDateStart AND  @ShipDateEnd 
              OPTION ( RECOMPILE )

 

即席非参数化查询

    即席查询不能重用执行计划,每次执行时都会被编译,消耗大量资源(特别是CPU)。像下面的查询,每次因为WHERE条件中参数值不同而产生不同的执行计划。

    虽然SQL Server有简单参数化(Simple Parameterization)的技术,但是此语句相对”太复杂”了。

SELECT  soh .SalesOrderNumber  , 
            sod.ProductID 
    FROM    Sales.SalesOrderHeader  AS soh 
    INNER  JOIN Sales.SalesOrderDetail  AS sod 
                ON soh.SalesOrderID = sod.SalesOrderID  
    WHERE    soh.SalesOrderNumber  = 'SO43662'

    非参数化查询主要有两方面的影响:

        1. 即席查询产生的一次性的查询计划会填满计划缓存。由此带来的内存压力,会让那些本可以重用的计划迫于内存压力而被清除掉等等。

        2. 编译这些一次性的查询计划浪费了大量的CPU资源。

    可以用下面的计数器来判断即席非参数化查询的影响程度: 

SQLServer: SQL Statistics:
SQL Compilations/Sec
SQLServer: SQL Statistics:
Auto-Param Attempts/Sec
SQLServer: SQL Statistics:
Failed Auto-Param/Sec

缓解之点子发生:

1.
改动应用程序代码,使殡葬至SQL Server语词尽量吃参数化。

  1. 以SQL Server
    2005及以上版本中,能于数据库级别设定强制参数化。但可能会合带类型参数探测一样的问题。

    ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

    1. SQL Server 2008跟以上版本中,启用实例级其它optimize for  ad hoc  workloads。

      启用后当即席查询第一次执行时只保存查询计划的一个“存根”,第二次执行时则缓存执行计划。“存根”使用很少的内存,
      
      这样就减少了那些本可以重用的执行计划,因内存压力而被清除掉的机率。
      

      EXEC sp_configure ‘show advanced options’,1

            RECONFIGURE 
            EXEC sp_configure  'optimize for ad hoc workloads',1 
            RECONFIGURE
      

      无适合的相

      互相查询是管一个查询的工作讲成三个线程执行,每一个线程使用单独的计划程序。查询并行发生在操作符(Operator)级别。

      询问优化器在编译执行计划时连连会叫这尽可能的赶紧。假若推行计划的预估成本连续跨越cost threshold of prillelism,同时SQL Server可用

      CPU个数多受1只,并且max degree of prallelism为0或超越1,则发出的举办计划用会合席卷相互。

      交互查询通过水平划分输入数据,然后分布及几近个逻辑CPU上而举行,从而收缩执行时间。对于数据仓库和表格系统会出便宜,对于OLTP系统

      即便连行会占用过多的CPU资源,而任何的哀告不得不待CPU资源。

      SQL Server有零星个操并行执行的sp_configure选项:

      cost threshold of prillelism:控制优化器为查询利用并行执行的阀值

      max degree of prallelism:制止单个查询用完所有可用的微处理器内核

      cost threshold of prallelism的默认值为5秒。在可比生之数据库及,默认阀值可能极端没有了,会促成并行执行的资源争用。

      可以通过下面的查询得到存于计划缓存中的并行执行计划,做啊调整cost threshold of prillelism的要紧参照依照:

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

      WITH XMLNAMESPACES
      (DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan‘)
      SELECT query_plan AS CompleteQueryPlan ,

      n.value ('(@StatementText)[1]', 'VARCHAR(4000)' ) AS StatementText  , 
      n.value ('(@StatementOptmLevel)[1]', 'VARCHAR(25)') 
                AS StatementOptimizationLevel , 
      n.value ('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') 
                 AS StatementSubTreeCost  , 
      n.query ('.' ) AS ParallelSubTreeXML  , 
      ecp.usecounts  , 
      ecp.size_in_bytes 
      

      FROM sys .dm_exec_cached_plans AS ecp

       CROSS  APPLY  sys .dm_exec_query_plan(plan_handle) AS eqp 
       CROSS  APPLY  query_plan.nodes  
              ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') 
       AS qn  ( n  ) 
      

      WHERE n.query (‘.’ ).exist (‘//RelOp[@PhysicalOp=”Parallelism”]’ ) = 1

       

      互着应用及处理器数是得到自后边三者中的最为小值:max degree of prallelis的价值,可用之处理器数和MAXDOP查询指示(会覆盖max degree of prallelis指定的价)。

      确切的max degree of prallelis值取决于工作负荷类型以及硬件资源支撑并行开销的能力。很多砖家推荐用这设定也1,前提是若的体系是真的独门的OLTP系统,

      只有大量底产出的小事务。对于NUMA和SMP系统的设定是殊的。SQL Server 2008跟以上版本用resource governor能用max degree of prallelis绑定到一定的查询组。

       

      当系统出现彼此性能问题时常,通常会CXPACKET等待会相比较明确,其实她怪冤,它只是是个表面现象。根本原因依然如查阅有CXPACKET等待的子线程的守候类型。

      假使伴随在若IO_COMPLETION,ASYNC_IOCOMPLETION,PAGEIOLATCH*的待,则使增进IO性能;

      假诺伴随在LATCH_*  and SOS_SCHEDULER_YIELD,则表示并行本身造成了性能问题,假如这还有ACCESS_METHODS_DATASET_PARENT等待,则行的并行度是根本原因。

      起这一个题目时常首先应优化并行执行的言辞,其次才是做max degree of prallelis和cost threshold of prillelism两者举办界定,再次是擢升硬件。

      当硬件暂时无法升级时,只好对相互做一定范围,做赔钱中同权衡考量。

       

      于SQL Server 2005,有个有关TokenAndPermUserStore的CPU问题。当数据库用户多,adhoc和动态查询很多还非AWE内存空间很多时不时,

      或会晤油然则生CMEMTHREAD大量候与TokenAndPermUserStore使用过并连增强。微软早已推出的缓解方案http://support.microsoft.com/kb/927396。

      绵绵的化解方案是修改程序架构,尽量裁减可能导致这问题之adhoc和动态查询的使。

      长时间解决方案是:

      1. 叫应用程序账户提高也sysadmin,从而回避权限检查假若庞大的削减TokenAndPermUserStore的缓存使用量。这是生平安风险的

      2. 运用DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’),定期清理这同组成部分缓存。

      3. SQL Server 2005 SP2及以上版本,可以下Trace Flag 4618&4610.4610限制缓存条目为1024,两者都启用则限制缓存条目为8192.

      4. SQL Server 2005 SP3及以上版本,使用Trace Flag 4621,可以设定缓存配额。 http://support.microsoft.com/kb/959823。

      5. SQL Server 2008有access check cache bucket count & access check cache quota两个sp_configure项,

        用来安装TokenAndPermUserStore的hash bucket数量与缓存条目数。

       

      关于超线程和BIOS节能控制选项

      许多砖家推荐SQL Server服务器不宜开超线程,按照作者的钻,这种推荐做法在过去是指向之,现在不必然是针对的。

      以过去,超线出来的CPU会联手齐享板载缓存,而这些缓存是KB量级的。而且windows 2000自我不辅助超线程,“超”出来的它碰面觉得是大体CPU。“CPU”一基本上就是会师促成缓存命中率低下,进而影响属性。

      使多DBA管理之OLTP&DSS的鱼龙混杂环境,那么超线程对于那一个DSS型查询的升级,在混环境中非凡不明确,甚至有时晤面招致急需神速响应OLTP弄查询等。

      现今,硬件和软件发展了,现在板载缓存量级都是MB级的了,CPU缓存命中率不再是问题。而windows 2003啊协助超线程了。而且现在OLTP,OLAP,DW等体系一般会切断使用。

      当然是否被或用经过测试才会最后决定。

       

      青色节能现在吧是硬件及服务器一种植标准了,自动退系统面临或多或少暂时无用到的硬件能淘和CPU频率。BioS中可设定为硬件自己支配或OS控制。

      windows 2008&r2默认设定电源计划也“平衡”,以允许该切换至节能格局。有时在windows 2008 or R2的新服务器升级系统一段时间后,性能降低彰着,这即是电源管理导致的CPU降频导致的。

      windows的性质计数器 % Processor Usage 是一度采取CPU频率除以可用CPU频率得到的。尽管CPU被降频了,那么是统计器会较高,会为人口误解成工作负荷很高。

      好行使CPU-Z来检测CPU的状态。

      建议将windows的电源计划调定为“高性能”,并且检查BIOS的电源控制选项,设定为OS Control.

       

      总结:

      1. SQL SERVER CPU调整的地点大少,很多时候依旧因为言语性能低下导致CPU使用过大。

      2. 解决参数嗅探问题,还有拼动态语句,使用plan_guide等方法。

相关文章