Uso do espaço de arquivos e grupos de arquivos do SQL Server – Parte 1

Uso do espaço de arquivos e grupos de arquivos do SQL Server – Parte 1

Uso do espaço de arquivos e grupos de arquivos do SQL Server - Parte 1
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Por: Aaron Bertrand | Atualizado: 2020-04-07 | Comentários Relacionado: Mais> Administração de banco de dados

Problema

Ao investigar um espaço em disco ou um grande problema de tabela do SQL Server, muitas vezes eu quero conhecer muitas
informações e precisa se esforçar para encontrar todos os tipos de consultas diferentes
que eu juntei ao longo do tempo. Eu quero responder algumas ou todas
das seguintes perguntas:

  • Qual é o tamanho da mesa?
  • Quantos índices e qual o tamanho deles?
  • Os dados são particionados?
  • Quantos grupos de arquivos e arquivos existem?
  • Como os dados são distribuídos pelos arquivos de dados?
  • Qual o tamanho dos arquivos de dados?
  • Quanto espaço os arquivos precisam para crescer no local atual?
  • Alguns ou todos os dados podem ser movidos para diferentes arquivos / grupos de arquivos?
  • Grupos de arquivos ou arquivos devem ser adicionados ou movidos?
  • Essas configurações são sensatas e estão na política?

Como não há um DMV do SQL Server que descreva tudo isso muito bem para você, posso
muitas vezes ouço um Dennis Hopper de 1994 gritando comigo em seu telefone flip: “Pop quiz,
hot shot! “Quando há uma questão crítica que afeta a proverbial caixa registradora,
e a pressão está alta, seria bom poder obter essas informações rapidamente
e em um só lugar.

Solução

Após alguns incidentes como esse, montei um procedimento armazenado que
reuni todas essas informações para mim. E eu realmente fiz duas variações de
o procedimento armazenado: um que detalhou as informações para mim com base em uma tabela
Eu especifiquei e uma versão mais elaborada que eu poderia usar quando ainda não
saber quais tabelas podem estar (em risco de) causar o problema.

Mas vamos começar do começo. Para uma tabela que eu já identifiquei
como “grande” ou um problema em termos de latência de replicação ou outros indicadores,
Eu quero saber, como ponto de partida:

  • O nome de cada grupo de arquivos para o banco de dados
  • Para cada arquivo de dados:
    • O ID, nome lógico e caminho
    • Tamanho atual e espaço livre (absoluto e em%)
    • Configurações de crescimento e tamanho máximo
  • Para cada índice (incluindo o índice de heap / cluster):
    • Quanto desses dados viveu em cada arquivo
    • Distribuição de partição entre esses arquivos
  • Se a tabela foi particionada (não seria óbvio de cima
    se, digamos, uma partição por grupo de arquivos)
  • Tamanho da unidade em que cada arquivo está ativado e quanto espaço livre nessa unidade
    (absoluto e em%)

Portanto, como um formato de saída, seria mais ou menos assim:

Você pode obter todos esses dados de diferentes DMVs e funções do sistema, mas, novamente,
não em um local ou em um conjunto de resultados organizado. Você precisará ir separadamente para
os seguintes lugares para juntar isso:

  • sys.filegroups
  • sys.database_files
  • FILEPROPERTY
  • sys.indexes
  • sys.partitions
  • sys.dm_db_database_page_allocations
  • sys.dm_os_volume_stats

Obter as informações individuais de cada um desses lugares não é
excessivamente complicado em isolamento. Para demonstrar com alguns exemplos, vamos
primeiro crie um banco de dados:

CREATE DATABASE FGExample;
GO

Exemplo 1 – Dados do SQL Server distribuídos de maneira desigual entre arquivos

Digamos que eu tenha um cenário em que os dados de uma tabela não sejam iguais
distribuído pelos arquivos (talvez um arquivo tenha sido adicionado posteriormente e o índice não tenha sido
reconstruído ainda, e o preenchimento proporcional ainda favorece os arquivos mais antigos). Manufaturar
Nesse cenário, podemos adicionar um novo grupo de arquivos desigualmente distribuído e adicionar dois arquivos:

Leia Também  Webcast gratuito hoje: como medir seu SQL Server

