SQL Server查询优化(细化到数据库/文件级别)

细化到数据库/文件级别

优化处理的下一步是细化到数据库/文件级别。这一步要找出哪些数据库占用了大部分等待开销。在数据库内，还需要再细化到文件类型（数据/日志），因为文件的类型决定了要采取的行动方案。在数据库/文件级别分析I/O信息的一种工具是动态管理函数dm_io_virtual_file_stats。这个函数接收一个数据库ID和文件ID作为输入参数。返回与该文件相关的I/O信息。如果将这两个指定为NULL，则返回所有的信息。

-- 分析数据库I/O

WITH DBIO AS

(

  SELECT

    DB_NAME(IVFS.database_id) AS db,

    MF.type_desc,

    SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io_bytes,

    SUM(IVFS.io_stall) AS io_stall_ms

  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS

    JOIN sys.master_files AS MF

      ON IVFS.database_id = MF.database_id

      AND IVFS.file_id = MF.file_id

  GROUP BY DB_NAME(IVFS.database_id), MF.type_desc

)

SELECT db, type_desc,

  CAST(1. * io_bytes / (1024 * 1024) AS NUMERIC(12, 2)) AS io_mb,

  CAST(io_stall_ms / 1000. AS NUMERIC(12, 2)) AS io_stall_s,

  CAST(100. * io_stall_ms / SUM(io_stall_ms) OVER()

       AS NUMERIC(10, 2)) AS io_stall_pct,

  ROW_NUMBER() OVER(ORDER BY io_stall_ms DESC) AS rn

FROM DBIO

ORDER BY io_stall_ms DESC;

结果输出了数据库名称，文件类型，以兆字节为单位的总I/O，以秒为单位的I/O等待时间，每个I/O等待时间占整个系统总等待时间的百分比，以及按I/O等待时间排序的行号。

假如数据库日志出现了较高的等待，你首先应该检查日志的配置是否合理，也就是说，把它放到不受干扰的磁盘驱动器上。记住数据日志是按照顺序写入的，所以把它分到多个磁盘上并没有什么帮助。对于导致密集日志操作的进程，可以通过减少日志操作量来优化这些进程。

至于tempdb，有许多操作都可能会给它带来压力，让它成为系统的严重瓶颈。