背景

         随着数据量的增长，会发现系统使用越来越慢，并且可能是突然变慢。大部分情况是因为随着数据量增长，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:45:31.4168963+08:00

l  计算吞吐量

        公式：8（KB/页）*(物理读取+预读)/( 占用时间)

         select 8*(1717492+0)/20403 = 673MB/s

        --因为换算后的单位为秒，而占用时间单位为毫秒，所以结果按秒的话为MB

        说明当前磁盘的吞吐量，每秒为673MB

l  计算耗时

        知道吞吐量后，就可以估算读取一定数据量需要的时间

        公式：表数据大小/读取速率

        获取表大小，可以看到数据占大小为13739936 KB   

sp_spaceused T_BF_INSTAMOUNTBACKUP

name                                                           rows                                              reserved                        data                                 index_size        unused

T_BF_INSTAMOUNTBACKUP             156756738                 20682056 KB                13739936 KB                6942000 KB     120 KB

       所以读取13739936 KB需要的时间为:

        select 13739936/1024.0/673=19.9374535661

        --即读取13739936KB，在磁盘读取速率每秒为673MB下，需要19.9秒的时间