笔记33-徐 SQLSERVER I/O问题

笔记33-徐 SQLSERVER I/O问题

--SQLSERVER I/O问题

--（1）在SQL的errorlog里出现833错误，出现这个错误SQL的响应速度会受到影响，甚至整个服务的响应都会变得很慢
--（2）在sys.sysprocesses里有很多连接的状态是pageiolatch_*或writelog

--SQL和硬盘进行交互的情况：
--（1）对于内存中没有缓存的数据，第一次访问时需要将数据所在页面从数据文件（mdf文件）中读取到内存里

--（2）在任何insert/update/delete提交之前，SQL需要保证日志记录能够写入到日志文件里
--严格来讲，SQL不是直接将日志记录写入日志文件里的，中间还有一层日志记录缓存。SQL会将
--修改所产生的日志记录写入日志记录缓存区，而SQL又会保证缓存区里的内容会很及时地写入日志文件。
--哪怕有任何意外发生（包括掉电，服务异常等）。对用户来讲，可以粗略理解成，在任何insert/update/delete提交之前
--SQL都需要将日志记录写入日志文件，每次写的数量跟数据修改量有关


--（3）SQL做checkpoint时，需要将内存缓存区中已经发生过修改的数据页面同步到硬盘中的数据文件里

--（4）当SQL buffer pool发生压力时，会触发lazy writer，主动将内存里的一些很久没有使用过的数据页面和执行计划
--清空。如果这些页面发生的修改还未由checkpoint写回硬盘，lazy writer会将其写回

--（5）一些特殊的操作，例如：DBCC CHECKDB,reindex,update statistics,数据库备份等，会带来比较大的硬盘读写


--特点
--（1）对于select操作，只要数据缓存在内存里，不应该有任何硬盘读写
--（2）SQL基本只读数据文件，读的量和内存的缓存能力有直接关系，也和用户需要读取的数据量有关系
--（3）SQL的写分两块，数据文件和日志文件。数据文件写的数目，主要和修改量有关，lazy writer也和
--内存是否有压力有关。日志文件写的数目，完全由数据修改量决定，和内存是否有压力无关




--1、SQL的recovery interval(sp_config)
--recovery interval控制着SQL多长时间进行一次checkpoint。如果间隔比较长的时间做checkpoint,在不做
--checkpoint的时候，SQL没有硬盘写，性能会比较快。但是做checkpoint的时候，因为累计了大量修改，
--可能要产生大量写，在这个时间点上，SQL性能会受到影响。所以延长checkpoint间隔不是一件好事，
--在绝大多数情况，默认设置是比较好的，没有必要修改他。



--2、数据/日志文件的自动增长和自动收缩
--文件的增长和收缩对SQL向这个文件的读写速度都会产生严重影响，所以对一个生产数据库，要避免自动增长
--和自动收缩发生!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


--3、数据文件里面页面碎片程度
--SQL对数据的操作是以页面为最小单位的。对于同样的数据量，他们在页面里排得越紧凑，
--所要消耗的页面数目也就越小，那么SQL需要读入和写出的页面数目也就越少。如果页面碎片
--很多，每个页面并没有存放多少数据，那么SQL可能必须要读入和写出多很多的页面。这会
--造成额外的硬盘读写量。

-----------------实验--------------------------------------------------------------------
--读者可以用下面的范例来演示这个关系。首先在tempdb里创建一张表，表的每一条记录会占1KB大小
--所以下面一个数据页面会存储大约8条记录
USE tempdb
GO
DROP TABLE demo
GO
CREATE TABLE demo(
a INT,        --存储大小为 4 个字节。int类型，最大可以存储32位的数据
b CHAR(990)   --固定长度，非 Unicode 字符数据，长度为 n 个字节。n 的取值范围为 1 至 8,000，存储大小是 n 个字节
)
GO

--994字节差不多1KB 一条记录
CREATE CLUSTERED INDEX demo_index ON demo(a)

