sql-server - 如何在 SQL Server 数据库中查找最大的对象？

Question

我将如何着手寻找 SQL Server 数据库中最大的对象？首先，通过确定哪些表(和相关索引)最大，然后确定特定表中的哪些行最大(我们将二进制数据存储在 BLOB 中)？

是否有任何工具可以帮助进行此类数据库分析？或者是否有一些我可以针对系统表运行的简单查询？

Answer 1

我一直在使用这个 SQL 脚本(我从某个地方的某个人那里得到的 - 无法重建它来自谁)多年，它帮助我理解和确定索引和表的大小:

SELECT 
    t.name AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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.name NOT LIKE 'dt%' AND
    i.object_id > 255 AND  
    i.index_id <= 1
GROUP BY 
    t.name, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id) 

当然，您可以使用其他排序标准，例如

ORDER BY SUM(p.rows) DESC

获取行数最多的表，或者

ORDER BY SUM(a.total_pages) DESC

获取使用最多页面(8K block )的表。