ALTER DATABASE FGExample ADD FILEGROUP UnevenDist;
GO
  ALTER DATABASE FGExample ADD FILE
(
  name=N'Uneven1', size=64,  filegrowth=20, maxsize=1024,
  filename=N'/var/opt/mssql/data/Uneven1.mdf'
)TO FILEGROUP UnevenDist;
  ALTER DATABASE FGExample ADD FILE
(
  name = N'Uneven2', size = 128, filegrowth = 10, maxsize = 1024,
  filename = N'/var/opt/mssql/data/Uneven2.mdf'
) TO FILEGROUP UnevenDist;
GO

Agora, podemos criar uma tabela nesse grupo de arquivos e adicionar alguns dados:

CREATE TABLE dbo.tblUnevenDist
(
    id int NOT NULL, filler char(2000) NOT NULL DEFAULT '',
    CONSTRAINT PK_tblUnevenDist PRIMARY KEY(id) ON UnevenDist
) ON UnevenDist;
GO
  CREATE INDEX IX_tblUnevenDist ON dbo.tblUnevenDist(id DESC) WHERE id >
  ON [PRIMARY];
GO

INSERT dbo.tblUnevenDist(id) SELECT [object_id] FROM sys.all_objects;

Em seguida, adicione um terceiro arquivo, que ficará vazio até que algo faça com que os dados sejam movidos
lá:

ALTER DATABASE FGExample ADD FILE 
(
  name = N'Uneven3', size = 96, filegrowth = 10,
  filename = N'/var/opt/mssql/data/Uneven3.mdf'
) TO FILEGROUP UnevenDist;
GO

Posso obter as informações sobre o grupo de arquivos e arquivos desta maneira:

-- get filegroup files
DECLARE @FileGroupName sysname = N'UnevenDist';
  ;WITH src AS
(
  SELECT FG          = fg.name,
         FileID      = f.file_id,
         LogicalName = f.name,
         [Path]      = f.physical_name,
         FileSizeMB  = f.size/128.,
         UsedSpaceMB = CONVERT(bigint, FILEPROPERTY(f.[name], 'SpaceUsed'))/128.,
         GrowthMB    = CASE f.is_percent_growth WHEN 1 THEN NULL ELSE f.growth/128. END,
         MaxSizeMB   = NULLIF(f.max_size, -1)/128.,
         DriveSizeMB = vs.total_bytes/1048576.,
         DriveFreeMB = vs.available_bytes/1048576.
  FROM sys.database_files AS f
  INNER JOIN sys.filegroups AS fg
        ON f.data_space_id = fg.data_space_id
  CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS vs
  WHERE fg.name = COALESCE(@FileGroupName, fg.name)
)
SELECT [Filegroup] = FG, FileID, LogicalName, [Path],
  FileSizeMB  = CONVERT(decimal(18,2), FileSizeMB),
  FreeSpaceMB = CONVERT(decimal(18,2), FileSizeMB-UsedSpaceMB),
  [%]         = CONVERT(decimal(5,2), 100.*(FileSizeMB-UsedSpaceMB)/FileSizeMB),
  GrowthMB    = COALESCE(RTRIM(CONVERT(decimal(18,2), GrowthMB)), '% warning!'),
  MaxSizeMB   = CONVERT(decimal(18,2), MaxSizeMB),
  DriveSizeMB = CONVERT(bigint, DriveSizeMB),
  DriveFreeMB = CONVERT(bigint, DriveFreeMB),
  [%]         = CONVERT(decimal(5,2), 100.*(DriveFreeMB)/DriveSizeMB)
FROM src
ORDER BY FG, LogicalName;

Resultados (prettified em uma planilha):