--接着在表里插入1000条记录，记录占据125个页面
DECLARE @i INT
SET @i=0
WHILE @i <1000
BEGIN
INSERT INTO demo VALUES(@i,'abcd')
SET @i=@i+1
END
GO
DBCC SHOWCONTIG(demo)
GO

--然后，每8条记录里删除7条记录。这样在每个页面里，只会保存一条记录。这时候页面里的大部分空间都是空闲的
--通常说这种页面里“碎片（fragment）”比较多
--------------------------------------------------------------------------------------
USE tempdb
DECLARE @i NUMERIC(6,2)
SET @i=0
WHILE @i<1000
BEGIN
IF @i/8 <>CONVERT(INT,@i/8)
DELETE demo WHERE a=@i
SET @i=@i+1
END
GO
SELECT * FROM demo
go
DBCC SHOWCONTIG(demo)
GO
--平均页密度由原来的83% 变为11%,页数不变都是150

--现在运行对这个表的查询,看看SQL需要读取多少个页面
SET STATISTICS IO ON
GO
SELECT * FROM dbo.demo
go
SET STATISTICS IO OFF
GO

--做一次索引重建,消除表上的碎片(defragment)
USE tempdb
ALTER INDEX demo_index  ON dbo.demo REBUILD
GO
--再运行同样的查询
SET STATISTICS IO ON
GO
SELECT * FROM dbo.demo
go
SET STATISTICS IO OFF
GO

--结果:重建索引前是127次,重建索引后是18次逻辑读




--4、表格上的索引结构
--聚集索引和没有聚集索引的差别，有聚集索引的时候，在存储同样多的数据时，明显管理得好一些




--5、数据压缩
--在SQL2008以后，只读文件组可以使用NTFS的压缩技术，以节省磁盘空间的消耗。
--代价是Windows需要花费额外的CPU与内存资源将数据解压缩，然后返回给SQL。
--所以如果CPU能力跟不上的话，对整体性能可能会造成负面影响



--6、数据文件和日志文件是否放在同一个硬盘上
--SQL对数据文件的读写不是时刻在进行的，总的来说，大部分的SQL读得多，写的少。
--对于日志文件，只要数据库发生修改，日志记录就需要不断地写入日志文件。如果写的
--动作不能及时完成，事务就不能提交，所以日志文件的写入性能对SQL的整体性能影响
--比较大。两类文件对SQL同等重要，但是要求各有不同

--通常建议：把数据文件和日志文件放在不同的硬盘上。如果有可能，日志文件放在写入速度
--比较快的磁盘上，例如，一个RAID10的分区。如果放在比较慢的RAID5分区上，就比较容易
--遇到I/O问题


--7、一个数据文件组是否有多个文件，并且放在不同的物理硬盘上
--对于数据文件，SQL会将新数据按照同一个文件组的每个文件剩余空间的大小，按比例
--写入所有有空余空间的文件里。所以如果这些文件分布在不同的物理硬盘上，那么I/O工作
--也会分布到不同的硬盘。这样对整体性能有帮助

--但是，对于日志文件，在一个时间点，SQL只会写一个日志文件。在不同的硬盘上创建多个日志
--文件对性能基本无帮助

--多个文件组，不同的硬盘
--一个文件组，多个数据文件，不同的硬盘
--日志文件不属于任何文件组

--总而言之，能够直接影响SQL做不做I/O配置是比较少的。但是DBA可以通过很多设定，间接地影响
--SQL，从而达到调整I/O的目的
--另外，和内存与CPU不同，SQL对硬盘的读写基本上是放手交给Windows管理的。SQL主要是调用不同
--的WindowsAPI，以完成各种读写需求。这方面，SQL跟一般的应用程序没什么差别




