SQL Server下如何测试存储性能

背景

         随着数据量的增长，会发现系统使用越来越慢，并且可能是突然变慢。大部分情况是因为随着数据量增长，SQL语句读取的数据页变多，当读取的数据页超过磁盘IO的能力后，语句执行效率将带来明显的下降。

         这篇文章的目的，主要是从数据库的角度来计算磁盘的吞吐能力以及在这种吞吐能力下，读取一定数据需要耗费的时间。

环境准备

         打开SQL Server MSSM连接需要测试的库，打开查询分析器，执行下面SQL

         checkpoint  --将缓存中的数据页持久化

         dbcc dropcleanbuffers     --释放缓存区的缓存，确保查询时从存储获取数据

         set statistics io on              --打开IO的统计信息

         set statistics time on        --打开耗时的统计信息

测试步骤

l  获取最大的数据表(前提表定期做了统计信息更新)

        select top 1 object_name(id),rows from sysindexes order by rows desc

        --查询的数据库得到最大表为T_BF_INSTAMOUNTBACKUP

l  读取整表的数据

        select count(*) from T_BF_INSTAMOUNTBACKUP with (index(0))

        -- with (index(0))，表存在聚集索引，那么需要强制使用聚集索引扫描，才能读取到所有的数据页 

l  执行后统计信息结果

    SQL Server 分析和编译时间:

       CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。

    (1 行受影响)

    表 'T_BF_INSTAMOUNTBACKUP'。扫描计数 1，逻辑读取 1723881 次，物理读取 0 次，预读 1717492 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

    --说明逻辑读，预读和物理读的次数，每次大小8KB，因为数据库的每页大小为8KB

     SQL Server 执行时间:

       CPU 时间 = 19203 毫秒，占用时间 = 20403 毫秒。

        --说明SQL语句耗用时间为20403 毫秒

        SQL Server 分析和编译时间:

       CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

     SQL Server 执行时间:

       CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

    完成时间: 2021-06-21T10: