Como encontrar o espaço usado por tabelas e índices no SQL Server – Parte 3

Como encontrar o espaço usado por tabelas e índices no SQL Server – Parte 3

Como encontrar o espaço usado por tabelas e índices no SQL Server - Parte 3 1
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Por: Aaron Bertrand | Atualizado: 2020-05-26 | Comentários | Palavras-chave: 1 | 2 3 Mais> Administração de banco de dados

Problema

No

Parte 1, escrevi
sobre a descoberta de mais detalhes sobre grandes tabelas do SQL Server e
no

Parte 2, mostrei um procedimento armazenado do SQL Server que facilita a montagem de todas essas informações
em um único conjunto de resultados. Esse procedimento, no entanto, assume que você já sabe qual
Tabela (s) do SQL Server para investigar. E se você souber apenas que os arquivos de um determinado
Banco de dados do SQL Server
estão crescendo ou parecem mal configurados? Não seria bom encontrar as mesas
nesse banco de dados com o maior tamanho em disco ou o maior número de linhas?

Solução

Eu olhei para a solução de
Parte 2, e percebi que levaria pouco
mais do que um procedimento de invólucro para atingir meu objetivo. O conceito é que você passa
no nome do banco de dados, o número de tabelas que você deseja retornar e se
você deseja classificar por tamanho ou por contagem de linhas. O procedimento executaria uma consulta no
sys.partitions ou sys.dm_db_database_page_allocations para determinar as n tabelas principais e, em seguida –
cada tabela da lista – chame o procedimento original. Isso produziria
n conjuntos de resultados, cada um mostrando como uma tabela é distribuída
nos arquivos e grupos de arquivos.

Outra abordagem poderia ser alterar o procedimento original
dinamizar cada tabela e seus índices em um único conjunto de resultados, mas isso pode ser mais
difícil de criar e consumir.

Leia Também  Visão geral de soluções e projetos no SSMS

Antes de mostrar o código no procedimento de wrapper que criei, vamos criar
um banco de dados simples com dois grupos de arquivos, três arquivos e duas tabelas, apenas para manter
os resultados explicativos, mas digeríveis. Aqui está o banco de dados:

CREATE DATABASE gronk 
ON PRIMARY
  (name = N'gronk_data1'filename = N'/var/opt/mssql/data/gronk1.mdf',    size = 10 MB),
FILEGROUP SECONDARY
  (name = N'gronk_data2a', filename = N'/var/opt/mssql/data/gronk2a.mdf',   size = 10 MB),
  (name = N'gronk_data2b', filename = N'/var/opt/mssql/data/gronk2b.mdf',   size = 10 MB)
LOG ON
  (name = N'gronk_log',    filename = N'/var/opt/mssql/data/gronk_log.ldf', size = 10 MB);

Em seguida, duas tabelas simples, uma com mais linhas e a outra com menos linhas, mas mais
dados:

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
USE gronk;
GO -- in the first table, we'll put a lot of rows, but small row size CREATE TABLE dbo.tblPrimary
(
  id int NOT NULL,
  CONSTRAINT pk_tblPrimary PRIMARY KEY (id),
  INDEX      ix_tblPrimary (id DESC)
) ON [PRIMARY];
GO INSERT dbo.tblPrimary(id) SELECT [object_id] FROM sys.all_objects;
  -- in the second table, we'll put fewer rows, but larger row size CREATE TABLE dbo.tblSecondary
(
  id int NOT NULL,
  filler char(4000) NOT NULL DEFAULT '',
  CONSTRAINT pk_tblSecondary PRIMARY KEY (id)
) ON [SECONDARY]; CREATE INDEX ix_tblSecondary ON dbo.tblSecondary(id DESC) INCLUDE (filler) ON [SECONDARY];
GO INSERT dbo.tblSecondary(id) SELECT TOP (1000) [object_id] FROM sys.all_objects;
GO

Com essas tabelas no lugar, voltarei ao banco de dados do Utility e criará
o seguinte procedimento de wrapper:

Agora, se eu chamá-lo com esta sintaxe, solicitando tabelas por contagem de linhas:

EXEC Utility.dbo.AssessDistribution_ForMultipleTables 
     @DatabaseName  = N'gronk',
     @PrimaryFactor = 'rows';

Eu recebo estes resultados:

Resultados da chamada de procedimento solicitando tabelas classificadas pelo número de linhas

E se eu mudar para as tabelas de classificação por tamanho:

EXEC Utility.dbo.AssessDistribution_ForMultipleTables 
     @DatabaseName  = N'gronk',
     @PrimaryFactor = 'size';

Os resultados retornam na ordem oposta (e indicam o número de páginas
em oposição ao número de linhas):

Resultados da chamada de procedimento solicitando tabelas classificadas por número de páginas

Algumas advertências:

  • Este procedimento soma a contagem ou tamanho de linhas em todos os índices. Se você apenas
    deseja contabilizar o índice em cluster (ou a tabela, se você tiver pilhas), remova o comentário
    a linha – AND index_id IN (0,1).
  • Essa solução ainda não foi testada com índices columnstore ou com memória otimizada
    tabelas.
  • Como vale a pena repetir, sys.dm_db_database_page_allocations
    tem

    alguns problemas potenciais de desempenho; se você pedir 10 mesas, estará
    chamará esta função 11 vezes. Apenas algo para ter em mente.

Próximos passos

Veja estas dicas relacionadas e outros recursos:

Última atualização: 2020-05-26

Sobre o autor

Aaron Bertrand (@AaronBertrand) é um tecnólogo apaixonado com experiência no setor que remonta ao ASP clássico e SQL Server 6.5. Ele é editor-chefe do blog relacionado ao desempenho, SQLPerformance.com, e também blogs no sqlblog.org.

Ver todas as minhas dicas

*As fotos exibidas neste post pertencem ao post sqlblog.org

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br