很多同学遇到 SQL 性能问题,要么毫无头绪干着急,要么手忙脚乱瞎忙活。
可谁不是从小工到大师呢,关键是要找到那条道。
如果你也在寻找这条道,不妨一起来看看这里。
用好三张 DMV, 检测 80% 的性能故障。
-sys.dm_os_wait_stats
-sys.dm_io_virtual_file_stats(NULL,NULL)
-sys.dm_os_performance_counters
前面有两篇文章已经对 sys.dm_os_wait_stats 做了比较详细的描述与扩展,贴在这里,有兴趣再回顾回顾!
揭秘数据库性能杀手 - 等待
揭秘数据库性能杀手 - 等待(赋脚本)
通过上面的两篇文章,大家可以穷尽自己的想象力,多做一些主题分析,相信能掌握不少有趣有用的诊断技术。
接下来我们来看另外两张 DMV,其中一张是个表级函数 sys.dm_io_virtual_file_stats
通过 sys.dm_io_virtual_file_stats可以看到数据库文件级别的 IO 情况:
-某一段时间内的写和读的量,大小变化,异常峰值;
-磁盘的写和读延迟;读写延迟严重的,就需要配合performance counter的趋势线,判断读写延迟是否是因为整个操作系统的作业量过大造成的
*延迟有个标准可以参考:
小于 10ms, 非常好;
在 10 - 20 ms 之间,正常;
在 20 - 50 ms 之间,异常,需要注意
大于 50 ms, 严重的 I/O 瓶颈
(来源于 MSDN)*
大家看到这里一定会问这么个问题,如果系统有严重的延迟,我该怎么办呢?
-可以将服务器上多余的其他进程移走;
-将严重延迟的数据文件,迁移到更快速度的 SSD 上
-精简索引和调优查询计划。精简索引是为了更新快,调优则是为了查询更快。少则快的原则在OLTP 上通用。
以下是创建性能数据采集表的脚本,依然是 5 分钟一次的采集:
CREATE TABLE T_GLOBAL_VIRTUAL_FILE_HIST(
ID BIGINT IDENTITY(1,1)
, AUDIT_DATETIME DATETIME
, DATABASE_NAME VARCHAR(200)
, DATABASE_FILE_NAME VARCHAR(200)
, DATABASE_FILE_ID INT
, DATABASE_FILE_DESC VARCHAR(15)
, DATABASE_FILE_PHYSICAL_NAME VARCHAR(500)
, NUM_OF_READS BIGINT
, NUM_OF_BYTES_READ BIGINT
, NUM_OF_WRITES BIGINT
, NUM_OF_BYTES_WRITTEN BIGINT
, IO_STALL_READ_MS BIGINT
, IO_STALL_WRITE_MS BIGINT
, IO_STALL BIGINT
)
DECLARE @INC_TIME_PERIOD INT = 5 ;
DECLARE @WAIT_DELAY VARCHAR(20) = '' ;
SET @WAIT_DELAY = LEFT( '00:' + RIGHT('00'+CONVERT(VARCHAR,@INC_TIME_PERIOD),2),5 )
WHILE(EXISTS(SELECT * FROM T_GLOBAL_WAIT_COLLECT_PROGRESS_CONTROL WHERE ENABLED_FLAG=1))
BEGIN
INSERT INTO T_GLOBAL_VIRTUAL_FILE_HIST (
AUDIT_DATETIME
, DATABASE_NAME
, DATABASE_FILE_NAME
, DATABASE_FILE_ID
, DATABASE_FILE_DESC
, DATABASE_FILE_PHYSICAL_NAME
, NUM_OF_READS
, NUM_OF_BYTES_READ
, NUM_OF_WRITES
, NUM_OF_BYTES_WRITTEN
, IO_STALL_READ_MS
, IO_STALL_WRITE_MS
, IO_STALL
)
SELECT GETUTCDATE() AS AUDIT_DATETIME
, DB_NAME(dbf.database_id) AS database_name
, dbf.name as database_file_name
, dbf.file_id as database_file_id
, dbf.type_desc as database_file_desc
, dbf.physical_name as database_file_physical_name
, vfs.num_of_reads
, vfs.num_of_bytes_read
, vfs.num_of_writes
, vfs.num_of_bytes_written
, vfs.io_stall_read_ms
, vfs.io_stall_write_ms
, vfs.io_stall
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
INNER JOIN sys.master_files dbf ON vfs.database_id = dbf.database_id
and vfs.FILE_ID = dbf.file_id
ORDER BY database_name,database_file_desc ,database_file_id
WAITFOR DELAY @WAIT_DELAY ;
END
上面两图基于下面的 SQL 完成。
这里有个 IO Stall 的概念,就是等待 IO 完成的一个等待状态。
; WITH BASE_QUERY AS (
SELECT AUDIT_DATETIME, ROW_NUMBER() OVER ( ORDER BY AUDIT_DATETIME) AS RNK
FROM (SELECT DISTINCT AUDIT_DATETIME FROM T_GLOBAL_VIRTUAL_FILE_HIST WITH(NOLOCK) ) TMP
)
, CAL_QUERY_CURRENT AS ( SELECT HIS.AUDIT_DATETIME
, HIS.DATABASE_NAME
, HIS.DATABASE_FILE_ID
, HIS.DATABASE_FILE_DESC
, HIS.DATABASE_FILE_PHYSICAL_NAME
, HIS.IO_STALL_READ_MS
, HIS.IO_STALL_READ_MS / NULLIF(HIS.NUM_OF_READS,0) AS AVG_STALL_RAED_MS
, HIS.DATABASE_FILE_NAME
, BQ.RNK
FROM T_GLOBAL_VIRTUAL_FILE_HIST HIS
INNER JOIN BASE_QUERY BQ ON HIS.AUDIT_DATETIME = BQ.AUDIT_DATETIME
WHERE HIS.IO_STALL_READ_MS IS NOT NULL
)
SELECT * FROM CAL_QUERY_CURRENT
上面两图基于下面的 SQL 完成。
这里可以看到日志是写比较频繁的一类文件,所以存放他们的物理磁盘一定要够快。
我们这里显示的是开发库,4G 左右的数据用了 1 小时沾满。
; WITH BASE_QUERY AS (
SELECT AUDIT_DATETIME, ROW_NUMBER() OVER ( ORDER BY AUDIT_DATETIME) AS RNK
FROM (SELECT DISTINCT AUDIT_DATETIME FROM T_GLOBAL_VIRTUAL_FILE_HIST WITH(NOLOCK) ) TMP
)
, CAL_QUERY_CURRENT AS ( SELECT HIS.AUDIT_DATETIME
, HIS.DATABASE_NAME
, HIS.DATABASE_FILE_ID
, HIS.DATABASE_FILE_DESC
, HIS.DATABASE_FILE_PHYSICAL_NAME
, HIS.NUM_OF_READS
, HIS.NUM_OF_BYTES_READ / 1024 /1024 /1024 AS NUM_OF_BYTES_READ
, HIS.NUM_OF_BYTES_READ / 1024 / NULLIF(NUM_OF_READS,0) AS AVG_OF_BYTES_READ
, HIS.NUM_OF_WRITES
, HIS.NUM_OF_BYTES_WRITTEN /1024 /1024 /1024 AS NUM_OF_BYTES_WRITTEN
, HIS.NUM_OF_BYTES_WRITTEN /1024 / NULLIF(HIS.NUM_OF_WRITES,0) AS AVG_OF_BYTEST_WRITTEN
, HIS.DATABASE_FILE_NAME
, BQ.RNK
FROM T_GLOBAL_VIRTUAL_FILE_HIST HIS
INNER JOIN BASE_QUERY BQ ON HIS.AUDIT_DATETIME = BQ.AUDIT_DATETIME
)
SELECT * FROM CAL_QUERY_CURRENT
通过 sys.dm_os_performance_counter 能看到 windows performance monitor 能看到的有关SQL Server 进程使用资源的信息。关于他的应用,下回分解!