SQL Server의 테이블 및 인덱스 크기

SQL Server의 테이블 및 인덱스 크기

SQL Server에서 테이블과 인덱스 크기를 표시하는데 기본적으로 도움이 되는 SQL 쿼리를 받을 수 있을까요?

SQL Server는 테이블/인덱스의 메모리 사용량을 어떻게 유지합니까?

sp_spaceused는 결합된 모든 인덱스의 크기를 제공합니다.

테이블의 각 인덱스의 크기를 원하는 경우 다음 두 가지 쿼리 중 하나를 사용합니다.

SELECT
    i.name                  AS IndexName,
    SUM(s.used_page_count) * 8   AS IndexSizeKB
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.indexes                AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name

SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

결과는 보통 약간 다르지만 1% 이내입니다.

그exec sp_spaceusedwithout parameter는 데이터베이스 전체의 요약을 나타냅니다.포어치 테이블솔루션은 테이블당1개의 결과 세트를 생성합니다.테이블이 너무 많으면 SSMS는 이 결과를 처리하지 못할 수 있습니다.

테이블 정보를 수집하는 스크립트를 만들었습니다.sp_spaceused는 사이즈별로 정렬된 단일 레코드 세트의 요약을 표시합니다.

create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

SQL 2012에서는 테이블 수준에서 이 정보를 쉽게 얻을 수 있습니다.

SQL Management Studio -> [ Db ]-> [ Reports ]-> [ Standard Reports ]-> [테이블별 디스크 사용량]를 오른쪽 클릭합니다.

즐거운 시간 되세요.

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

이 게시물이 만들어진 지 오래되었지만, 저는 제 스크립트를 공유하고 싶었습니다.

WITH CteIndex
AS
(
SELECT 
     reservedpages = (reserved_page_count)
     ,usedpages = (used_page_count)
     ,pages = (
            CASE
                WHEN (s.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
            )    
     ,s.object_id   
     ,i.index_id        
     ,i.type_desc AS IndexType
     ,i.name AS indexname
    FROM sys.dm_db_partition_stats s
    INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id   
)
SELECT DISTINCT
DB_NAME(DB_ID()) AS DatabaseName
,o.name AS TableName
,o.object_id
,ct.indexname
,ct.IndexType
,ct.index_id
, IndexSpace = LTRIM (STR ((CASE WHEN usedpages > pages THEN CASE WHEN ct.index_id < 2 THEN  pages ELSE (usedpages - pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
FROM CteIndex ct
INNER JOIN sys.objects o ON o.object_id = ct.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
AND ps.index_id = ct.index_id
ORDER BY name ASC

이것은, 다음의 경우에 유효합니다.

• SQL Server (2008년 이후)
• 현재 데이터베이스별 모든 테이블에 대한 정보 포함

--Gets the size of each index for the specified table
DECLARE @TableName sysname = N'SomeTable';

SELECT i.name AS IndexName
      ,8 * SUM(s.used_page_count) AS IndexSizeKB
FROM sys.indexes AS i
    INNER JOIN sys.dm_db_partition_stats AS s 
        ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
WHERE s.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;

SELECT i.name AS IndexName
      ,8 * SUM(a.used_pages) AS IndexSizeKB
FROM sys.indexes AS i
    INNER JOIN sys.partitions AS p 
        ON i.[object_id]  = p.[object_id] AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units AS a 
        ON p.partition_id = a.container_id
WHERE i.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;

가장 성공한 답변의 보다 콤팩트한 버전을 다음에 나타냅니다.

create table #tbl(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'

select * from #tbl
    order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #tbl

이 쿼리는 다음 두 가지 답변에서 얻을 수 있습니다.

데이터베이스에 있는 모든 테이블의 크기를 가져옵니다.

SQL Server 데이터베이스에서 가장 큰 개체를 찾는 방법

하지만 저는 이것을 보편화하도록 강화했습니다.사용하다sys.objects사전:

SELECT 
    s.NAME as SCHEMA_NAME,
    t.NAME AS OBJ_NAME,
    t.type_desc as OBJ_TYPE,
    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.objects t
INNER JOIN
    sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID 
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 
    s.NAME, t.NAME, t.type_desc, i.object_id, i.index_id, i.name 
ORDER BY
    sum(a.total_pages) DESC
;

확장 저장 프로시저가 있습니다.sp_spaceused이 정보를 유출할 수 있습니다.데이터 사전에서 이 작업을 수행하는 것은 상당히 복잡하지만, 이 작업을 수행하는 스크립트에 팬을 연결합니다.이 stackoverflow 질문에서는 캐퍼시티 플래닝에 사용할 테이블 및 인덱스사이즈의 견적을 작성하기 위해 사용할 수 있는 기초 데이터 구조에 대한 정보를 제공합니다.

단일 테이블(및 해당 인덱스) 저장소 데이터를 보려면:

exec sp_spaceused MyTable

언급URL : https://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server