如何在SQL Server数据库中找到最大的对象？ [英] How to find largest objects in a SQL Server database?

问题描述

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

有没有任何工具来帮助这种数据库分析？

解决方案

我一直在使用这个SQL脚本从某人，某处 - 不能重建谁来自），它帮助我有一定的理解和确定指数和表的大小：

  SELECT 
 t.NAME as TableName，
 i.name作为indexName，
 sum（p.rows）作为RowCounts，
 sum total_subages）作为TotalPages，
 sum（a.used_pa​​ges）作为UsedPages，
 sum（a.data_pages）作为DataPages，
（sum（a.total_pages）* 8）/ 1024作为TotalSpaceMB， 
（sum（a.used_pa​​ges）* 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和i.index_id = p .index_id 
 INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id 
 WHERE 
 t.NAME不喜欢'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当然，您可以使用另一个排序标准，例如
 $ b（ 
   
 $ b 
  ORDER BY SUM（p.rows）DESC 
  
 
 b $ b 
获取最多行的表，或
  ORDER BY SUM（a.total_pages）DESC 
  
以获得使用最多页面（8K块）的表。
 
How would I go about finding the largest objects in a SQL Server database?  First, by determining which tables (and related indices) are the largest and then determining which rows in a particular table are largest (we're storing binary data in BLOBs)?

Are there any tools out there for helping with this kind of database analysis?  Or are there some simple queries I could run against the system tables?
解决方案
I've been using this SQL script (which I got from someone, somewhere - can't reconstruct who it came from) for ages and it's helped me quite a bit understanding and determining the size of indices and tables:
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) 
Of course, you can use another ordering criteria, e.g.
ORDER BY SUM(p.rows) DESC
to get the tables with the most rows, or 
ORDER BY SUM(a.total_pages) DESC
to get the tables with the most pages (8K blocks) used.

                        
这篇关于如何在SQL Server数据库中找到最大的对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！