SQL Server 데이터베이스에서 가장 큰 개체를 찾는 방법은 무엇입니까?

SQL Server 데이터베이스에서 가장 큰 개체를 찾는 방법은 무엇입니까?

SQL Server 데이터베이스에서 가장 큰 개체를 찾으려면 어떻게 해야 합니까?먼저 가장 큰 테이블(및 관련 인덱스)을 결정한 다음 특정 테이블에서 가장 큰 행을 결정합니다(이진 데이터를 BLOB에 저장).

이런 종류의 데이터베이스 분석을 지원하는 도구가 있습니까?아니면 시스템 테이블에 대해 실행할 수 있는 간단한 쿼리가 있습니까?

저는 오랫동안 이 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 서버 2008에서는 상위 테이블별 표준 리포트 Disk Usage를 실행할 수도 있습니다.이것은 DB를 마우스 오른쪽 단추로 클릭하고 Reports->Standard Reports를 선택한 후 원하는 보고서를 선택하여 찾을 수 있습니다.

이 쿼리는 현재 연결에서 가장 큰 테이블을 찾는 데 도움이 됩니다.

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

@marc_s의 답변은 매우 훌륭하며 몇 년 동안 사용해 왔습니다.그러나 스크립트가 일부 열 저장소 인덱스의 데이터를 누락하고 완전한 그림을 표시하지 않는다는 것을 발견했습니다.예를 들어 할 때SUM(TotalSpace)스크립트와 비교하여 Management Studio의 총 공간 데이터베이스 속성을 비교합니다(Management Studio의 경우 숫자가 더 큽니다).는 이 하기 위해 를 수정하고 했습니다.: 문제를해결위기수조확다장니습했이금고하정를트스립크해하▁i다▁to▁it.

select
    tables.[name] as table_name,
    schemas.[name] as schema_name,
    isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown') as database_name,
    sum(allocation_units.total_pages) * 8 as total_space_kb,
    cast(round(((sum(allocation_units.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as total_space_mb,
    sum(allocation_units.used_pages) * 8 as used_space_kb,
    cast(round(((sum(allocation_units.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as used_space_mb,
    (sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8 as unused_space_kb,
    cast(round(((sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as unused_space_mb,
    count(distinct indexes.index_id) as indexes_count,
    max(dm_db_partition_stats.row_count) as row_count,
    iif(max(isnull(user_seeks, 0)) = 0 and max(isnull(user_scans, 0)) = 0 and max(isnull(user_lookups, 0)) = 0, 1, 0) as no_reads,
    iif(max(isnull(user_updates, 0)) = 0, 1, 0) as no_writes,
    max(isnull(user_seeks, 0)) as user_seeks,
    max(isnull(user_scans, 0)) as user_scans,
    max(isnull(user_lookups, 0)) as user_lookups,
    max(isnull(user_updates, 0)) as user_updates,
    max(last_user_seek) as last_user_seek,
    max(last_user_scan) as last_user_scan,
    max(last_user_lookup) as last_user_lookup,
    max(last_user_update) as last_user_update,
    max(tables.create_date) as create_date,
    max(tables.modify_date) as modify_date
from 
    sys.tables
    left join sys.schemas on schemas.schema_id = tables.schema_id
    left join sys.indexes on tables.object_id = indexes.object_id
    left join sys.partitions on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id
    left join sys.allocation_units on partitions.partition_id = allocation_units.container_id
    left join sys.dm_db_index_usage_stats on tables.object_id = dm_db_index_usage_stats.object_id and indexes.index_id = dm_db_index_usage_stats.index_id
    left join sys.dm_db_partition_stats on tables.object_id = dm_db_partition_stats.object_id and indexes.index_id = dm_db_partition_stats.index_id
group by schemas.[name], tables.[name], isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown')
order by 5 desc

누군가에게 도움이 되기를 바랍니다.이 스크립트는 수백 개의 다른 테이블, 인덱스 및 스키마를 사용하는 대규모 TB 전체 데이터베이스를 대상으로 테스트되었습니다.

다음 코드를 사용할 수도 있습니다.

USE AdventureWork
GO
CREATE TABLE #GetLargest 
(
  table_name    sysname ,
  row_count     INT,
  reserved_size VARCHAR(50),
  data_size     VARCHAR(50),
  index_size    VARCHAR(50),
  unused_size   VARCHAR(50)
)

SET NOCOUNT ON

INSERT #GetLargest

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT 
  a.table_name,
  a.row_count,
  COUNT(*) AS col_count,
  a.data_size
  FROM #GetLargest a
     INNER JOIN information_schema.columns b
     ON a.table_name collate database_default
     = b.table_name collate database_default
       GROUP BY a.table_name, a.row_count, a.data_size
       ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #GetLargest

SQL Server Management Studio 2008을 사용하는 경우 개체 탐색기 세부 정보 창에서 볼 수 있는 특정 데이터 필드가 있습니다.테이블 폴더를 찾아 선택하기만 하면 됩니다.세부 정보 보기에서 열 제목을 마우스 오른쪽 버튼으로 클릭하고 "보고서"에 필드를 추가할 수 있습니다.SSMS 2008 익스프레스를 이용하시는 경우 마일리지가 달라질 수 있습니다.

이 쿼리는 SqlServerCentral에서도 매우 유용합니다. 여기 원본 게시물에 대한 링크가 있습니다.

SQL Server 최대 테이블

  select name=object_schema_name(object_id) + '.' + object_name(object_id)
, rows=sum(case when index_id < 2 then row_count else 0 end)
, reserved_kb=8*sum(reserved_page_count)
, data_kb=8*sum( case 
     when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count 
     else lob_used_page_count + row_overflow_used_page_count 
    end )
, index_kb=8*(sum(used_page_count) 
    - sum( case 
           when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count 
        else lob_used_page_count + row_overflow_used_page_count 
        end )
     )    
, unused_kb=8*sum(reserved_page_count-used_page_count)
from sys.dm_db_partition_stats
where object_id > 1024
group by object_id
order by 
rows desc   

제 데이터베이스에서 그들은 이 질의와 첫 번째 답변 사이에 다른 결과를 제공했습니다.

누군가 유용한 것을 발견하길 바랍니다.

언급URL : https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database