CPU开销分析,索引阐述系列五

2019-10-18 08:27 来源:未知
-- 创建聚集索引
create table [dbo].[pub_stocktest] add  constraint [pk_pub_stocktest] primary key clustered 
(
[sid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, 
online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

-- 创建非聚集索引
 create nonclustered index [ix_model] on [dbo].[pub_stocktest]
(
    [model] asc
)
include (     [name]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, 
online = off, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 85) on [primary]

一. 概述

  上次在介绍性能调优中讲到了I/O的开销查看及维护,这次介绍CPU的开销及维护, 在调优方面是可以从多个维度去发现问题如I/O,CPU,  内存,锁等,不管从哪个维度去解决,都能达到调优的效果,因为sql server系统作为一个整体性,它都是紧密相连的,例如:解决了sql语句中I/O开销较多的问题,那对应的CPU开销也会减少,反之解决了CPU开销最多的,那对应I/O开销也会减少。解决I/O开销后CPU耗时也减少,是因为CPU下的Worker线程需要扫描I/O页数就少了,出现的资源锁的阻塞也减少了,具体可参考cpu的原理。

  下面sql语句的dmv:sys.dm_exec_query_stats和sys.dm_exec_sql_text 已经在上篇”sql server 性能调优 I/O开销分析“中有讲到。

--查询编译以来 cpu耗时总量最多的前50条(Total_woker_time)
SELECT TOP 50
    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 [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,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  total_worker_time DESC

查询如下图所示,显示CPU耗时总量最多的前50条

图片 1

在排名第38条,拿出耗时的sql脚本来分析,发现未走索引。如下图

图片 2

SELECT [PO_NO],[Qty] FROM [ORD_PurchaseLine] WITH(NOLOCK) WHERE ([PO_NO] IN (' ')) 

图片 3

exec sys.sp_readerrorlog 0, 1, 'listening'

1.1 Filefactor参数

二. 维护注意点

  1.  在生产数据库下,CPU耗时查询,并不限定只排查总耗时前50条,可以是前100~200条。具体看sql脚本没有没优化的需要,并不是每个表的查询都必须走索引。如:有的表不走索引时并不会感觉很耗时平均I/0次数少,表中已建的索引已有多个,增删改也频繁,还有索引占用空间,这时需要权衡。 

-- 快速查看索引数量
sp_help [RFQ_PurDemandDetail]

  图片 4

 2. 不要在工作时间维护大表索引

    当我们排查到有的大表缺失索引,数据在100w以上,如果在工作时间来维护索引,不管是创建索引还是重建索引都会造成表的阻塞, 这里表的响应会变慢或者直接卡死,前端应用程序直接请求超时。这里需要注意的。来看下新建一个索引的脚本会发现 开启了行锁与页锁(ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)。

CREATE NONCLUSTERED INDEX [ix_createtime] ON [dbo].[PUB_Search_Log] 
(
    [CreateTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
 IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

 

  使用Filefactor可以对索引的每个叶子分页存储保留一些空间。对于聚集索引,叶级别包含了数据,使用Filefactor来控制表的保留空间,通过预留的空间,避免了新的数据按顺序插入时,需腾出空位而进行分页分隔。
  Filefactor设置生效注意,只有在创建索引时才会根据已经存在的数据决定预留的空间大小,如里需要可以alter index重建索引并重置原来指定的Filefactor值。
  在创建索引时,如果不指定Filefactor,就采用默认值0 也就是填充满,可通过sp_configure 来配置全局实例。Filefactor也只就用于叶子级分页上。如果要在中间层控制索引分页,可以通过指定pad_index选项来实现.该选择会通知到索引上所有层次使用相同的Filefactor。Pad_index也只有索引在新建或重建时有用。

1.2 Drop_existing 参数

  删除或重建一个指定的索引作为单个事务来处理。该项在重建聚集索引时格外有用,当删除一个聚集索引时,sqlserver会重建每个非聚集索引以便将书签从聚集索引键改为RID。如果再新建或者重建聚集索引,Sql server会再一次重建全部的非聚集索引,如果再新建或重建的聚集索引键值相同,可以设置Drop_existing=ON。

1.3 IGNORE_DUP_KEY

  是指如果一个update或者insert语句影响多行数据,但有一行键被发现产生重值时,整个语句就会回滚,IGNORE_DUP_KEY=on时产生重复键值时不会引起整个语句的回滚,重复的行会被舍弃其它的行会被插入或更新。

TAG标签:
版权声明:本文由澳门金莎娱乐网站发布于数据库,转载请注明出处:CPU开销分析,索引阐述系列五