Размер таблицы и индекса в SQL Server

Ответ 1

exec sp_spaceused Без параметра отображается сводка для всей базы данных. Решение foreachtable генерирует один результат для каждой таблицы - SSMS, возможно, не сможет справиться, если у вас слишком много таблиц.

Я создал script, который собирает информацию таблицы через 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

Ответ 2

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%.

Ответ 3

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

Ответ 4

В SQL 2012 получение этой информации на уровне таблицы стало очень простым:

SQL Management Studio → Щелкните правой кнопкой мыши по Db → Отчеты → Стандартные отчеты → Использование диска по таблице!

Enjoy

Ответ 5

Вот более компактная версия наиболее успешного ответа:

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

Ответ 6

его долгое время с момента создания для этого сообщения, но я хотел поделиться своим script:

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 года)
• Включает информацию для всех таблиц в текущей базе данных

Ответ 7

--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;

Ответ 8

Существует расширенная хранимая процедура sp_spaceused, которая выводит эту информацию. Он довольно запутан, чтобы сделать это из словаря данных, но Эта ссылка отдает предпочтение script, которая делает это. fooobar.com/info/12159/... имеет некоторую фанатичную информацию о базовых структурах данных, которые можно использовать для построения оценок размеров таблиц и индексов для планирования размера.