--系统级I/O问题判断
--从Windows层面分析，将主要依赖性能监视器里的各个计数器。Windows可以在一个物理硬盘上
--划分出若干个逻辑分区，赋给每个分区一个逻辑盘符，也可以将整个磁盘做单独一个分区使用
--对于分配在同一块物理磁盘的逻辑分区，其实他们的操作都共享磁盘的读写带宽，相当于在
--一块磁盘上工作。而Windows上的应用，只认逻辑盘符。逻辑盘在哪一块物理磁盘上，是否和
--其他逻辑盘共享同一块磁盘，应用程序是不知道的


--logicaldisk：记录的是按照逻辑盘符，每个逻辑盘的读写计数器。可以了解系统以及应用向不同的盘符
--发出的工作量各有多大

--physicaldisk：以物理磁盘为单位，如果某个物理磁盘是有RAID组成的磁盘阵列，那么Windows只会列出
--一整块物理磁盘。如果物理磁盘上有若干个逻辑分区，这些分区将以一组为单位出现。
--可以了解磁盘的响应速度。如果要分析磁盘性能问题，主要分析physicaldisk下的计数器，logicaldisk
--仅能起到参考作用

--常用磁盘计数器：

--%idle time
--%disk time
--%disk read time
--%disk wirte time
--avg.disk sec/read
--avg.disk sec/write
--avg.disk sec/transfer
--avg.disk bytes/transfer
--avg.disk queue length
--avg.disk read queue length
--avg.disk write queue length
--disk bytes/sec
--disk transfer/sec
--disk read/sec
--disk writes/sec
--disk read bytes/sec
--disk write bytes/sec
--current disk queue length

--关键计数器如下：

--%disk time
--在采样期间，磁盘处于读写状态的百分比。理论上，这个值应该小于100%，而且应该是%disk read time和
--%disk wirte time的和。但是由于算法限制，在磁盘很忙的时候，这个计数器往往会大于100%。所以这个
--计数器能够给DBA看到一个趋势线，但是值本身是不准确的


--%idle time
--在采样期间，磁盘处于空闲状态的百分比。相对于经常大于100%的%disk time，这个值却是准确的。当
--磁盘处于空闲状态时，他的值是100%。当磁盘在满载做操作时，他的值是0%。所以建议读者通过这个值
--，反推出%disk time的真实值

--disk bytes/sec
--每秒钟磁盘读写的数量，以bytes为单位。他是disk read bytes/sec和disk write bytes/sec的和
--一个很保守的参考值是：
--好：20MB~40MB
--一般：10MB~20MB


--avg.disk sec/transfer
--磁盘每一次读写的动作所花的平均时间


--avg.disk sec/read
--磁盘每一次读所花的平均时间



--avg.disk sec/write
--磁盘每一次写所花的平均时间

--这三个值经常用来衡量磁盘速度：参考值:
--很好：<10MS
--一般：10~20MS
--有点慢：20~50MS
--非常慢：>50MS




--avg.disk queue length
--在某个时间点，磁盘队列的长度，也就是发出的磁盘操作正在等待被磁盘处理的请求数目。
--例如有一个应用发出一条读请求，但是目标磁盘当时正在处理其他任务。那么这个新的读
--请求就会被放在磁盘队列里。这时候磁盘队列的值就是1。理论上讲，这个值不应该长时间地
--大于2

--分析者要综合看这些性能计数器不能从某一个计数器的值确定磁盘有没有瓶颈



--看计数器的值，要看磁盘繁忙的那些时间段，而不是整体时间的平均值。整体时间的平均值
--基本上没什么意义

--案例：7.1
--当SQL在做checkpoint的时候，disk queue是很长的，最长值在40以上。这时候，disk bytes/sec
--的最大值基本是在7MB，而avg.disk sec/write却经常超过50ms。所以这块硬盘的吞吐量是比较低的
--只要用户一直在修改数据库，就需要不停写日志，这个工作量无法减少，要解决问题，一定要解决
--磁盘的写入速度问题




--833错误
--案例：SQL已经向磁盘发出读写请求，但是该请求返回所用时间已超过15秒。就是说，某个磁盘请求读写
--过了15秒还没做完

