Pregunta Tamaño de tabla e índice en SQL Server


¿Podemos tener una consulta SQL que básicamente ayudará a visualizar los tamaños de tabla e índice en SQl Server?

¿Cómo SQL Server mantiene el uso de memoria para tablas / índices?


73
2017-11-25 09:40


origen


Respuestas:


los exec sp_spaceused sin parámetro muestra el resumen de toda la base de datos. La solución foreachtable genera un conjunto de resultados por tabla, que SSMS podría no ser capaz de manejar si tiene demasiadas tablas.

Creé un guión que recoge las informaciones de la mesa a través de sp_spaceused y muestra un resumen en un único conjunto de registros, ordenados por tamaño.

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

66
2017-11-25 10:38



sp_spaceused te da el tamaño de todos los índices combinados.

Si desea el tamaño de cada índice para una tabla, use una de estas dos consultas:

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

Los resultados suelen ser ligeramente diferentes, pero dentro del 1%.


108
2018-03-11 16:04



En SQL 2012 obtener esta información a nivel de tabla se ha vuelto deliciosamente simple:

SQL Management Studio -> Haga clic derecho en Db -> Informes -> Informes estándar -> ¡Uso de disco por tabla!

Disfrutar


16
2018-01-08 13:44



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

13
2017-11-25 10:09



Aquí hay una versión más compacta de la respuesta más exitosa:

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

3
2017-11-13 22:59



Ha pasado mucho tiempo desde la creación de esta publicación, pero quería compartir mi 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

funciona para:

  • SQL Server (comenzando en 2008)
  • Incluye información para todas las tablas por base de datos actual

3
2018-06-15 01:30



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

2
2017-08-31 08:26



Hay un procedimiento almacenado extendido sp_spaceused eso saca esta información. Es bastante intrincado hacerlo desde el diccionario de datos, pero Este enlace se entusiasma con un guión que lo hace. Esta pregunta sobre el stackoverflow tiene una gran cantidad de información sobre las estructuras de datos subyacentes que puede usar para construir estimaciones de tamaños de tablas e índices para la planificación de capcity.


0
2017-11-25 10:11



Esta consulta proviene de otras dos respuestas:

Obtenga el tamaño de todas las tablas en la base de datos

¿Cómo encontrar objetos más grandes en una base de datos de SQL Server?

, pero mejoré esto para ser universal. Usa sys.objects diccionario:

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
;

0
2018-03-28 11:44