1. 数据库容量监控的必要性

在SQL Server数据库日常运维中，掌握各表的数据量分布是DBA的基础技能。上周处理一个性能问题时，发现某业务表数据量已突破2000万行却未建立合适索引，导致报表查询超时。通过系统视图快速定位问题表后，我们立即采取了分表措施。这个案例让我意识到，定期统计表数据量应该成为数据库健康检查的常规操作。

2. 系统视图解析方案

2.1 sys.partitions核心字段解读

SELECT t.name AS 表名, s.name AS 架构名, p.rows AS 数据行数 FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE p.index_id IN (0,1) -- 0代表堆表，1代表聚集索引

这个查询的关键在于理解partition表的存储特性：

• rows字段记录的是分区级行数估计值
• 当表存在分区时，需要按object_id聚合计算总和
• index_id过滤确保不重复计算非聚集索引

2.2 空间占用统计技巧

SELECT t.name AS 表名, SUM(a.total_pages) * 8 AS 总空间KB, SUM(a.used_pages) * 8 AS 已用空间KB FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.object_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id GROUP BY t.name

注意：该查询需要在用户数据库上下文执行，master库无法获取其他库的存储信息

3. 增强型统计脚本

3.1 包含索引的完整统计

DECLARE @TableInfo TABLE ( 表名 NVARCHAR(128), 架构名 NVARCHAR(128), 行数 BIGINT, 保留空间KB DECIMAL(18,2), 数据空间KB DECIMAL(18,2), 索引空间KB DECIMAL(18,2), 未使用空间KB DECIMAL(18,2) ) INSERT INTO @TableInfo EXEC sp_MSforeachtable ' USE [?]; SELECT OBJECT_NAME(i.object_id) AS 表名, SCHEMA_NAME(o.schema_id) AS 架构名, SUM(p.rows) AS 行数, SUM(a.total_pages) * 8 AS 保留空间KB, SUM(a.data_pages) * 8 AS 数据空间KB, (SUM(a.total_pages) - SUM(a.data_pages)) * 8 AS 索引空间KB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 未使用空间KB FROM sys.indexes i JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.partition_id = a.container_id JOIN sys.objects o ON i.object_id = o.object_id WHERE i.object_id > 100 AND o.type = ''U'' GROUP BY i.object_id, o.schema_id ' SELECT * FROM @TableInfo ORDER BY 行数 DESC

3.2 结果解读要点

1. 行数统计：对分区表显示的是所有分区总和
2. 空间计算：
   • 保留空间 = 数据空间 + 索引空间 + 未使用空间
   • 当未使用空间占比超过20%时建议重建索引
3. 系统表过滤：object_id>100排除了系统对象

4. 自动化监控方案

4.1 创建历史记录表

CREATE TABLE dbo.TableSizeHistory ( RecordID INT IDENTITY(1,1), CaptureDate DATETIME DEFAULT GETDATE(), TableName NVARCHAR(128), SchemaName NVARCHAR(128), RowCount BIGINT, ReservedKB DECIMAL(18,2), DataKB DECIMAL(18,2), IndexKB DECIMAL(18,2), UnusedKB DECIMAL(18,2) )

4.2 配置定时作业

USE msdb GO EXEC dbo.sp_add_job @job_name = N'TableSizeMonitor' EXEC sp_add_jobstep @job_name = N'TableSizeMonitor', @step_name = N'Capture Table Sizes', @subsystem = N'TSQL', @command = N' INSERT INTO YourDB.dbo.TableSizeHistory ( TableName, SchemaName, RowCount, ReservedKB, DataKB, IndexKB, UnusedKB ) EXEC sp_MSforeachtable @command1='' SELECT OBJECT_NAME(i.object_id), SCHEMA_NAME(o.schema_id), SUM(p.rows), SUM(a.total_pages) * 8, SUM(a.data_pages) * 8, (SUM(a.total_pages)-SUM(a.data_pages)) * 8, (SUM(a.total_pages)-SUM(a.used_pages)) * 8 FROM sys.indexes i JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id JOIN sys.allocation_units a ON p.partition_id=a.container_id JOIN sys.objects o ON i.object_id=o.object_id WHERE o.type=''''U'''' GROUP BY i.object_id, o.schema_id '' ', @database_name = N'YourDB'

5. 性能优化实践

5.1 分区表特殊处理

对于分区超过50个的大型表，建议改用采样统计：

SELECT OBJECT_NAME(object_id) AS 表名, SUM(row_count) AS 预估行数 FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('YourPartitionedTable') GROUP BY object_id

5.2 增量统计策略

-- 创建变化跟踪表 CREATE TABLE dbo.TableChangeTracking ( TableName NVARCHAR(128) PRIMARY KEY, LastRowCount BIGINT, ChangePercentage DECIMAL(5,2), LastUpdated DATETIME ) -- 每日增量分析 UPDATE t SET t.LastRowCount = c.RowCount, t.ChangePercentage = ABS(c.RowCount - t.LastRowCount)*100.0/NULLIF(t.LastRowCount,0), t.LastUpdated = GETDATE() FROM dbo.TableChangeTracking t JOIN ( SELECT OBJECT_NAME(object_id) AS TableName, SUM(row_count) AS RowCount FROM sys.dm_db_partition_stats GROUP BY object_id ) c ON t.TableName = c.TableName WHERE ABS(c.RowCount - t.LastRowCount) > t.LastRowCount*0.1 -- 变化超过10%

6. 可视化分析方案

6.1 Power BI数据模型

1. 创建以下DAX度量值：

数据量占比 = DIVIDE( SUM('TableSizeHistory'[DataKB]), CALCULATE( SUM('TableSizeHistory'[DataKB]), ALLSELECTED('TableSizeHistory'[TableName]) ) ) 环比增长率 = VAR CurrentPeriod = SUM('TableSizeHistory'[RowCount]) VAR PreviousPeriod = CALCULATE( SUM('TableSizeHistory'[RowCount]), DATEADD('TableSizeHistory'[CaptureDate], -1, MONTH) ) RETURN DIVIDE(CurrentPeriod - PreviousPeriod, PreviousPeriod)

6.2 预警阈值设置

-- 创建空间预警规则 CREATE PROCEDURE dbo.CheckTableGrowth AS BEGIN DECLARE @Result TABLE ( TableName NVARCHAR(128), GrowthRate DECIMAL(10,2), CurrentSizeGB DECIMAL(10,2) ) INSERT INTO @Result SELECT TOP 5 TableName, (MAX(RowCount)-MIN(RowCount))*100.0/NULLIF(MIN(RowCount),0) AS GrowthRate, MAX(ReservedKB)/1024.0/1024 AS CurrentSizeGB FROM dbo.TableSizeHistory WHERE CaptureDate >= DATEADD(DAY, -7, GETDATE()) GROUP BY TableName HAVING MAX(RowCount) > 1000000 ORDER BY GrowthRate DESC IF EXISTS (SELECT 1 FROM @Result WHERE GrowthRate > 50) BEGIN -- 触发邮件警报 EXEC msdb.dbo.sp_send_dbmail @recipients = 'dba@yourcompany.com', @subject = '数据库表空间增长预警', @query = 'SELECT * FROM @Result WHERE GrowthRate > 50' END END

7. 企业级解决方案

7.1 使用扩展事件跟踪

CREATE EVENT SESSION [TableGrowthTracking] ON SERVER ADD EVENT sqlserver.object_altered( WHERE ([object_type]='U') ), ADD EVENT sqlserver.object_created( WHERE ([object_type]='U') ) ADD TARGET package0.event_file( SET filename=N'TableGrowthTracking' ) WITH ( MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS )

7.2 数据仓库集成方案

-- 在数据仓库中创建星型模型 CREATE TABLE dim.TableInfo ( TableSK INT IDENTITY(1,1) PRIMARY KEY, TableName NVARCHAR(128) NOT NULL, SchemaName NVARCHAR(128) NOT NULL, IsPartitioned BIT DEFAULT 0, CreateDate DATETIME, LastModifiedDate DATETIME ) CREATE TABLE fact.TableSizeHistory ( HistorySK INT IDENTITY(1,1) PRIMARY KEY, TableSK INT FOREIGN KEY REFERENCES dim.TableInfo(TableSK), SnapshotDate DATETIME NOT NULL, RowCount BIGINT NOT NULL, SizeKB DECIMAL(18,2) NOT NULL, IndexCount INT DEFAULT 0 ) -- 使用SSIS包定期加载数据

8. 性能对比实测

在包含500张表的OLTP环境测试结果：

关键发现：sys.partitions方案在精度和性能间取得最佳平衡，适合生产环境使用