Início / dba / Perguntas / 284247
Accepted
arthur
Asked: 2021-01-30 01:31:42 +0800 CST

Tamanho da tabela sem índices no sql server

  • 772

Usando a seguinte consulta, posso obter o tamanho dos índices de uma tabela:

SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), 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

Se eu executar sp_spaceused 'schema.table'o tamanho sem índices corresponde à datacoluna?

Alguma maneira de obter o tamanho da tabela sem índices no sql server? ( SELECTa declaração seria boa, pois gostaria de conectar essa consulta a uma consulta maior).

Se eu executar Get size of all tables in database , os tamanhos das tabelas são quase os mesmos dos tamanhos dos índices (sugerindo que algo está errado).

sql-server size

2 respostas

  1. Best Answer

    Existem várias maneiras de encontrar o tamanho de uma tabela no banco de dados. A primeira e mais fácil maneira é clicar com o botão direito do mouse na tabela e verificar a guia de armazenamento e isso fornece espaço de dados e espaço de índice. O espaço de dados nada mais é do que o tamanho dos dados na tabela e, da mesma forma, o espaço de índice fornece o tamanho do índice na tabela mencionada.

    Armazenamento de mesa

    As informações acima corresponderiam exatamente ao comando sp_spaceused 'schema_name.table_name'Podemos verificar o mesmo para a Tabela de usuários (no esquema dbo e no banco de dados StackOverFlow).

    sp_spaceused

    Aqui, se você adicionar dados, tamanho do índice e não utilizado (em KB), obterá espaço reservado por tabela.

    Você também pode verificar o tamanho da tabela e do índice executando o comando sp_blitzindex(do Sr. Brent Ozar). O comando ficaria como abaixo:

    exec sp_BlitzIndex  @SchemaName = 'dbo', @TableName = 'Users'

    Aqui, você pode verificar a coluna Tamanho. O tamanho do índice clusterizado fornecerá o tamanho da tabela e você também poderá encontrar o tamanho de cada índice, bem como seu uso (se estiver interessado).

    Além disso, você também pode verificar o tamanho da tabela de todos eles entrando em Relatórios conforme abaixo:

    Relatório padrão

    Você também pode executar a instrução select abaixo para obter o tamanho total e o tamanho do índice de todas as tabelas:

    ;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.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) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

    Se você quiser apenas os tamanhos da tabela, execute o seguinte:

    SELECT
t.name as TableName
, SUM (CASE WHEN (i.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) * 8 as table_size_kb
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
where t.object_id = OBJECT_ID('schema.table')
GROUP BY t.name

    Espero que isto ajude.

    • 6

  2. O DMV retorna as informações que você deseja, você só precisa saber que index_id = 0 é para Heaps e que Index_id 1 é para o índice clusterizado

    São esses que são relatados como dados na saída de sp_spaceused

    usar tempdb

    create table heap(id int identity(1,1), txt nvarchar(max) default 'hahaha')

    inserir valores de heap (txt) ('um')

    selecione object_name(object_id) como objname, * FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s onde index_id = 0

    • 2

relate perguntas