来自 澳门威尼斯人注册网站 2019-12-15 17:34 的文章
当前位置: 澳门威尼斯人平台 > 澳门威尼斯人注册网站 > 正文

sql server 性能调优 I/O开销分析

一. 概述

 sql server作为关系型数据库,需要进行数据存储, 那在运行中就会不断的与硬盘进行读写交互。如果读写不能正确快速的完成,就会出现性能问题以及数据库损坏问题。下面讲讲引起I/O的产生,以及分析优化。

Zookeeper的I/O模式?

Zookeeper在启动过程中有一步很重要:初始化ServerCnxnFactory
其中的实现类有NettyServerCnxnFactory和NIOServerCnxnFactory

一.概述 

  与网络I/O相关的等待的主要是ASYNC_NETWORK_IO,是指当sql server返回数据结果集给客户端的时候,会先将结果集填充到输出缓存里(ouput cache),同时网络层会开始将输出缓存里的数据打包,由客户端接收。如果客户端接收数据包慢,sql server没有地方存放新数据结果时,这时任务进入ASYNC_NETWORK_IO等待状态。

  1. 从实例级别查看ASYNC_NETWORK_IO

   图片 1

   平均耗时: 46366950.0/43014737.0=1.077ms, 最大等待时间:~40秒。

  2. 重现ASYNC_NETWORK_IO等待

     为了演示ASYNC_NETWORK_IO 现象,我们需要输出一个大结果集。当sql server内存完全被使用后,大量的数据填充到缓存里,此时sql server没有地方存放新数据结果,进入等待状态。

-- 一次查询100000条数据输出到客户端
SELECT TOP 100000 * FROM PUB_Stock WITH(nolock)

  监听到的会话如下:

  图片 2

  使用dbcc inputbuffer 查询64结果如下:

    图片 3

  3.分析与解决

    这个等待出现的问题强调以下几点:

    (1) 客户端没有把数据及时取走,调整sqlserver 的配置一般情况下是不是有什么大的帮助。

    (2) 网络层可能是问题的原因。  解决:1是减少对客户端大量数据输出。 2是加大sqlserver 的network packe size,从一定程度上优化网络转输的性能,但会增加内存的开销(建议小于设置小于8kb)。

    network packe size是客户端与sqlserver通信的每个数据包大小有关系。network packe size设置的数据包存放于内存功能组件的connection类别里。默认是4kb设置,输入输出缓存会放在buffer pool里,如果改成了8kb 或更大,输入输出缓存会放在multi-page里 关于内存可查看sql server 内存初探。 设置network packe size 可以由sp_configure控制。客户端应用程序可以覆盖此值如在.net 里配置如下。

Data Source=(local);Initial Catalog=AdventureWorks;"Integrated Security=SSPI;Packet Size=512

    演示将 net work packe size设置成6050字节

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'network packet size', 6500 ;  
GO  
RECONFIGURE;  
GO 

   也可以能过界面来配置

  图片 4

    (3) 应用程序端性能问题,也会导致sql server里的ASYNC_NETWORK_IO等待。

      sqlserver 的网络层将结果集打包好发向客户端以后,要等到客户端确认收到,才会接着发下一个包。

    (4) 分布式锁

      如果长时间看到ASYNC_NETWORK_IO,同时在sqlserver内部又造成了阻塞,并且该等待持续了很久,就该怀疑是否是分布式的死锁。

  总结:当遇到ASYNC_NETWORK_IO等待,需要检查应用程序自己的健康状况,也要检查应用是否有必要向sql server 申请这么大的结果集返回,一般来讲sqlserver 本身没有什么问题。

一.概述

  IO 内存是sql server最重要的资源,数据从磁盘加载到内存,再从内存中缓存,输出到应用端,在sql server 内存初探中有介绍。在明白了sqlserver内存原理后,就能更好的分析I/O开销,从而提升数据库的整体性能。 在生产环境下数据库的sqlserver服务启动后一个星期,就可以通过dmv来分析优化。在I/O分析这块可以从物理I/O和内存I/O二方面来分析, 重点分析应在内存I/O上,可能从多个维度来分析,比如从sql server服务启动以来 历史I/O开销总量分析,自执行计划编译以来执行次数总量分析,平均I/0次数分析等。

  sys.dm_exec_query_stats:返回缓存的查询计划,缓存计划中的每个查询语句在该视图中对应一行。当sql server工作负载过重时,该dmv也有可以统计不正确。如果sql server服务重启缓存的数据将会清掉。这个dmv包括了太多的信息像内存扫描数,内存空间数,cpu耗时等,具体查看msdn文档。

  sys.dm_exec_sql_text:返回的 SQL 文本批处理,它是由指定sql_handle,其中的text列是查询的文本。

1.1 按照物理读的页面数排序 前50名