Observar visualmente essa saída imediatamente aponta cinco problemas. Quatro de
eles são destacados acima; da esquerda para a direita:

  • Os arquivos são de tamanhos diferentes – Para distribuição uniforme, você deseja
    estes são os mesmos; caso contrário, os arquivos pequenos nunca são tocados e se tornam inúteis.
  • Um arquivo está quase completamente vazio – Isso indica o arquivo
    foi adicionado recentemente ou não está sendo selecionado por preenchimento proporcional.
  • Um arquivo possui uma configuração de crescimento automático maior – Se este arquivo crescer em seguida, poderá
    se tornar um ponto de acesso, pois terá mais espaço livre do que qualquer outro arquivo que
    crescer apenas pela metade dessa quantidade.
  • Um arquivo não tem um tamanho máximo – Pode haver razões para isso,
    mas se apenas um arquivo estiver sem limite, ele poderá se tornar o único gargalo
    quando os outros arquivos se aproximarem de sua capacidade.
  • Existem três arquivos neste grupo de arquivos – Normalmente, você quer ter um número
    de arquivos que se correlacionam de alguma forma com o número de núcleos – não necessariamente
    1: 1, talvez 1: 2 ou 1: 8, mas alguns números pares. Nesse caso, talvez a máquina tenha
    um soquete único com 6 núcleos, mas ainda vale a pena investigar para garantir que essa configuração
    é ótimo.

Exemplo 2 – Crescimento automático da porcentagem de arquivo do SQL Server

Vamos adicionar outro grupo de arquivos e dois arquivos; desta vez, vamos dar
distribuição igual, mas “acidentalmente” vamos configurar um
dos arquivos para usar o crescimento automático percentual (não
exatamente uma melhor prática
)

ALTER DATABASE FGExample ADD FILEGROUP EvenDist;
GO
  ALTER DATABASE FGExample ADD FILE
(
    name = N'Even1', size = 32, filegrowth = 10%,
    filename = N'/var/opt/mssql/data/Even1.mdf'
)
TO FILEGROUP EvenDist;
ALTER DATABASE FGExample ADD FILE
(
    name = N'Even2', size = 32, filegrowth = 10,
    filename = N'/var/opt/mssql/data/Even2.mdf'
)
TO FILEGROUP EvenDist;

E inseriremos os mesmos dados de antes em uma nova tabela neste grupo de arquivos:

CREATE TABLE dbo.tblEvenDist
(
    id int NOT NULL, filler char(2000) NOT NULL DEFAULT'',
    CONSTRAINT PK_tblEvenDist PRIMARY KEY(id) ON UnevenDist
) ON UnevenDist;
GO
  CREATE INDEX IX_tblEvenDist ON dbo.tblUnevenDist(id DESC) WHERE id >
  ON [PRIMARY];
GO
  INSERT dbo.tblEvenDist(id) SELECT [object_id] FROM sys.all_objects;

Alterando a consulta “obter arquivos do grupo de arquivos” acima para especificar um nome de grupo de arquivos diferente
(EvenDist neste caso) como o parâmetro
(ou definindo-o como NULL para que todos os grupos de arquivos sejam retornados), vemos esses (adicionais)
linhas:

Desta vez, podemos ver facilmente que os dados estão distribuídos de maneira mais uniforme, mas nós
recebo um pequeno aviso de que um dos arquivos tem crescimento configurado para porcentagem.
Em nosso cenário, a porcentagem real é menos importante do que criar uma ação
item para corrigir essa configuração para um tamanho fixo.

Exemplo 3 – O grupo de arquivos do SQL Server está inclinado

Às vezes, a inclinação da distribuição entre arquivos pode ser normal e esperada. Levar a
exemplo em que você possui uma tabela particionada e uma partição em seu próprio grupo de arquivos
simplesmente contém mais dados que outro. Isso pode acontecer por várias razões, principalmente
desvio de dados, incluindo:

  • As vendas dobraram ano após ano, mas as partições ainda são definidas como
    ano.
  • Os intervalos de partição são diferentes (talvez tenhamos mudado de anual para mensal).
  • Algumas partições ainda não foram preenchidas (por exemplo, os dados ainda estão sendo
    já preenchidas ou partições já criadas para cobrir vários anos no
    futuro).
  • Algumas partições são compactadas de maneira diferente (talvez apenas compactação de dados
    em algumas partições ou índices ou em diferentes configurações de compactação – como

    COLUMNSTORE_ARCHIVE
    – nos mais velhos).

Além disso, qualquer partição
poderia ter arquivos individuais
tamanhos diferentes (ou com uma porcentagem diferente), devido a problemas destacados
nos dois primeiros exemplos. Você nem sempre precisa executar nenhuma ação, mas precisa
certamente não pode realizar nenhuma análise – não importa tomar decisões –
sem visibilidade.