--SQL对日志文件的操作一般都是小量多次的，不会有超大量的I/O请求。现在居然对日志文件的某个操作
--都花了15秒没有返回，可见磁盘的响应速度一定出了大问题


--I/O问题的SQL内部分析
--（1）从sys.sysprocesses或者sys.dm_os_wait_stats的waittype字段的值
--SQL先从内存里的buffer pool里找页面，如果没找到设置等待状态如下：

--等待状态：pageiolatch_ex(写)或pageiolatch_sh(读)

--然后发起一个异步I/O，将页面读入buffer pool。在I/O没做完之前，连接都会保持在
--pageiolatch_ex(写)或pageiolatch_sh(读)，I/O消耗的时间越长，等待的时间也会越长



--查看所有的等待累计值：
SELECT wait_type,
waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE'pageiolatch%'
ORDER BY wait_type

--各个字段定义分别如下：
--wait_type:等待类型的名称
--waiting_tasks_count:该等待类型的等待数。该计数器在每开始一个等待时便会增加
--wait_time_ms:该等待类型的总等待时间（毫秒）。该时间包括single_wait_time_ms
--max_wait_time_ms:该等待类型的最长等待时间
--single_wait_time_ms:正在等待的线程从收到信号通知到其开始运行时间之间的时差


--所以当pageiolatch_ex(写)或pageiolatch_sh(读)的waiting_tasks_count和wait_time_ms比较高
--的时候，DBA就可以大致估计，SQL经常要等待I/O，可能磁盘速度跟不上SQL的需要

--pageiolatch_ex(写)或pageiolatch_sh(读)针对数据文件的I/O等待

--日志文件的I/O等待是 writelog ,当SQL要写日志文件而磁盘来不及完成时，SQL不得不进入等待状态
--直到日志记录被写入，才能提交当前事务。
--一般一次日志写入数量不会很多，磁盘应当能够按时完成，如果SQL经常要等writelog，说明磁盘的瓶颈
--还是比较严重的

SELECT wait_type,
waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE'writelog'
ORDER BY wait_type




--（2）DBA要搞清楚是哪个数据库哪个文件老做I/O,是数据文件还是日志文件,是读还是写
--动态管理函数
--sys.dm_io_virtual_file_stats({database_id|null},{file_id|null})
--如果参数都是null，就返回所有文件的统计信息
--这个动态管理函数是SQL自上次启动以来的历史统计信息

--字段含义：
--database_id:数据库ID
--file_id:文件的ID
--sample_ms:自从计算机启动以来的毫秒数.可以此列来比较该函数的不同输出
--num_of_reads:对文件发出的读取次数
--num_of_bytes_read:在此文件中读取的总字节数
--io_stall_read_ms:用户等待文件中发出读取所用的总时间
--num_of_writes:在该文件中写入的次数
--num_of_bytes_written:写入文件的总字节数
--io_stall_write_ms:用户等待在该文件中完成写入所用的总时间
--io_stall:用户等待在文件中完成I/O操作所用的总时间
--size_on_disk_bytes:该文件在磁盘上占用的字节数,对于稀疏文件,此数字是数据库快照在磁盘上所占用的实际字节数
--file_handle:用于此文件的Windows文件句柄

--可以通过下面的查询了解哪个文件经常要做读（num_of_reads/num_of_bytes_read），经常要做写
--（num_of_writes/num_of_bytes_written），经常读写要等待（io_stall_write_ms/io_stall_read_ms/io_stall）

SELECT
db.name AS databasename,
f.fileid AS fileid,
f.filename AS FILENAME,
i.num_of_reads AS numofread,
i.num_of_bytes_read AS numofbyteread,
i.io_stall_read_ms AS ioinstallread,
i.num_of_writes AS numofwrite,
i.num_of_bytes_written AS numofbytewrite,
i.io_stall_write_ms AS iostallwrite,
i.io_stall AS iostall,
i.size_on_disk_bytes AS sizeondiskbyte
from sys.databases db INNER JOIN
sys.sysaltfiles f ON db.database_id=f.dbid
INNER JOIN sys.dm_io_virtual_file_stats(NULL,null) i
ON i.database_id=f.dbid AND i.file_id=f.fileid

