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

本文是小编为大家收集整理的关于如何找到SQL Server数据库中最大的对象?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

如何在SQL Server数据库中找到最大的对象?首先,通过确定哪些表(及相关索引)是最大的,然后确定特定表中的哪个行是最大的(我们将二进制数据存储在斑点中)?

是否有任何工具可以帮助进行此类数据库分析?还是我可以在系统表上运行一些简单的查询?

推荐答案

我一直在使用此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块)的表格.

其他推荐答案

在SQL Server 2008中,您还可以通过顶表运行标准报告磁盘使用情况.这可以通过右键单击 DB,选择报告 - >标准报告并选择您想要的报告.

其他推荐答案

此查询有助于找到您的最大表格.

SELECT  TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

本文地址:https://www.itbaoku.cn/post/597384.html

问题描述

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.

其他推荐答案

In SQL Server 2008, you can also just run the standard report Disk Usage by Top Tables. This can be found by right clicking the DB, selecting Reports->Standard Reports and selecting the report you want.

其他推荐答案

This query help to find largest table in you are connection.

SELECT  TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC