기본적으로 SQl Server에서 테이블 및 인덱스 크기를 보는 데 도움이되는 SQL 쿼리를 가질 수 있습니까?
SQL Server는 테이블 / 인덱스에 대한 메모리 사용량을 어떻게 유지합니까?
답변
exec sp_spaceused매개 변수없이 전체 데이터베이스에 대한 요약 정보를 보여줍니다. foreachtable 솔루션은 테이블 당 하나의 결과 집합을 생성합니다. 테이블이 너무 많으면 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
답변
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 % 이내입니다.
답변
SQL 2012에서는 테이블 수준에서이 정보를 얻는 것이 매우 간단 해졌습니다.
SQL Management Studio-> Db-> Reports-> Standard Reports-> Disk usage by table!
즐겨
답변
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
답변
--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
답변
이 게시물을 작성한 지 오래되었지만 스크립트를 공유하고 싶었습니다.
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부터)
- 현재 데이터베이스 당 모든 테이블에 대한 정보를 포함합니다.