Vamos criar uma tabela particionada simples com distribuição desigual entre
arquivos e entre partições. Podemos ver onde a consulta existente pode ajudar e onde
pode ficar aquém. Primeiro, podemos criar os grupos de arquivos onde nossas partições serão
ser distribuído:

ALTER DATABASE FGExample ADD FILEGROUP Part1;
ALTER DATABASE FGExample ADD FILEGROUP Part2;
ALTER DATABASE FGExample ADD FILEGROUP Part3;
ALTER DATABASE FGExample ADD FILEGROUP Part4;
GO   ALTER DATABASE FGExample ADD FILE
(
 name = N'P1',   size = 16, filegrowth = 5,
  filename = N'/var/opt/mssql/data/P1.mdf'
) TO FILEGROUP Part1;
ALTER DATABASE FGExample ADD FILE
(
 name = N'P2',   size = 16, filegrowth = 5,
  filename = N'/var/opt/mssql/data/P2.mdf'
) TO FILEGROUP Part2;

ALTER DATABASE FGExample ADD FILE
(
 name = N'P3',   size = 16, filegrowth = 5,
  filename = N'/var/opt/mssql/data/P3.mdf'
) TO FILEGROUP Part3;
  ALTER DATABASE FGExample ADD FILE
(
 name = N'P4a',  size = 16, filegrowth = 5,
  filename = N'/var/opt/mssql/data/P4a.mdf'
) TO FILEGROUP Part4;

Agora precisamos de um esquema e função de partição:

CREATE PARTITION FUNCTION PFInt(int)
AS RANGE RIGHT FOR VALUES(10,20,30);
 
CREATE PARTITION SCHEME PSInt
AS PARTITION PFInt TO (Part1, Part2, Part3, Part4);

Em seguida, uma tabela com o índice em cluster criado nesse esquema (e não em cluster
índices em PRIMARY):

CREATE TABLE dbo.PartExample
(
  id     int        NOT NULL, 
  dt     datetime   NOT NULL DEFAULT GETDATE(),
  filler char(4000) NOT NULL DEFAULT'',   INDEX cix_pe CLUSTERED(id) ) ON PSInt(id); GO   CREATE INDEX ix_pe ON dbo.PartExample(id DESC)
  ON[PRIMARY]; GO   CREATE INDEX ix_dt ON dbo.PartExample(dt)
  ON[PRIMARY]; GO

Alguns dados:

INSERT dbo.PartExample(id) SELECT CASE 
  WHEN o >  1000000 THEN 5  --   331 rows
  WHEN o >         THEN 15 --   692 rows
  WHEN o > -10000   THEN 25 -- 3,106 rows
  ELSE 35 END               -- 6,779 rows
FROM (SELECT [object_id] FROM sys.all_columns) AS t(o);

E, em seguida, um arquivo adicional para criar alguma inclinação artificial no
Part4 grupo de arquivos:

ALTER DATABASE FGExample ADD FILE 
(
  name = N'P4b',  size = 16, filegrowth = 5, 
  filename = N'/var/opt/mssql/data/P4b.mdf'
) TO FILEGROUP Part4;

Agora, se executarmos a consulta “obter arquivos do grupo de arquivos” novamente com os grupos de arquivos relevantes
mostramos estes resultados:

Eu destaquei as duas coisas que se destacam: que um dos arquivos em
o grupo de arquivos Part4 cresceu de sua configuração original de 16 MB e
o outro arquivo – que não cresceu – está vazio. Uma coisa que é
O que não é óbvio neste conjunto de resultados é se quaisquer índices são

não alinhado por partição
, e pode não ser algo que eu saiba que deveria estar procurando
para.

Próximos passos

Fique atento à parte 2, onde mostrarei como introduzir informações do índice
para esse resultado sem sobrecarregar o consumidor – facilitando a identificação
todos esses problemas e muito mais, sem saber quais consultas você deve executar
para fazer isso. Enquanto isso, continue lendo para obter dicas relacionadas e outros recursos:

Última atualização: 2020-04-07

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 do sqlblog.org.

Ver todas as minhas dicas



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