4 .2 .4 配置存储系统

4 .2 .4 配置存储系统
在考虑存储系统时，数量、大小、速度及阵列情况都是需要纳人考虑范围内的，并且
需要根据工作负载而定。当 SQL Server所需的数据不在内存的Buffer cache中时，就会读 取磁盘，引起磁盘I/O。当数据修改时，事务也会写到事务日志文件中，然后写人数据文件
中，这也会引起磁盘I/O。除了应用程序日常操作引起的I/O以外，还有以下操作也会引起
额外的I/O。
□ 日志备份：会引起读写I/O压力，因为需要读取事务日志文件中的信息，然后写入
日志备份文件中。
□索引维护：包括重组和重建，这个操作非常消耗1/0、内存、CPU资源。
□全文目录和全文索引：“天生”就是高读写I/O的操作。
□数据库Checkpoint操作：会引发脏页写进数据文件的操作。 □髙可用：绝大部分高可用都借助同步日志实现，对日志的读写会引起磁盘I/O。
在OLTP中，天生就具有高IOPS及写操作。对于这类负载，最起码的合理配置应该是，
把数据文件、日志文件及TempDB的文件分到不同物理磁盘存放，从而分摊对文件的I/O争用。 对于OLAP,事务运行时间长、规模大，数据相对静态，对磁盘的IOPS要求较低，但
是对I/O吞吐量的要求较髙，因为每次返回的数据量相对较大。并且这种负载中索引维护、
日志备份等操作较少，所以应该针对I/O吞吐量进行优化。
4 .2 .5 检查读写速率
除了前期规划，有时候需要对当前已经在使用的系统进行检查。下面的脚本可以运行
在当前系统，并查看当前数据库文件中的IO情况。
— 当前数据库文件的I /O 统计信息

SELECT DB_NAME(vfs.database_id) AS database_name ,
vfs.database_id ,
vfs.FILE_ID ,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency ,
io_stall / NULLIF(num_of_reads + num_of_writes, 0) AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads, 0) AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes, 0) AS avg_bytes_per_write ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS [size_on_disk_mbytes(MB)] ,
mf.physical_name
FROM sys.dm_io_virtual_file_stats(DB_ID('master'), 1) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID

 

除了这个脚本，还有其他3个脚本可用于检査当前系统的读写情况，如代码清单
4-1〜 4-3所示 ）。
代码清单4 -1 计划缓存中存储过程的逻辑写排名
--计划缓存中存储过程的逻辑写排名(SQL 2008 and 2008 R 2 适 用 }，主要是写操作的压力
SELECT p.name AS [SP N a m e ] , qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_reads AS [TotalLogicalReads], qs.execution_count , qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_jprocedure_stats AS qs ON p.[object_id] = qs. [object_
id]
WHERE qs.database_id = DB_ID()AND qs.total_logical_writes > 0 ORDER BY qs.total_logical_writes DESC;
代码清单4 - 2 计划缓存中存储过程的物理读排名
--计划缓存中存储过程的物理读排名（SQL 2008 and 2008 R 2 适用} , 主要是读操作的压力
SELECT p.name AS [SP Name] • qs.total_physical_reads AS [TotalPhysicalReads], qs.total_logical_reads AS [TotalLogicalReads], qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads], qs .execution_count , qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_
id]
WHERE qs.database_id = DB_ID() AND qs.total_physical_reads > 0

代码清单4 - 3 计划缓存中存储过程的逻辑写排名
--计划缓存中存储过程的逻辑写排名(SQL 2008 and 2008 R 2 适用） ，主要是读操作的压力，并且在 内存中
SELECT p.name AS [SP N a m e ] , qs.total_logical_reads AS [TotalLogicalReads] , qs.total_logical_wri tes AS [TotalLocicalWrites], qs.execution_count , qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_
id]
WHERE qs.database_id = DB_ID() AND qs.total_logical_reads > 0 ORDER BY qs.total_logical_reads DESC;

具有高度写操作的负载，应该与日志文件分开存放，否则会给数据文件及日志文件带
来I/O压力。对于大数据量的库，通常会使用多个数据文件分别存放数据，以减少I/O争
用。分开文件的原因之一是数据文件和日志文件的行为不一样，特别是日志文件的顺序读
写，如果与数据文件放在一起，会导致磁头的来回移动，降低I/O性能。上面的脚本在一
定程度上也和RAID配置有关，对于OLTP,写性能一般比读性能重要；而对于OLAP系
统，读性能往往比写性能重要。