SELECT TOP 50
 qs.total_physical_reads,qs.execution_count,
 qs.total_physical_reads/qs.execution_count AS [avg I/O],
 qs. creation_time,
 qs.max_elapsed_time,
 qs.min_elapsed_time,
 SUBSTRING(qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset=-1
 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
 qt.dbid,dbname=DB_NAME(qt.dbid),
 qt.objectid,
 qs.sql_handle,
 qs.plan_handle
 from sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_physical_reads DESC

  如下图所示:

  total_physical_reads:计划自编译后在执行期间所执行的物理读取总次数。

  execution_count :计划自上次编译以来所执行的次数。

  [avg I/O]:    平均读取的物理次数(页数)。

  creation_time:编译计划的时间。 

        query_text:执行计划对应的sql脚本

       后面来包括所在的数据库ID:dbid,数据库名称:dbname

图片 5

 1.2 按照逻辑读的页面数排序 前50名

SELECT TOP 50
 qs.total_logical_reads,
 qs.execution_count,
  qs.max_elapsed_time,
 qs.min_elapsed_time,
 qs.total_logical_reads/qs.execution_count AS [AVG IO],
 SUBSTRING(qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset=-1 
 THEN LEN(CONVERT(NVARCHAR(max),qt.text)) *2
  ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) 
  AS query_text,
 qt.dbid,
 dbname=DB_NAME(qt.dbid),
 qt.objectid,
 qs.sql_handle,
  creation_time,
 qs.plan_handle
 from sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_logical_reads DESC

如下图所示:

图片 6

  通过上面的逻辑内存截图来简要分析下:

  从内存扫描总量上看最多的是8311268次页扫描,自执行编译后运行t-sql脚本358次,这里的耗时是毫秒为单位包括最大耗时和最小耗时,平均I/O是23215次(页),该语句文本是一个update 修改,该表数据量大没有完全走索引(权衡后不对该语句做索引覆盖),但执行次数少,且每次执行时间是非工作时间,虽然扫描开销大,但没有影响白天客户使用。

  从执行次数是有一个43188次, 内存扫描总量排名39位。该语句虽然只有815条,但执行次数很多,如里服务器有压力可以优化,一般是该语句没有走索引。把文本拿出来如下

SELECT  Count(*)  AS TotalCount FROM [MEM_FlagshipApply]
 WITH(NOLOCK) Where (((([Status] = 2) AND ([IsDeleted] = 1)) AND ([MemType] = 0)) AND ([MEMID] <> 6))

下面两图一个是分析该语句的执行计划,sqlserver提示缺少索引,另一个是i/o统计扫描了80次。

图片 7

图片 8

 新建索引后在来看看

 CREATE NONCLUSTERED INDEX ix_1
ON [dbo].[MEM_FlagshipApply] ([Status],[IsDeleted],[MemType],[MEMID])

  图片 9

      图片 10

 

二.sql server  主要磁盘读写的行为

  2.1  从数据文件(.mdf)里, 读入新数据页到内存。前页讲述内存时我们知道,如果想要的数据不在内存中时,就会从硬盘的数据文件里以页面为最小单位,读取到内存中,还包括预读的数据。 当内存中存在,就不会去磁盘读取数据。足够的内存可以最小化磁盘I/O,因为磁盘的速度远慢于内存。

  2.2  预写日志系统(WAL),向日志文件(.ldf)写入增删改的日志记录。 用来维护数据事务的ACID。

  2.3  Checkpoint 检查点发生时,将脏页数据写入到数据文件 ,在sp_configure的recovery interval 控制着sql server多长时间进行一次Checkpoint, 如果经常做Checkpoint,那每次产生的硬盘写就不会太多,对硬盘冲击不会太大。如果隔长时间一次Checkpoint,不做Checkpoint时性能可能会比较快,但累积了大量的修改,可能要产生大量的写,这时性能会受影响。在绝大多数据情况下,默认设置是比较好的,没必要去修改。

  2.4   内存不足时,Lazy Write发生,会将缓冲区中修改过的数据页面同步到硬盘的数据文件中。由于内存的空间不足触发了Lazy Write, 主动将内存中很久没有使用过的数据页和执行计划清空。Lazy Write一般不被经常调用。

  2.5   CheckDB,  索引维护,全文索引,统计信息,备份数据,高可用同步日志等。

如何配置?

QuorumPeerConfig#parseProperties中,可以看到,直接设置serverCnxnFactory的classname就行。

二. 其它网络I/O等待

  这里还有其它几个NET_WAITFOR_PACKET,PROXY_NETWORK_IO,EXTERNAL_SCRIPT_NETWORK_IOF。
  2.1 NET_WAITFOR_PACKET: 在msdn中解释是 网络读取过程中,连接正在等待网络数据包时出现。

    实际级等待如下图所示:
    图片 11   
2.2 后面二个proxy_network_io,external_script_network_iof。在生产环境下没有数据。在msdn中也没有找到相应解释。只能通过字面含义去解释。

本文由澳门威尼斯人平台发布于澳门威尼斯人注册网站,转载请注明出处:sql server 性能调优 I/O开销分析

关键词: