尧图网页设计 
1. 为什么需要统计每张表的数据量我遇到的几个真实场景干SQL Server运维和开发这几年统计每张表的数据量这个需求几乎是隔三差五就会出现一次。听起来很简单但真操作起来会发现一套顺手、可靠、还能反复用的方法并不那么容易凑齐。先说说我实际碰到过的场景你大概也能对号入座第一个场景是数据迁移评估。有一次要把一套老业务系统从SQL Server 2008 R2迁到SQL Server 2019用的还是高版本兼容模式。对方只给了一句库大概有30个G但迁移方案里必须知道哪几张表占了大头因为要决定哪些表可以延迟迁移、哪些表需要并行迁移、是否要做数据压缩。没有一张表行数排行榜方案根本没法细化。第二个场景是容量规划。那时候负责的库里有一张日志表业务方说数据增长很快。到底多快一周涨多少行每天涨多少只有把历史数据量的快照记录下来看到一条陡峭的增长曲线才能说服业务方做归档策略。不然光靠嘴说这表很大一点说服力都没有。第三个场景是性能问题排查。有张业务表查询很慢第一反应是缺索引。但在加索引前我得先知道这张表到底多大——是百万级、千万级还是亿级。不同量级的优化思路完全不一样百万级可能就是缺个好索引亿级就得考虑分区、归档甚至换存储方案。这步判断错了后面全白干。第四个场景是莫名其妙的数据量对不上。周期性的数据同步任务跑完业务方说源表有100万行目标表只有80万行。你需要快速统计出所有表的行数做对比定位到底哪几张表对不上。如果还是手工一条一条sp_spaceused去执行那真能查到怀疑人生。所以把查看每张表的数据量这件事做成一套脚本、弄明白每一种统计方式的原理、知道哪些数字能信哪些数字只能做参考是每个SQL Server从业者绕不开的基本功。这篇文章就把我的做法、踩过的坑、以及最后沉淀下来的模板都整理出来按我的实际使用经验从头到尾讲一遍。2. 三种主流统计手段的对比不是每个行数都可信先讲核心的统计手段。市面上常见的无非是三种系统存储过程sp_spaceused、系统视图sys.dm_db_partition_stats、以及老旧的sysindexes。我建议你先把它们各自的脾气摸清楚再决定用哪套方案。2.1 sp_spaceused最直观但第一次执行有隐藏成本-- 查看当前库所有表的汇总 sp_spaceused; -- 查看单张表不更新统计信息 sp_spaceused NUserTable; -- 查看单张表强制更新统计信息 sp_spaceused NUserTable, updateusage NTRUE;sp_spaceused返回的列有name表名、rows行数、reserved保留空间、data数据空间、index_size索引空间、unused未用空间。最坑的一点就在这个updateusage参数上。不带这个参数的sp_spaceused其实读的是系统表里缓存的统计信息大多数情况下没有问题。但如果统计信息过期你得到的行数可能是老数据。而一旦你加了TRUESQL Server就会执行DBCC UPDATEUSAGE对这张表做完整扫描来重新统计——小表感觉不到几千万行的大表动辄跑几分钟甚至更久而且会占用大量IO和CPU。我第一次跑DBCC UPDATEUSAGE扫了一个2.3亿行的表跑了大概七分钟期间库整体IO明显飙升监控直接告警。从那之后我就立了一个规矩生产环境绝不轻易给sp_spaceused加UPDATEUSAGE参数除非是在维护窗口或凌晨低峰期。2.2 sys.dm_db_partition_stats速度快且适合批量这个DMV是SQL Server 2005之后引入的它直接访问分区级别的统计信息不需要触发任何扫描所以批量统计几百张表时非常快。SELECT t.name AS TableName, SUM(p.rows) AS RowCounts FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id p.object_id WHERE t.is_ms_shipped 0 AND p.index_id IN (0, 1) -- 0堆1聚集索引 GROUP BY t.name ORDER BY RowCounts DESC;这里有个非常关键的地方p.index_id IN (0, 1)。如果把这行去掉你会看到很多表的行数突然翻了几倍甚至几十倍其实那不是真实行数而是把这张表上所有非聚集索引的分区行数也累加进来了。我后面会专门讲这个坑。2.3 sysindexes老掉牙但有些历史脚本里还在用SELECT t.name AS TableName, MAX(si.rows) AS RowCounts FROM sys.tables t INNER JOIN sys.sysindexes si ON t.object_id si.id WHERE t.is_ms_shipped 0 AND si.indid IN (0, 1) GROUP BY t.name ORDER BY RowCounts DESC;sysindexes在SQL Server 2000时代几乎是最常用的方式。但它的问题也很明显rows字段容易失效尤其在大量增删改后不自动更新而且微软在新版本里已经不再保证这个视图的行为。我只建议在兼容老脚本或者实在没别的办法时才看一眼新开发的脚本不要走这条路。2.4 三种方式横向对比方式是否触发扫描批量统计速度数据准确性适用场景sp_spaceused不带参数否一般依赖统计信息单表快速查看sp_spaceused带UPDATEUSAGE是慢高维护窗口校准sys.dm_db_partition_stats否快高近似实时批量统计首选sysindexes否快可能过期老脚本兼容我个人的结论很明确日常批量统计用sys.dm_db_partition_stats单表查看图省事用sp_spaceused不带参数。只有在做一次彻底的对账时才挑凌晨窗口对重点大表跑DBCC UPDATEUSAGE。3. 一个能直接用的批量统计脚本覆盖所有用户表光会单表查询还不够真正的需求往往是一口气列出一个库里所有表的数据量。我来分享一下我最终沉淀下来的脚本模板以及写这套脚本时踩过的一些细节坑。3.1 基础版临时表 动态SQL思路很简单先拿到所有用户表名字然后逐条拼接sp_spaceused的执行语句结果集中收集到一张临时表里。IF OBJECT_ID(tempdb..#TableSpace) IS NOT NULL DROP TABLE #TableSpace; CREATE TABLE #TableSpace ( TableName NVARCHAR(128), Rows BIGINT, Reserved NVARCHAR(50), Data NVARCHAR(50), IndexSize NVARCHAR(50), Unused NVARCHAR(50) ); DECLARE sql NVARCHAR(MAX) N; SELECT sql sql NINSERT INTO #TableSpace EXEC sp_spaceused QUOTENAME(name) N; FROM sys.tables WHERE is_ms_shipped 0; EXEC sp_executesql sql; SELECT TableName, Rows, Reserved, Data, IndexSize, Unused FROM #TableSpace ORDER BY CAST(REPLACE(Reserved, KB, ) AS BIGINT) DESC;这个脚本有两个细节需要注意。细节一QUOTENAME的作用。如果表名是Order Details这种带空格的名字直接用sp_spaceused Order Details会报错因为中间的空格把表名拆成了两个标识符。QUOTENAME会用方括号把名字包起来变成[Order Details]这样就安全了。这是我实际踩过的坑——有张表叫User Data第一次写脚本时没注意直接报语法错误。细节二Reserved字段是字符串。它返回的是12345 KB这种格式如果你想要按空间大小排序直接ORDER BY Reserved会变成按字母序排结果就是999 KB排在100 KB前面完全不对。所以排序前要把单位去掉转成数字。3.2 进阶版加上行数、空间大小、建表时间的完整统计基础版够用了但很多时候光有空间不够我还想把表的基本信息一起输出。所以后来我把脚本改成了这样IF OBJECT_ID(tempdb..#TableStats) IS NOT NULL DROP TABLE #TableStats; CREATE TABLE #TableStats ( SchemaName NVARCHAR(128), TableName NVARCHAR(128), RowCounts BIGINT, TotalSpaceKB BIGINT, UsedSpaceKB BIGINT, IndexSpaceKB BIGINT, CreateDate DATETIME, ModifyDate DATETIME ); INSERT INTO #TableStats ( SchemaName, TableName, RowCounts, TotalSpaceKB, UsedSpaceKB, IndexSpaceKB, CreateDate, ModifyDate ) SELECT s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS RowCounts, CAST(SUM(a.total_pages) * 8 AS BIGINT) AS TotalSpaceKB, CAST(SUM(a.used_pages) * 8 AS BIGINT) AS UsedSpaceKB, CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS BIGINT) AS IndexSpaceKB, t.create_date, t.modify_date FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.indexes i ON t.object_id i.object_id INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0 AND i.index_id IN (0, 1) GROUP BY s.name, t.name, t.create_date, t.modify_date ORDER BY RowCounts DESC;这一版直接查的是分配单元allocation_units算出来的空间大小是按页数乘以8KB得出的比解析sp_spaceused的字符串要干净得多。这里有个地方需要说明SUM(a.total_pages) * 8是按页算KB。SQL Server的页大小固定是8KB一个区extent是8页共64KB所以不论表是否压缩这个计算都能给出准确的空间占用。数据压缩只会影响实际存储的行数行可以变小、一页装更多行不会改变页的物理大小所以这个算法永远成立。3.3 输出方式怎么把结果攒成一张排行榜统计结果攒到临时表后最终查询建议按场景输出。如果是想快速看到行数最高的表SELECT TOP 20 SchemaName, TableName, RowCounts, TotalSpaceKB / 1024 AS TotalSpaceMB FROM #TableStats ORDER BY RowCounts DESC;如果是想看空间占用最大的表SELECT TOP 20 SchemaName, TableName, RowCounts, TotalSpaceKB / 1024 AS TotalSpaceMB, UsedSpaceKB / 1024 AS UsedSpaceMB, IndexSpaceKB / 1024 AS IndexSpaceMB FROM #TableStats ORDER BY TotalSpaceKB DESC;如果你需要横向对比数据规模和空间规模之间的关系这张临时表都能直接支撑。我一般会在最后加一个汇总SELECT COUNT(*) AS TotalTableCount, SUM(RowCounts) AS TotalRowCount, SUM(TotalSpaceKB) / 1024 AS TotalSpaceMB, SUM(UsedSpaceKB) / 1024 AS UsedSpaceMB FROM #TableStats;这样一跑整库的家底就清楚了多少张表、总行数多少、总占用多少空间。4. 统计数字不等于真实行数深入解析两个容易翻车的根因用sys.dm_db_partition_stats这种方式统计出来的行数我每次都会跟业务方强调这个数字是系统当前记录的统计值不是COUNT(*)实时数出来的精确值。两者大多数时候一致但在两种特定场景下会不一致提前知道能帮你省掉很多不必要的解释和返工。4.1 根因一非聚集索引导致的行数虚高这是新手最容易踩的坑。假设你的表有一个主键聚集索引和三个非聚集索引那么这张表在sys.partitions里会有四条记录index_id 0堆没有聚集索引时的数据存储index_id 1聚集索引index_id 2第一个非聚集索引index_id 3第二个非聚集索引index_id 4第三个非聚集索引如果你查询时没有过滤index_id只写了SUM(p.rows)那么这张表的行数会被算成四遍。我亲眼见过一个同事把一张40万行的表统计出160万行后来一查才发现是索引累加的原因。所以统一规则就是统计表行数只看index_id IN (0, 1)也就是堆或聚集索引。非聚集索引的行数只用来做索引大小评估不做表行数统计。4.2 根因二统计信息不是实时值SQL Server为了性能不会在每次INSERT/UPDATE/DELETE后都实时更新所有统计信息。sys.dm_db_partition_stats里的rows其实来自分区元数据它跟统计信息的刷新频率有一定关系。在大量增删操作后、统计信息未自动更新时你可能会看到一个过期的行数。举个例子你刚批量删除了300万行再去查sys.dm_db_partition_stats可能看到的还是删除前的行数。等一段时间或者手动更新统计信息后才会纠正。这就是为什么在做精确对账、数据校验的时候不能只依赖系统视图还是得用COUNT(*)。SELECT COUNT(*) FROM dbo.TargetTable;对超大表跑这个肯定有代价但它能给你一个100%准确的实时行数。如果只是日常巡检系统视图片偏低一点高一点问题不大如果是要跟业务方数人头那必须用COUNT(*)口径。4.3 什么时候需要DBCC UPDATEUSAGEDBCC UPDATEUSAGE存在的意义就是校准这些元数据。-- 校准当前库所有表的空间和行数统计 DBCC UPDATEUSAGE(YourDatabaseName); -- 只校准一张表 DBCC UPDATEUSAGE(YourDatabaseName, dbo.TargetTable);它本质上做的事情就是遍历表和索引的页数纠正sys.partitions和sys.allocation_units里的缓存值。代价是按页扫描表越大跑得越久。我的经验是不要对全库跑只对怀疑不准确的特定大表跑。而且一定安排在凌晨维护窗口跑完立刻做一次快照统计。如果你发现某张表的行数一直在飘怀疑系统统计出了问题这时候DBCC UPDATEUSAGE才是对症下药。4.4 行数口径开发、测试、生产各用什么环境推荐口径原因开发/测试sys.dm_db_partition_stats快、方便、没有性能压力生产日常巡检sys.dm_db_partition_stats不触发扫描对业务影响小生产精确对账COUNT(*)只信任实时计数生产月度校准DBCC UPDATEUSAGE 快照纠正元数据偏差如果你能把这套口径讲给团队听清楚很多为什么数据量对不上为什么统计出来翻倍的争论就能提前避免。5. 总数据量怎么算行数总和与磁盘空间是两个概念总数据量这句话在业务沟通里有歧义——有人问的是总行数有人问的是磁盘占用空间还有人两个都要。所以我在做统计表的时候会同时输出行数和空间两个维度并区分清楚。5.1 总行数多少张表加起来有多少行SELECT COUNT(DISTINCT t.object_id) AS TableCount, SUM(p.rows) AS TotalRows FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id p.object_id WHERE t.is_ms_shipped 0 AND p.index_id IN (0, 1);注意我用了COUNT(DISTINCT t.object_id)而不是COUNT(*)。因为一张表如果有多个分区或者多个索引sys.partitions里就会有多个行直接COUNT(*)会把表数量也算重复。这一点对分区的表尤其明显。5.2 看总体的空间占用数数据量更要数页有一种常见误区是把行数等同于数据量。但真实场景里行数少的表完全可能占用更大的磁盘空间——因为每一行有变长字段、有索引、有碎片。所以空间维度的统计一定不能省。SELECT CAST(SUM(a.total_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS TotalSizeMB, CAST(SUM(a.used_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS UsedSizeMB, CAST(SUM(a.total_pages) * 8 / 1024.0 / 1024 AS DECIMAL(18, 2)) AS TotalSizeGB, CAST(SUM(a.used_pages) * 8 / 1024.0 / 1024 AS DECIMAL(18, 2)) AS UsedSizeGB FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id i.object_id INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0;这里有两点要提醒第一total_pages包含表和索引的所有页。它等于数据页索引页未使用的保留页。used_pages只包括实际使用了的数据页和索引页。两者之差就是碎片和预留空间。如果你的库碎片率很高你会发现total_pages明显比used_pages大不少这正是需要重建或重组索引的信号。第二这个统计不包含日志文件.ldf和tempdb。这两个文件的大小和增长情况需要用DBCC SQLPERF和sys.database_files才能查到。如果有人跟你说数据库占了200G你要分辨清楚他是说数据文件还是数据文件日志文件。这个口径差很大——我遇到过日志文件占了一大半的情况。5.3 数据库整体大小文件维度的统计法除了逻辑上的表数据量物理文件的大小也建议定期掌握。一条SQL就能看全当前库的所有文件SELECT name AS FileName, type_desc AS FileType, size * 8 / 1024 AS SizeMB, CAST(FILEPROPERTY(name, SpaceUsed) AS INT) * 8 / 1024 AS UsedMB, size * 8 / 1024 - CAST(FILEPROPERTY(name, SpaceUsed) AS INT) * 8 / 1024 AS FreeMB FROM sys.database_files;这个查询的价值在于它能告诉你数据文件和日志文件各自的已用空间和可用空间。如果你发现数据文件还有大量未用空间但业务还在报磁盘空间不足问题可能根本不在数据库而在其他文件或者备份策略上。这种排查路径会快很多。6. 把一次统计变成持续监控记录历史快照追踪表增长统计做一次只是摸底真正有价值的是持续跟踪。下面分享一下我是怎么把统计脚本改造成一个轻量级监控方案的。6.1 创建一个历史快照表IF OBJECT_ID(dbo.TableStatsHistory) IS NULL BEGIN CREATE TABLE dbo.TableStatsHistory ( Id INT IDENTITY(1,1) PRIMARY KEY, SnapshotDate DATE NOT NULL, SnapshotTime DATETIME NOT NULL, SchemaName NVARCHAR(128) NOT NULL, TableName NVARCHAR(128) NOT NULL, RowCounts BIGINT NOT NULL, TotalSpaceKB BIGINT NOT NULL, UsedSpaceKB BIGINT NOT NULL, IndexSpaceKB BIGINT NOT NULL ); END;有几点设计说明一下我用了SnapshotDate和SnapshotTime两个字段而不是直接用一个DATETIME是因为按天查询时用SnapshotDate做条件可以走索引而且很好理解。如果你把日期和时间都塞进一个字段过滤时还得用CONVERT转换函数反而容易出问题。6.2 把当前统计结果写入快照表INSERT INTO dbo.TableStatsHistory ( SnapshotDate, SnapshotTime, SchemaName, TableName, RowCounts, TotalSpaceKB, UsedSpaceKB, IndexSpaceKB ) SELECT CAST(GETDATE() AS DATE), GETDATE(), s.name, t.name, SUM(p.rows), CAST(SUM(a.total_pages) * 8 AS BIGINT), CAST(SUM(a.used_pages) * 8 AS BIGINT), CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS BIGINT) FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.indexes i ON t.object_id i.object_id INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0 AND i.index_id IN (0, 1) GROUP BY s.name, t.name;这段SQL可以直接做成一个存储过程或者Agent作业每天凌晨跑一次。跑了三个月之后你手上就有了一张表增长趋势图的数据基础。6.3 查询增长趋势哪张表涨得最快-- 某张表最近30天的行数变化 SELECT SnapshotDate, RowCounts, TotalSpaceKB / 1024 AS TotalSpaceMB FROM dbo.TableStatsHistory WHERE TableName SalesOrderDetail AND SnapshotDate DATEADD(DAY, -30, CAST(GETDATE() AS DATE)) ORDER BY SnapshotDate;更进一步你可以计算每天的增量WITH Growth AS ( SELECT TableName, SnapshotDate, RowCounts, LAG(RowCounts) OVER (PARTITION BY TableName ORDER BY SnapshotDate) AS PrevRowCounts FROM dbo.TableStatsHistory WHERE TableName SalesOrderDetail ) SELECT SnapshotDate, RowCounts, RowCounts - PrevRowCounts AS DailyGrowthRows FROM Growth WHERE PrevRowCounts IS NOT NULL ORDER BY SnapshotDate;这样你就不是拍脑袋说这张表涨得很快了而是能拿出确凿的每日增量数据。有一次我就是靠这个增量数据成功说服业务方给日志表做了分区半年之后备份速度从一小时降到了十五分钟。没有历史快照数据你在评审会上说什么都是空话。6.4 保留策略和注意事项快照表本身也会越攒越大所以建议设一个保留策略。最简单的方式就是定期删除90天或180天之前的数据DELETE FROM dbo.TableStatsHistory WHERE SnapshotDate DATEADD(DAY, -180, CAST(GETDATE() AS DATE));另外一个要提醒的是快照统计不要跟业务高峰期重叠。虽然sys.dm_db_partition_stats不触发扫描但它对大库执行时也会有一些元数据读开销。我习惯安排在凌晨2点到5点之间跟备份任务错开半小时以上。如果你是云数据库还要注意实例规格和IOPS限制避免和自动备份任务撞车导致性能波动。7. 踩坑记录与实操参数建议这些细节救过我的命写到这里我再把实际操作中踩过的一些坑和最终沉淀下来的参数建议集中列一遍。这些东西不是从官方文档里翻出来的而是在生产环境里真的碰到过、调过的经验。7.1 sp_spaceused返回的行数可能让你误判大表有一次我负责的库业务方反馈有一张订单表至少有500万行但我用sp_spaceused一看显示的rows只有300万。差点因为这个数字判断错了备份恢复的ETA。后来排查发现原来这张表是分区表sp_spaceused不带分区参数时统计的只是默认分区的行数不是全部分区总和。如果你用的是分区表sp_spaceused的返回可能是错的或者只能看到某个特定分区。这种场景下我强烈建议你用sys.dm_db_partition_stats方案并且在GROUP BY时不要漏掉分区维度。如果你想看某张分区表每个分区的行数SELECT partition_number, SUM(rows) AS RowCounts FROM sys.partitions WHERE object_id OBJECT_ID(dbo.SalesOrderDetail) AND index_id IN (0, 1) GROUP BY partition_number ORDER BY partition_number;每个分区的行数一眼看穿。这个对于判断哪几个月的数据占了大头特别有用。7.2 统计脚本的性能开销控制批量统计几百张表时sys.dm_db_partition_stats本身很快但如果一张表有几十个索引JOIN时会产生大量行。我建议把index_id IN (0, 1)的条件放在JOIN之前也就是INNER JOIN ... ON条件里而不是放在WHERE里。比如这样INNER JOIN sys.indexes i ON t.object_id i.object_id AND i.index_id IN (0, 1)这样做可以让SQL Server在表连接时直接过滤掉非聚集索引而不是先全部JOIN完再去WHERE过滤。在系统视图上看执行计划会有明显区别。几十万行结果的场景下这个顺序调整可能把查询时间从几十秒降到几秒。7.3 别在业务高峰期跑DBCC UPDATEUSAGE虽然这条我前面提过但还是想再强调一次。DBCC UPDATEUSAGE会扫描分配单元并可能持有一些锁索引维护期间影响不小。我在有一次白天误跑了DBCC UPDATEUSAGE(YourDb)结果整个库的操作出现了明显的阻塞。从那以后我把这个命令的权限严格限制在DBA手上并且总是在维护窗口执行。如果你要校准的库特别大也别忘了先评估一下扫描时间。我的参考经验是百万级表秒级完成千万级表几十秒到几分钟亿级表可能以十分钟为单位计算。没有提前评估就动手很可能把维护窗口直接拖垮。7.4 每次统计都记下统计方式我的习惯是在统计结果里加一列SourceType。比如1 sys.dm_db_partition_stats常规统计2 COUNT(*)精确统计3 DBCC UPDATEUSAGE后统计校准统计这样过几个月回头查历史数据时你能知道哪些数字是估算的、哪些是精确的、哪些是校准过的。不然你拿着一张Excel表问为什么这里写100万行、那里写95万行根本无从解释。加上这个标记一秒就能定位到当时用的统计口径。7.5 表数量极大时的分批处理技巧如果数据库里有几千张表一次性把所有动态SQL拼起来执行有可能超过sp_executesql的字符串长度限制或者造成临时表过大。我遇到过一个库表数量超过8000张直接执行时报了The string that starts with INSERT INTO... is too long。解决办法是分批处理每批处理500张表DECLARE BatchSize INT 500; DECLARE Offset INT 0; DECLARE sql NVARCHAR(MAX); WHILE Offset (SELECT COUNT(*) FROM sys.tables) BEGIN SET sql N; SELECT sql sql NINSERT INTO #TableStats EXEC sp_spaceused QUOTENAME(name) N; FROM sys.tables WHERE is_ms_shipped 0 ORDER BY name OFFSET Offset ROWS FETCH NEXT BatchSize ROWS ONLY; EXEC sp_executesql sql; SET Offset Offset BatchSize; END;这个分批的思路不光适用于sp_spaceused也适用于任何大批量动态SQL拼接的场景。碰到几千张表时一定要有这个分批意识。7.6 统计结果出来后怎么判断异常最后分享一个经验行数本身不可怕行数的突变才是重点。同一个库每周跑一次统计如果你发现一张表的行数从1000万突然涨到3000万这说明业务上的批量写入或者数据同步逻辑出了问题或者有异常任务在灌数据。这时候你需要的不是再统计一次而是立刻去查最近有没有新增的作业、有没有跑批任务被重复触发。反过来如果一张平时千万级的大表行数突然掉到几十万那就要警惕是否有人手滑做了DELETE或者TRUNCATE。历史快照表在这里的价值就体现出来了——没有历史数据做对比你很难在第一时间发现这种诡异变化。8. 运行这套统计方案的统一入口收拢成存储过程前面讲的都是即查即用的脚本。如果这些脚本要在团队里推广或者给自己省事我建议把它收拢成存储过程统一入口方便重复调用。我自己的做法是写一个usp_GetTableRowCountSummary存储过程支持几个参数SchemaName可选、TableName可选、OrderBy可选行数/空间、TopN可选。核心逻辑就是前面那套sys.dm_db_partition_stats方案。我把最终版本贴在这里你可以直接参考改造成自己的工具CREATE OR ALTER PROCEDURE dbo.usp_GetTableRowCountSummary TableName NVARCHAR(128) NULL, OrderBy NVARCHAR(20) rows, -- rows 或 space TopN INT 20 AS BEGIN SET NOCOUNT ON; SELECT TOP (TopN) s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS RowCounts, CAST(SUM(a.total_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS TotalSpaceMB, CAST(SUM(a.used_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS UsedSpaceMB, CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0 AS DECIMAL(18, 2)) AS IndexSpaceMB FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.indexes i ON t.object_id i.object_id AND i.index_id IN (0, 1) INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0 AND (TableName IS NULL OR t.name TableName) GROUP BY s.name, t.name ORDER BY CASE WHEN OrderBy rows THEN SUM(p.rows) END DESC, CASE WHEN OrderBy space THEN SUM(a.total_pages) END DESC; END;调用的时候就很舒服了-- 全库行数TOP20 EXEC dbo.usp_GetTableRowCountSummary OrderBy rows, TopN 20; -- 全库空间占用TOP20 EXEC dbo.usp_GetTableRowCountSummary OrderBy space, TopN 20; -- 只看某一张表 EXEC dbo.usp_GetTableRowCountSummary TableName SalesOrderDetail, TopN 1;团队里其他同事要统计的时候直接调这个存储过程就行不用再各自拼SQL。如果还要看总数据量在过程里再加一句汇总查询输出或者让调用方自己跑前面提到的全库汇总SQL。写存储过程的时候别忘了一个细节SET NOCOUNT ON。不写这句每次INSERT或SELECT后都会返回rows affected消息某些客户端执行脚本时会把一堆消息当成结果集输出影响解析。这种小问题排查起来也很费时间提前写好省心。9. 个人心得统计工具兜底业务口径先行套脚本写出来用了几年踩的坑也积累了不少最后想分享的反而是一个偏软的经验统计工具再顺手也永远替代不了先约定口径再动手统计这个步骤。我遇到最典型的一次是两个开发组在群里争论某张表到底有800万还是1200万行。其实两边用的都是同一套统计脚本但一方统计的是整个分区表全部分区另一方只统计了默认分区。双方都有自己的道理因为我没人定义这张表的数据量到底指什么。后来我把统计口径写了一页简短说明贴在文档里日常巡检用sys.dm_db_partition_stats、精确对账用COUNT(*)、月度校准用DBCC UPDATEUSAGE三个口径各司其职。再之后类似的争论就少了很多。还有一个我个人的习惯是每当业务方跟我约时间讨论数据库容量时我一定提前准备好最近几个月的表增长快照不只是给一个当前大小。这样对方能看到趋势沟通起来更高效。比起临时跑一条SQL然后说现在1.5亿行三个月前1.2亿行、月均增长1000万行这种说法显然更有决策价值。另外如果你的环境是云数据库很多云厂商控制台自带一键查看表空间的功能但要注意它们底层大多也是调用类似sys.dm_db_partition_stats的方式同样存在统计口径的问题。控制台给出的数字方便看但真要做决策还是用自己掌握的这套脚本更靠谱至少你知道每个数字背后的计算逻辑。这套方案从单表查询到全库汇总、从历史快照到存储过程封装基本覆盖了我日常90%以上的查看数据量需求。你拿过去之后建议先在测试库完整跑一遍对照一下每张表的实际感受——比如选一张你自己清楚大概行数的表比对统计结果是否一致。确认没问题再上生产过程中如果遇到跟我不一样的场景顺着系统视图往下查一般都能找到原因。 SQL Server统计每张表数据量：三种方法与实用脚本对比
1. 为什么需要统计每张表的数据量我遇到的几个真实场景干SQL Server运维和开发这几年统计每张表的数据量这个需求几乎是隔三差五就会出现一次。听起来很简单但真操作起来会发现一套顺手、可靠、还能反复用的方法并不那么容易凑齐。先说说我实际碰到过的场景你大概也能对号入座第一个场景是数据迁移评估。有一次要把一套老业务系统从SQL Server 2008 R2迁到SQL Server 2019用的还是高版本兼容模式。对方只给了一句库大概有30个G但迁移方案里必须知道哪几张表占了大头因为要决定哪些表可以延迟迁移、哪些表需要并行迁移、是否要做数据压缩。没有一张表行数排行榜方案根本没法细化。第二个场景是容量规划。那时候负责的库里有一张日志表业务方说数据增长很快。到底多快一周涨多少行每天涨多少只有把历史数据量的快照记录下来看到一条陡峭的增长曲线才能说服业务方做归档策略。不然光靠嘴说这表很大一点说服力都没有。第三个场景是性能问题排查。有张业务表查询很慢第一反应是缺索引。但在加索引前我得先知道这张表到底多大——是百万级、千万级还是亿级。不同量级的优化思路完全不一样百万级可能就是缺个好索引亿级就得考虑分区、归档甚至换存储方案。这步判断错了后面全白干。第四个场景是莫名其妙的数据量对不上。周期性的数据同步任务跑完业务方说源表有100万行目标表只有80万行。你需要快速统计出所有表的行数做对比定位到底哪几张表对不上。如果还是手工一条一条sp_spaceused去执行那真能查到怀疑人生。所以把查看每张表的数据量这件事做成一套脚本、弄明白每一种统计方式的原理、知道哪些数字能信哪些数字只能做参考是每个SQL Server从业者绕不开的基本功。这篇文章就把我的做法、踩过的坑、以及最后沉淀下来的模板都整理出来按我的实际使用经验从头到尾讲一遍。2. 三种主流统计手段的对比不是每个行数都可信先讲核心的统计手段。市面上常见的无非是三种系统存储过程sp_spaceused、系统视图sys.dm_db_partition_stats、以及老旧的sysindexes。我建议你先把它们各自的脾气摸清楚再决定用哪套方案。2.1 sp_spaceused最直观但第一次执行有隐藏成本-- 查看当前库所有表的汇总 sp_spaceused; -- 查看单张表不更新统计信息 sp_spaceused NUserTable; -- 查看单张表强制更新统计信息 sp_spaceused NUserTable, updateusage NTRUE;sp_spaceused返回的列有name表名、rows行数、reserved保留空间、data数据空间、index_size索引空间、unused未用空间。最坑的一点就在这个updateusage参数上。不带这个参数的sp_spaceused其实读的是系统表里缓存的统计信息大多数情况下没有问题。但如果统计信息过期你得到的行数可能是老数据。而一旦你加了TRUESQL Server就会执行DBCC UPDATEUSAGE对这张表做完整扫描来重新统计——小表感觉不到几千万行的大表动辄跑几分钟甚至更久而且会占用大量IO和CPU。我第一次跑DBCC UPDATEUSAGE扫了一个2.3亿行的表跑了大概七分钟期间库整体IO明显飙升监控直接告警。从那之后我就立了一个规矩生产环境绝不轻易给sp_spaceused加UPDATEUSAGE参数除非是在维护窗口或凌晨低峰期。2.2 sys.dm_db_partition_stats速度快且适合批量这个DMV是SQL Server 2005之后引入的它直接访问分区级别的统计信息不需要触发任何扫描所以批量统计几百张表时非常快。SELECT t.name AS TableName, SUM(p.rows) AS RowCounts FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id p.object_id WHERE t.is_ms_shipped 0 AND p.index_id IN (0, 1) -- 0堆1聚集索引 GROUP BY t.name ORDER BY RowCounts DESC;这里有个非常关键的地方p.index_id IN (0, 1)。如果把这行去掉你会看到很多表的行数突然翻了几倍甚至几十倍其实那不是真实行数而是把这张表上所有非聚集索引的分区行数也累加进来了。我后面会专门讲这个坑。2.3 sysindexes老掉牙但有些历史脚本里还在用SELECT t.name AS TableName, MAX(si.rows) AS RowCounts FROM sys.tables t INNER JOIN sys.sysindexes si ON t.object_id si.id WHERE t.is_ms_shipped 0 AND si.indid IN (0, 1) GROUP BY t.name ORDER BY RowCounts DESC;sysindexes在SQL Server 2000时代几乎是最常用的方式。但它的问题也很明显rows字段容易失效尤其在大量增删改后不自动更新而且微软在新版本里已经不再保证这个视图的行为。我只建议在兼容老脚本或者实在没别的办法时才看一眼新开发的脚本不要走这条路。2.4 三种方式横向对比方式是否触发扫描批量统计速度数据准确性适用场景sp_spaceused不带参数否一般依赖统计信息单表快速查看sp_spaceused带UPDATEUSAGE是慢高维护窗口校准sys.dm_db_partition_stats否快高近似实时批量统计首选sysindexes否快可能过期老脚本兼容我个人的结论很明确日常批量统计用sys.dm_db_partition_stats单表查看图省事用sp_spaceused不带参数。只有在做一次彻底的对账时才挑凌晨窗口对重点大表跑DBCC UPDATEUSAGE。3. 一个能直接用的批量统计脚本覆盖所有用户表光会单表查询还不够真正的需求往往是一口气列出一个库里所有表的数据量。我来分享一下我最终沉淀下来的脚本模板以及写这套脚本时踩过的一些细节坑。3.1 基础版临时表 动态SQL思路很简单先拿到所有用户表名字然后逐条拼接sp_spaceused的执行语句结果集中收集到一张临时表里。IF OBJECT_ID(tempdb..#TableSpace) IS NOT NULL DROP TABLE #TableSpace; CREATE TABLE #TableSpace ( TableName NVARCHAR(128), Rows BIGINT, Reserved NVARCHAR(50), Data NVARCHAR(50), IndexSize NVARCHAR(50), Unused NVARCHAR(50) ); DECLARE sql NVARCHAR(MAX) N; SELECT sql sql NINSERT INTO #TableSpace EXEC sp_spaceused QUOTENAME(name) N; FROM sys.tables WHERE is_ms_shipped 0; EXEC sp_executesql sql; SELECT TableName, Rows, Reserved, Data, IndexSize, Unused FROM #TableSpace ORDER BY CAST(REPLACE(Reserved, KB, ) AS BIGINT) DESC;这个脚本有两个细节需要注意。细节一QUOTENAME的作用。如果表名是Order Details这种带空格的名字直接用sp_spaceused Order Details会报错因为中间的空格把表名拆成了两个标识符。QUOTENAME会用方括号把名字包起来变成[Order Details]这样就安全了。这是我实际踩过的坑——有张表叫User Data第一次写脚本时没注意直接报语法错误。细节二Reserved字段是字符串。它返回的是12345 KB这种格式如果你想要按空间大小排序直接ORDER BY Reserved会变成按字母序排结果就是999 KB排在100 KB前面完全不对。所以排序前要把单位去掉转成数字。3.2 进阶版加上行数、空间大小、建表时间的完整统计基础版够用了但很多时候光有空间不够我还想把表的基本信息一起输出。所以后来我把脚本改成了这样IF OBJECT_ID(tempdb..#TableStats) IS NOT NULL DROP TABLE #TableStats; CREATE TABLE #TableStats ( SchemaName NVARCHAR(128), TableName NVARCHAR(128), RowCounts BIGINT, TotalSpaceKB BIGINT, UsedSpaceKB BIGINT, IndexSpaceKB BIGINT, CreateDate DATETIME, ModifyDate DATETIME ); INSERT INTO #TableStats ( SchemaName, TableName, RowCounts, TotalSpaceKB, UsedSpaceKB, IndexSpaceKB, CreateDate, ModifyDate ) SELECT s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS RowCounts, CAST(SUM(a.total_pages) * 8 AS BIGINT) AS TotalSpaceKB, CAST(SUM(a.used_pages) * 8 AS BIGINT) AS UsedSpaceKB, CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS BIGINT) AS IndexSpaceKB, t.create_date, t.modify_date FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.indexes i ON t.object_id i.object_id INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0 AND i.index_id IN (0, 1) GROUP BY s.name, t.name, t.create_date, t.modify_date ORDER BY RowCounts DESC;这一版直接查的是分配单元allocation_units算出来的空间大小是按页数乘以8KB得出的比解析sp_spaceused的字符串要干净得多。这里有个地方需要说明SUM(a.total_pages) * 8是按页算KB。SQL Server的页大小固定是8KB一个区extent是8页共64KB所以不论表是否压缩这个计算都能给出准确的空间占用。数据压缩只会影响实际存储的行数行可以变小、一页装更多行不会改变页的物理大小所以这个算法永远成立。3.3 输出方式怎么把结果攒成一张排行榜统计结果攒到临时表后最终查询建议按场景输出。如果是想快速看到行数最高的表SELECT TOP 20 SchemaName, TableName, RowCounts, TotalSpaceKB / 1024 AS TotalSpaceMB FROM #TableStats ORDER BY RowCounts DESC;如果是想看空间占用最大的表SELECT TOP 20 SchemaName, TableName, RowCounts, TotalSpaceKB / 1024 AS TotalSpaceMB, UsedSpaceKB / 1024 AS UsedSpaceMB, IndexSpaceKB / 1024 AS IndexSpaceMB FROM #TableStats ORDER BY TotalSpaceKB DESC;如果你需要横向对比数据规模和空间规模之间的关系这张临时表都能直接支撑。我一般会在最后加一个汇总SELECT COUNT(*) AS TotalTableCount, SUM(RowCounts) AS TotalRowCount, SUM(TotalSpaceKB) / 1024 AS TotalSpaceMB, SUM(UsedSpaceKB) / 1024 AS UsedSpaceMB FROM #TableStats;这样一跑整库的家底就清楚了多少张表、总行数多少、总占用多少空间。4. 统计数字不等于真实行数深入解析两个容易翻车的根因用sys.dm_db_partition_stats这种方式统计出来的行数我每次都会跟业务方强调这个数字是系统当前记录的统计值不是COUNT(*)实时数出来的精确值。两者大多数时候一致但在两种特定场景下会不一致提前知道能帮你省掉很多不必要的解释和返工。4.1 根因一非聚集索引导致的行数虚高这是新手最容易踩的坑。假设你的表有一个主键聚集索引和三个非聚集索引那么这张表在sys.partitions里会有四条记录index_id 0堆没有聚集索引时的数据存储index_id 1聚集索引index_id 2第一个非聚集索引index_id 3第二个非聚集索引index_id 4第三个非聚集索引如果你查询时没有过滤index_id只写了SUM(p.rows)那么这张表的行数会被算成四遍。我亲眼见过一个同事把一张40万行的表统计出160万行后来一查才发现是索引累加的原因。所以统一规则就是统计表行数只看index_id IN (0, 1)也就是堆或聚集索引。非聚集索引的行数只用来做索引大小评估不做表行数统计。4.2 根因二统计信息不是实时值SQL Server为了性能不会在每次INSERT/UPDATE/DELETE后都实时更新所有统计信息。sys.dm_db_partition_stats里的rows其实来自分区元数据它跟统计信息的刷新频率有一定关系。在大量增删操作后、统计信息未自动更新时你可能会看到一个过期的行数。举个例子你刚批量删除了300万行再去查sys.dm_db_partition_stats可能看到的还是删除前的行数。等一段时间或者手动更新统计信息后才会纠正。这就是为什么在做精确对账、数据校验的时候不能只依赖系统视图还是得用COUNT(*)。SELECT COUNT(*) FROM dbo.TargetTable;对超大表跑这个肯定有代价但它能给你一个100%准确的实时行数。如果只是日常巡检系统视图片偏低一点高一点问题不大如果是要跟业务方数人头那必须用COUNT(*)口径。4.3 什么时候需要DBCC UPDATEUSAGEDBCC UPDATEUSAGE存在的意义就是校准这些元数据。-- 校准当前库所有表的空间和行数统计 DBCC UPDATEUSAGE(YourDatabaseName); -- 只校准一张表 DBCC UPDATEUSAGE(YourDatabaseName, dbo.TargetTable);它本质上做的事情就是遍历表和索引的页数纠正sys.partitions和sys.allocation_units里的缓存值。代价是按页扫描表越大跑得越久。我的经验是不要对全库跑只对怀疑不准确的特定大表跑。而且一定安排在凌晨维护窗口跑完立刻做一次快照统计。如果你发现某张表的行数一直在飘怀疑系统统计出了问题这时候DBCC UPDATEUSAGE才是对症下药。4.4 行数口径开发、测试、生产各用什么环境推荐口径原因开发/测试sys.dm_db_partition_stats快、方便、没有性能压力生产日常巡检sys.dm_db_partition_stats不触发扫描对业务影响小生产精确对账COUNT(*)只信任实时计数生产月度校准DBCC UPDATEUSAGE 快照纠正元数据偏差如果你能把这套口径讲给团队听清楚很多为什么数据量对不上为什么统计出来翻倍的争论就能提前避免。5. 总数据量怎么算行数总和与磁盘空间是两个概念总数据量这句话在业务沟通里有歧义——有人问的是总行数有人问的是磁盘占用空间还有人两个都要。所以我在做统计表的时候会同时输出行数和空间两个维度并区分清楚。5.1 总行数多少张表加起来有多少行SELECT COUNT(DISTINCT t.object_id) AS TableCount, SUM(p.rows) AS TotalRows FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id p.object_id WHERE t.is_ms_shipped 0 AND p.index_id IN (0, 1);注意我用了COUNT(DISTINCT t.object_id)而不是COUNT(*)。因为一张表如果有多个分区或者多个索引sys.partitions里就会有多个行直接COUNT(*)会把表数量也算重复。这一点对分区的表尤其明显。5.2 看总体的空间占用数数据量更要数页有一种常见误区是把行数等同于数据量。但真实场景里行数少的表完全可能占用更大的磁盘空间——因为每一行有变长字段、有索引、有碎片。所以空间维度的统计一定不能省。SELECT CAST(SUM(a.total_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS TotalSizeMB, CAST(SUM(a.used_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS UsedSizeMB, CAST(SUM(a.total_pages) * 8 / 1024.0 / 1024 AS DECIMAL(18, 2)) AS TotalSizeGB, CAST(SUM(a.used_pages) * 8 / 1024.0 / 1024 AS DECIMAL(18, 2)) AS UsedSizeGB FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id i.object_id INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0;这里有两点要提醒第一total_pages包含表和索引的所有页。它等于数据页索引页未使用的保留页。used_pages只包括实际使用了的数据页和索引页。两者之差就是碎片和预留空间。如果你的库碎片率很高你会发现total_pages明显比used_pages大不少这正是需要重建或重组索引的信号。第二这个统计不包含日志文件.ldf和tempdb。这两个文件的大小和增长情况需要用DBCC SQLPERF和sys.database_files才能查到。如果有人跟你说数据库占了200G你要分辨清楚他是说数据文件还是数据文件日志文件。这个口径差很大——我遇到过日志文件占了一大半的情况。5.3 数据库整体大小文件维度的统计法除了逻辑上的表数据量物理文件的大小也建议定期掌握。一条SQL就能看全当前库的所有文件SELECT name AS FileName, type_desc AS FileType, size * 8 / 1024 AS SizeMB, CAST(FILEPROPERTY(name, SpaceUsed) AS INT) * 8 / 1024 AS UsedMB, size * 8 / 1024 - CAST(FILEPROPERTY(name, SpaceUsed) AS INT) * 8 / 1024 AS FreeMB FROM sys.database_files;这个查询的价值在于它能告诉你数据文件和日志文件各自的已用空间和可用空间。如果你发现数据文件还有大量未用空间但业务还在报磁盘空间不足问题可能根本不在数据库而在其他文件或者备份策略上。这种排查路径会快很多。6. 把一次统计变成持续监控记录历史快照追踪表增长统计做一次只是摸底真正有价值的是持续跟踪。下面分享一下我是怎么把统计脚本改造成一个轻量级监控方案的。6.1 创建一个历史快照表IF OBJECT_ID(dbo.TableStatsHistory) IS NULL BEGIN CREATE TABLE dbo.TableStatsHistory ( Id INT IDENTITY(1,1) PRIMARY KEY, SnapshotDate DATE NOT NULL, SnapshotTime DATETIME NOT NULL, SchemaName NVARCHAR(128) NOT NULL, TableName NVARCHAR(128) NOT NULL, RowCounts BIGINT NOT NULL, TotalSpaceKB BIGINT NOT NULL, UsedSpaceKB BIGINT NOT NULL, IndexSpaceKB BIGINT NOT NULL ); END;有几点设计说明一下我用了SnapshotDate和SnapshotTime两个字段而不是直接用一个DATETIME是因为按天查询时用SnapshotDate做条件可以走索引而且很好理解。如果你把日期和时间都塞进一个字段过滤时还得用CONVERT转换函数反而容易出问题。6.2 把当前统计结果写入快照表INSERT INTO dbo.TableStatsHistory ( SnapshotDate, SnapshotTime, SchemaName, TableName, RowCounts, TotalSpaceKB, UsedSpaceKB, IndexSpaceKB ) SELECT CAST(GETDATE() AS DATE), GETDATE(), s.name, t.name, SUM(p.rows), CAST(SUM(a.total_pages) * 8 AS BIGINT), CAST(SUM(a.used_pages) * 8 AS BIGINT), CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS BIGINT) FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.indexes i ON t.object_id i.object_id INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0 AND i.index_id IN (0, 1) GROUP BY s.name, t.name;这段SQL可以直接做成一个存储过程或者Agent作业每天凌晨跑一次。跑了三个月之后你手上就有了一张表增长趋势图的数据基础。6.3 查询增长趋势哪张表涨得最快-- 某张表最近30天的行数变化 SELECT SnapshotDate, RowCounts, TotalSpaceKB / 1024 AS TotalSpaceMB FROM dbo.TableStatsHistory WHERE TableName SalesOrderDetail AND SnapshotDate DATEADD(DAY, -30, CAST(GETDATE() AS DATE)) ORDER BY SnapshotDate;更进一步你可以计算每天的增量WITH Growth AS ( SELECT TableName, SnapshotDate, RowCounts, LAG(RowCounts) OVER (PARTITION BY TableName ORDER BY SnapshotDate) AS PrevRowCounts FROM dbo.TableStatsHistory WHERE TableName SalesOrderDetail ) SELECT SnapshotDate, RowCounts, RowCounts - PrevRowCounts AS DailyGrowthRows FROM Growth WHERE PrevRowCounts IS NOT NULL ORDER BY SnapshotDate;这样你就不是拍脑袋说这张表涨得很快了而是能拿出确凿的每日增量数据。有一次我就是靠这个增量数据成功说服业务方给日志表做了分区半年之后备份速度从一小时降到了十五分钟。没有历史快照数据你在评审会上说什么都是空话。6.4 保留策略和注意事项快照表本身也会越攒越大所以建议设一个保留策略。最简单的方式就是定期删除90天或180天之前的数据DELETE FROM dbo.TableStatsHistory WHERE SnapshotDate DATEADD(DAY, -180, CAST(GETDATE() AS DATE));另外一个要提醒的是快照统计不要跟业务高峰期重叠。虽然sys.dm_db_partition_stats不触发扫描但它对大库执行时也会有一些元数据读开销。我习惯安排在凌晨2点到5点之间跟备份任务错开半小时以上。如果你是云数据库还要注意实例规格和IOPS限制避免和自动备份任务撞车导致性能波动。7. 踩坑记录与实操参数建议这些细节救过我的命写到这里我再把实际操作中踩过的一些坑和最终沉淀下来的参数建议集中列一遍。这些东西不是从官方文档里翻出来的而是在生产环境里真的碰到过、调过的经验。7.1 sp_spaceused返回的行数可能让你误判大表有一次我负责的库业务方反馈有一张订单表至少有500万行但我用sp_spaceused一看显示的rows只有300万。差点因为这个数字判断错了备份恢复的ETA。后来排查发现原来这张表是分区表sp_spaceused不带分区参数时统计的只是默认分区的行数不是全部分区总和。如果你用的是分区表sp_spaceused的返回可能是错的或者只能看到某个特定分区。这种场景下我强烈建议你用sys.dm_db_partition_stats方案并且在GROUP BY时不要漏掉分区维度。如果你想看某张分区表每个分区的行数SELECT partition_number, SUM(rows) AS RowCounts FROM sys.partitions WHERE object_id OBJECT_ID(dbo.SalesOrderDetail) AND index_id IN (0, 1) GROUP BY partition_number ORDER BY partition_number;每个分区的行数一眼看穿。这个对于判断哪几个月的数据占了大头特别有用。7.2 统计脚本的性能开销控制批量统计几百张表时sys.dm_db_partition_stats本身很快但如果一张表有几十个索引JOIN时会产生大量行。我建议把index_id IN (0, 1)的条件放在JOIN之前也就是INNER JOIN ... ON条件里而不是放在WHERE里。比如这样INNER JOIN sys.indexes i ON t.object_id i.object_id AND i.index_id IN (0, 1)这样做可以让SQL Server在表连接时直接过滤掉非聚集索引而不是先全部JOIN完再去WHERE过滤。在系统视图上看执行计划会有明显区别。几十万行结果的场景下这个顺序调整可能把查询时间从几十秒降到几秒。7.3 别在业务高峰期跑DBCC UPDATEUSAGE虽然这条我前面提过但还是想再强调一次。DBCC UPDATEUSAGE会扫描分配单元并可能持有一些锁索引维护期间影响不小。我在有一次白天误跑了DBCC UPDATEUSAGE(YourDb)结果整个库的操作出现了明显的阻塞。从那以后我把这个命令的权限严格限制在DBA手上并且总是在维护窗口执行。如果你要校准的库特别大也别忘了先评估一下扫描时间。我的参考经验是百万级表秒级完成千万级表几十秒到几分钟亿级表可能以十分钟为单位计算。没有提前评估就动手很可能把维护窗口直接拖垮。7.4 每次统计都记下统计方式我的习惯是在统计结果里加一列SourceType。比如1 sys.dm_db_partition_stats常规统计2 COUNT(*)精确统计3 DBCC UPDATEUSAGE后统计校准统计这样过几个月回头查历史数据时你能知道哪些数字是估算的、哪些是精确的、哪些是校准过的。不然你拿着一张Excel表问为什么这里写100万行、那里写95万行根本无从解释。加上这个标记一秒就能定位到当时用的统计口径。7.5 表数量极大时的分批处理技巧如果数据库里有几千张表一次性把所有动态SQL拼起来执行有可能超过sp_executesql的字符串长度限制或者造成临时表过大。我遇到过一个库表数量超过8000张直接执行时报了The string that starts with INSERT INTO... is too long。解决办法是分批处理每批处理500张表DECLARE BatchSize INT 500; DECLARE Offset INT 0; DECLARE sql NVARCHAR(MAX); WHILE Offset (SELECT COUNT(*) FROM sys.tables) BEGIN SET sql N; SELECT sql sql NINSERT INTO #TableStats EXEC sp_spaceused QUOTENAME(name) N; FROM sys.tables WHERE is_ms_shipped 0 ORDER BY name OFFSET Offset ROWS FETCH NEXT BatchSize ROWS ONLY; EXEC sp_executesql sql; SET Offset Offset BatchSize; END;这个分批的思路不光适用于sp_spaceused也适用于任何大批量动态SQL拼接的场景。碰到几千张表时一定要有这个分批意识。7.6 统计结果出来后怎么判断异常最后分享一个经验行数本身不可怕行数的突变才是重点。同一个库每周跑一次统计如果你发现一张表的行数从1000万突然涨到3000万这说明业务上的批量写入或者数据同步逻辑出了问题或者有异常任务在灌数据。这时候你需要的不是再统计一次而是立刻去查最近有没有新增的作业、有没有跑批任务被重复触发。反过来如果一张平时千万级的大表行数突然掉到几十万那就要警惕是否有人手滑做了DELETE或者TRUNCATE。历史快照表在这里的价值就体现出来了——没有历史数据做对比你很难在第一时间发现这种诡异变化。8. 运行这套统计方案的统一入口收拢成存储过程前面讲的都是即查即用的脚本。如果这些脚本要在团队里推广或者给自己省事我建议把它收拢成存储过程统一入口方便重复调用。我自己的做法是写一个usp_GetTableRowCountSummary存储过程支持几个参数SchemaName可选、TableName可选、OrderBy可选行数/空间、TopN可选。核心逻辑就是前面那套sys.dm_db_partition_stats方案。我把最终版本贴在这里你可以直接参考改造成自己的工具CREATE OR ALTER PROCEDURE dbo.usp_GetTableRowCountSummary TableName NVARCHAR(128) NULL, OrderBy NVARCHAR(20) rows, -- rows 或 space TopN INT 20 AS BEGIN SET NOCOUNT ON; SELECT TOP (TopN) s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS RowCounts, CAST(SUM(a.total_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS TotalSpaceMB, CAST(SUM(a.used_pages) * 8 / 1024.0 AS DECIMAL(18, 2)) AS UsedSpaceMB, CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0 AS DECIMAL(18, 2)) AS IndexSpaceMB FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.indexes i ON t.object_id i.object_id AND i.index_id IN (0, 1) INNER JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id INNER JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0 AND (TableName IS NULL OR t.name TableName) GROUP BY s.name, t.name ORDER BY CASE WHEN OrderBy rows THEN SUM(p.rows) END DESC, CASE WHEN OrderBy space THEN SUM(a.total_pages) END DESC; END;调用的时候就很舒服了-- 全库行数TOP20 EXEC dbo.usp_GetTableRowCountSummary OrderBy rows, TopN 20; -- 全库空间占用TOP20 EXEC dbo.usp_GetTableRowCountSummary OrderBy space, TopN 20; -- 只看某一张表 EXEC dbo.usp_GetTableRowCountSummary TableName SalesOrderDetail, TopN 1;团队里其他同事要统计的时候直接调这个存储过程就行不用再各自拼SQL。如果还要看总数据量在过程里再加一句汇总查询输出或者让调用方自己跑前面提到的全库汇总SQL。写存储过程的时候别忘了一个细节SET NOCOUNT ON。不写这句每次INSERT或SELECT后都会返回rows affected消息某些客户端执行脚本时会把一堆消息当成结果集输出影响解析。这种小问题排查起来也很费时间提前写好省心。9. 个人心得统计工具兜底业务口径先行套脚本写出来用了几年踩的坑也积累了不少最后想分享的反而是一个偏软的经验统计工具再顺手也永远替代不了先约定口径再动手统计这个步骤。我遇到最典型的一次是两个开发组在群里争论某张表到底有800万还是1200万行。其实两边用的都是同一套统计脚本但一方统计的是整个分区表全部分区另一方只统计了默认分区。双方都有自己的道理因为我没人定义这张表的数据量到底指什么。后来我把统计口径写了一页简短说明贴在文档里日常巡检用sys.dm_db_partition_stats、精确对账用COUNT(*)、月度校准用DBCC UPDATEUSAGE三个口径各司其职。再之后类似的争论就少了很多。还有一个我个人的习惯是每当业务方跟我约时间讨论数据库容量时我一定提前准备好最近几个月的表增长快照不只是给一个当前大小。这样对方能看到趋势沟通起来更高效。比起临时跑一条SQL然后说现在1.5亿行三个月前1.2亿行、月均增长1000万行这种说法显然更有决策价值。另外如果你的环境是云数据库很多云厂商控制台自带一键查看表空间的功能但要注意它们底层大多也是调用类似sys.dm_db_partition_stats的方式同样存在统计口径的问题。控制台给出的数字方便看但真要做决策还是用自己掌握的这套脚本更靠谱至少你知道每个数字背后的计算逻辑。这套方案从单表查询到全库汇总、从历史快照到存储过程封装基本覆盖了我日常90%以上的查看数据量需求。你拿过去之后建议先在测试库完整跑一遍对照一下每张表的实际感受——比如选一张你自己清楚大概行数的表比对统计结果是否一致。确认没问题再上生产过程中如果遇到跟我不一样的场景顺着系统视图往下查一般都能找到原因。