----------------------------------------------------------------------------------------------------
SELECT
db.name AS databasename,
f.fileid AS fileid,
f.filename AS FILENAME,
i.num_of_reads AS numofread,
i.num_of_bytes_read AS numofbyteread,
i.io_stall_read_ms AS ioinstallread,
i.num_of_writes AS numofwrite,
i.num_of_bytes_written AS numofbytewrite,
i.io_stall_write_ms AS iostallwrite,
i.io_stall AS iostall,
i.size_on_disk_bytes AS sizeondiskbyte
from sys.databases db INNER JOIN
sys.sysaltfiles f ON db.database_id=f.dbid
INNER JOIN sys.dm_io_virtual_file_stats(DB_ID('gposdb'),null) i
ON i.database_id=f.dbid AND i.file_id=f.fileid


--动态管理视图sys.dm_io_pending_io_requests
--当前SQL中每个处于挂起状态的I/O请求
SELECT
database_id AS dbid,
file_id AS fileid,
io_stall AS iostall,
io_pending_ms_ticks AS iopendingmstick,
scheduler_address AS scheduleraddress
from sys.dm_io_virtual_file_stats(NULL,null) as t1,
sys.dm_io_pending_io_requests AS t2
WHERE t1.file_handle=t2.io_handle


--（3）性能监视器
--相关计数器：
--buffer manager---------------------------------------------------------------
--下面的计数器能够反映和buffer pool有关的I/O动作

--page reads/sec和page writes/sec
--反映SQL每秒钟读写了多少页面。可以清楚了解由于buffer pool的行为带来了多少磁盘读写


--lazy writes/sec
--反映lazy writer 为了清空buffer pool每秒钟做了多少页面写入动作

--checkpoint writes/sec
--每秒钟从buffer pool里写入到磁盘上的dirty page数目

--readahead pages/sec
--每秒钟SQL做的预读read ahead数目


--access methods-----------------------------------------------------------------------------------
--除了读取和写入指令需要访问的页面，SQL还要做一些辅助工作，帮助指令完成，这些工作也会带来I/O动作

--freespace scans/sec
--在堆heap 结构里找到能够使用的空间。对没有聚集索引的表，SQL会以堆的形式存储。如果这个计数器很高
--说明SQL在堆的管理上花费很多资源，应该考虑多建聚集索引

--page splits/sec
--当表上有很多插入动作时，一些页面会被放满。为了维护索引上的顺序，SQL需要把一页劈成两页，这个动作叫
--“page split”。当数据库的修改比较多，尤其是插入比较多时，page split是难免的。如果这个值比较高，而你
--又觉得他的确对性能有影响，可以考虑定期重建索引，使用比较小的填充值(fill factor)

--page allocations/sec
--当SQL需要创建对象，例如：表，索引时，分配给新对象的页面数量

--workfiles/sec
--当SQL为了完成某些操作而在内存中建立一个hash结构时（例如用hash算法作join）
--该计数器就加一。如果某些hash结构比较大，SQL可能会将一部分数据写到硬盘里。
--所以这个值其实并不直接意味着磁盘读写。但是由于SQL通常只是在没有合适索引的时候才选择hash算法
--所以DBA可以通过这个值了解数据库索引是不是有优化的必要。很多情况下索引优化能够大大降低SQL的读
--数量。做I/O问题时，这个计数器有必要看一下

--worktables/sec
--每秒创建的工作表数。例如，工作表可用于存储查询假脱机（query spool），LOB变量，XML变量，表变量，
--游标的临时结果

--full scans/sec
--每秒钟SQL做的全表扫描数目。如果一个表设计良好，就应该没有很多的全表扫描。而全表扫描通常意味着
--比较大的内存使用和比较多的I/O请求。所以在一个SQL系统，尤其是一个OLTP系统，这个值越小越好

--index searches/sec
--每秒钟检索索引的次数，也就是利用索引完成指令的数目

--database(log activity)--------------------------------------------------------------------------
--在这个计数器下，我们能看到一些和日志写入有关的计数器

--log flushes/sec
--SQL每秒钟在这个数据库上做的日志写的次数

--log bytes flushed/sec
--SQL每秒钟在这个数据库上做的日志写的数量（bytes）

--log flush wait time
--写入日志的动作曾经因为磁盘来不及响应而遇到的等待时间。这种等待会导致前端事务不能提交，所以会
--严重影响SQL的性能。正常情况下，这个值大多数时间都是0

--log flush waits/sec
--在每秒钟提交的事务里，有多深个事务曾经等待过日志写入完成。正常情况下，不应该有等待，如果有等待
--检查存放日志文件的磁盘性能

--两个计数器，粗略分析SQL的整体性能
--MSSQL:SQL Statistics -Batch Request/sec :SQL每秒钟完成的批处理数目
--MSSQL:Databases Active Transactions:SQL里打开的，还没有提交的事务数目



-------------硬盘压力测试------------------------------------------------------------------------------------
--SAN(storage area network),raid这样的存储技术在SQL服务器上已经被广泛使用

--关于测试
--1、不同类型的磁盘读写操作，每次读写transfer的数据量会不一样。而对于磁盘来讲，每秒钟做的读写量不但跟完成
--的读写次数有关，还和每次读写的数据量有很大关系。SQL不同的操作，会有不同的读写量。像index seek这样的
--操作，可能以8KB为单位比较多；像read ahead,table scan这样的动作，可能64KB甚至更大的单位更多。
--所以管理员要测试磁盘在不同的transfer size情况下，他的表现是不是都很好

--2、有些磁盘的读写速度并不一样。例如：raid5，他的写会比读要慢。所以读和写的速度都要测试到

--3、现在的SAN和其他物理存储设备会有很大的读写缓存，类似于服务器上的内存。如果每次读写的量比较小
--读写缓存就可以处理了，可能不会发生真正到存储介质上的读写。这样的速度，会比真正读写速度快很多。
--所以在测试时，一定要保证读写数量是读写缓存大小的2到4倍

--4、SQL的不同数据文件，可以放在不同的磁盘上。SQL的读写工作可以由若干个磁盘共同完成。整体的速度
--会有所提升。但是提升多少呢？做硬盘压力测试的时候，要先测试单个磁盘的速度，再测多个磁盘共同工作
--时的速度

--5、有些磁盘如果工作压力一直比较大，会调整他的行为来适应。所以压力测试一定要持续一段时间。对于一个
--初步测试，5到10分钟的满负荷运作是至少的


--SQLIO工具------------------------------------------------------------------------------------------
--虽然叫SQLIO，但是这个工具跟SQL没有什么直接关系，可以在任何一台SQL的机器上运行

--功能：
--能够指定压力测试是读还是写
--指定每次是连续的读/写，还是随机的读/写
--指定每次读/写的大小
--指定每个测试的时间长短
--指定读/写的磁盘位置，路径和文件大小。支持多个磁盘同时读/写测试

--两个文件：
--param.txt
--sqlio.exe
--运行参数：
--  -o:测试文件在磁盘上产生的队列长度disk queue length
--  -LS:记录磁盘反应时间，这是一个推荐使用的选项
--   -k:指定是读还是写（R\W）
--   -s:测试持续多少秒，一般至少5到10分钟
--    -b:每次I/O请求的大小
--    -f:I/O的种类，是随机random  还是连续 sequential
--    -F:参数文件的名字，默认就是param.txt

 

整数/浮点数转字符串，下面三个函数都是等价的

SELECT '{'+STR(2)+'}'
SELECT '{'+CAST(2 AS VARCHAR(10))+'}'
SELECT '{'+CONVERT(varchar(10),2)+'}'

 

f