¡Hola, chicos!
En esta publicación muy rápida, me gustaría compartir con ustedes un script simple que muestra cómo enumerar las tablas e índices más grandes en la base de datos y devolver el tamaño de cada tabla y cada índice, y también puedo enumerar los objetos de todas las bases de datos en su instancia de SQL Server.

Ya había creado algunos artículos relacionados con los tamaños de los archivos de datos y los tamaños de los discos, pero no tenía un artículo específico para los tamaños de las tablas de índice, así que pensé que era hora de crear algo así.

También había compartido este mismo guión en el artículo. SQL Server: consultas de DBA útiles para el día a día que siempre debe buscar en Internet, pero la indexación no funciona tan bien como para encontrar este script aquí.

Vea aquí algunos otros artículos relacionados con el espacio en disco:

Para enumerar las tablas más grandes de la base de datos y devolver el tamaño de cada tabla, simplemente ejecute el siguiente script en la base de datos deseada:

SELECT TOP 100
    s.[name] AS [schema],
    t.[name] AS [table_name],
    p.[rows] AS [row_count],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM 
    sys.tables t
    JOIN sys.indexes i ON t.[object_id] = i.[object_id]
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE 
    t.is_ms_shipped = 0
    AND i.[object_id] > 255 
GROUP BY
    t.[name], 
    s.[name], 
    p.[rows]
ORDER BY 
    [size_mb] DESC

Resultado:

Si desea enumerar las tablas más grandes, considerando todas las bases de datos de su instancia, utilice el siguiente script:

IF (OBJECT_ID('tempdb..#tamanho_tabelas') IS NOT NULL) DROP TABLE #tamanho_tabelas
CREATE TABLE #tamanho_tabelas (
    [database]   NVARCHAR(256),
    [schema]     NVARCHAR(256),
    [table_name] NVARCHAR(256),
    [row_count]  BIGINT,
    [size_mb]    DECIMAL(36, 2),
    [used_mb]    DECIMAL(36, 2),
    [unused_mb]  DECIMAL(36, 2)
)

INSERT INTO #tamanho_tabelas
EXEC sys.[sp_MSforeachdb] '
IF (''?'' NOT IN (''model'', ''master'', ''tempdb'', ''msdb''))
BEGIN

    SELECT TOP 100
        ''?'' AS [database],
        s.[name] AS [schema],
        t.[name] AS [table_name],
        p.[rows] AS [row_count],
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
    FROM 
        [?].sys.tables t
        JOIN [?].sys.indexes i ON t.[object_id] = i.[object_id]
        JOIN [?].sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
        JOIN [?].sys.allocation_units a ON p.[partition_id] = a.container_id
        LEFT JOIN [?].sys.schemas s ON t.[schema_id] = s.[schema_id]
    WHERE 
        t.is_ms_shipped = 0
        AND i.[object_id] > 255 
    GROUP BY
        t.[name], 
        s.[name], 
        p.[rows]
    ORDER BY
        5 DESC
        
END'

SELECT TOP 100 * 
FROM [#tamanho_tabelas]
ORDER BY [size_mb] DESC

Resultado:

Otra necesidad muy común es encontrar los índices más grandes del banco para analizar si siguen siendo útiles o si están ocupando mucho espacio innecesariamente.

Para hacer esto, simplemente use el siguiente script para devolver la lista de los índices más grandes en la base de datos:

SELECT TOP(100)
    s.[name] AS [schema],
    t.[name] AS [table_name],
    i.[name] AS [index_name],
    i.[type_desc],
    p.[rows] AS [row_count],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM
    sys.tables t
    JOIN sys.indexes i ON t.[object_id] = i.[object_id]
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE 
    t.is_ms_shipped = 0
    AND i.[object_id] > 255 
GROUP BY
    t.[name], 
    s.[name],
    i.[name],
    i.[type_desc],
    p.[rows]
ORDER BY 
    [size_mb] DESC

Resultado:

Y si desea realizar la misma consulta en todos los bancos de su instancia, utilice este script aquí:

IF (OBJECT_ID('tempdb..#tamanho_indices') IS NOT NULL) DROP TABLE #tamanho_indices
CREATE TABLE #tamanho_indices (
    [database]   NVARCHAR(256),
    [schema]     NVARCHAR(256),
    [table_name] NVARCHAR(256),
    [index_name] NVARCHAR(256),
    [index_type] NVARCHAR(50),
    [row_count]  BIGINT,
    [size_mb]    DECIMAL(36, 2),
    [used_mb]    DECIMAL(36, 2),
    [unused_mb]  DECIMAL(36, 2)
)

INSERT INTO #tamanho_indices
EXEC sys.[sp_MSforeachdb] '
IF (''?'' NOT IN (''model'', ''master'', ''tempdb'', ''msdb''))
BEGIN

    SELECT TOP(100)
        ''?'' AS [database],
        s.[name] AS [schema],
        t.[name] AS [table_name],
        i.[name] AS [index_name],
        i.[type_desc],
        p.[rows] AS [row_count],
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
    FROM
        [?].sys.tables t
        JOIN [?].sys.indexes i ON t.[object_id] = i.[object_id]
        JOIN [?].sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
        JOIN [?].sys.allocation_units a ON p.[partition_id] = a.container_id
        LEFT JOIN [?].sys.schemas s ON t.[schema_id] = s.[schema_id]
    WHERE 
        t.is_ms_shipped = 0
        AND i.[object_id] > 255 
    GROUP BY
        t.[name], 
        s.[name],
        i.[name],
        i.[type_desc],
        p.[rows]
    ORDER BY
        7 DESC
        
END'

SELECT TOP 100 * 
FROM [#tamanho_indices]
ORDER BY [size_mb] DESC

Resultado:

¡Y eso es todo, amigos!
Espero que te haya gustado este tip, un fuerte abrazo y ¡hasta la próxima!