SOMA de DATALENGTHs que não correspondem ao tamanho da tabela de sys.allocation_units

                          Please note that the following info is not intended to be a comprehensive
description of how data pages are laid out, such that one can calculate
the number of bytes used per any set of rows, as that is very complicated.

Os dados não são a única coisa que ocupa espaço em uma página de dados de 8k:

• Há espaço reservado. Você só tem permissão para usar 8060 dos 8192 bytes (são 132 bytes que nunca foram seus):

  • Cabeçalho da página: tem exatamente 96 bytes.
  • Slot array: é de 2 bytes por linha e indica o deslocamento de onde cada linha começa na página. O tamanho dessa matriz não é limitado aos 36 bytes restantes (132 - 96 = 36), caso contrário, você estaria efetivamente limitado a colocar apenas 18 linhas no máximo em uma página de dados. Isso significa que cada linha é 2 bytes maior do que você pensa. Esse valor não está incluído no "tamanho do registro", conforme relatado por DBCC PAGE, e é por isso que ele é mantido separado aqui, em vez de ser incluído nas informações por linha abaixo.
  • Metadados por linha (incluindo, mas não limitado a):
    • O tamanho varia dependendo da definição da tabela (ou seja, número de colunas, comprimento variável ou comprimento fixo, etc). Informações retiradas dos comentários de @PaulWhite e @Aaron que podem ser encontradas na discussão relacionada a esta resposta e teste.
    • Cabeçalho de linha: 4 bytes, 2 deles denotando o tipo de registro e os outros dois sendo um deslocamento para o bitmap NULL
    • Número de colunas: 2 bytes
    • NULL Bitmap: quais colunas são atualmente NULL. 1 byte por cada conjunto de 8 colunas. E para todas as colunas, mesmo NOT NULLaquelas. Portanto, mínimo 1 byte.
    • Matriz de deslocamento de coluna de comprimento variável: mínimo de 4 bytes. 2 bytes para manter o número de colunas de comprimento variável e, em seguida, 2 bytes por cada coluna de comprimento variável para manter o deslocamento para onde ele começa.
    • Informações de controle de versão: 14 bytes (estará presente se seu banco de dados estiver definido como ALLOW_SNAPSHOT_ISOLATION ONou READ_COMMITTED_SNAPSHOT ON).
  • Consulte a seguinte pergunta e resposta para obter mais detalhes sobre isso: Matriz de slots e tamanho total da página
  • Por favor, veja a seguinte postagem no blog de Paul Randall que tem vários detalhes interessantes sobre como as páginas de dados são dispostas: Bisbilhotando com DBCC PAGE (Parte 1 de?)

• Ponteiros LOB para dados que não são armazenados em linha. Portanto, isso representaria DATALENGTH+ pointer_size. Mas estes não são de tamanho padrão. Consulte a postagem de blog a seguir para obter detalhes sobre esse tópico complexo: Qual é o tamanho do ponteiro LOB para tipos (MAX) como Varchar, Varbinary etc.? . Entre essa postagem vinculada e alguns testes adicionais que fiz , as regras (padrão) devem ser as seguintes:

  • Tipos de LOB legados/obsoletos que ninguém deveria mais usar a partir do SQL Server 2005 ( TEXT, NTEXTe IMAGE):
    • Por padrão, sempre armazene seus dados em páginas LOB e sempre use um ponteiro de 16 bytes para o armazenamento LOB.
    • SE sp_tableoption foi usado para definir a text in rowopção, então:
      • se houver espaço na página para armazenar o valor e o valor não for maior que o tamanho máximo da linha (intervalo configurável de 24 a 7000 bytes com padrão de 256), ele será armazenado na linha,
      • caso contrário, será um ponteiro de 16 bytes.
  • Para os tipos de LOB mais recentes introduzidos no SQL Server 2005 ( VARCHAR(MAX), NVARCHAR(MAX)e VARBINARY(MAX)):
    • Por padrão:
      • Se o valor não for maior que 8.000 bytes e houver espaço na página, ele será armazenado em linha.
      • Inline Root — para dados entre 8.001 e 40.000 (na verdade, 42.000) bytes, se o espaço permitir, haverá de 1 a 5 ponteiros (24 a 72 bytes) IN ROW que apontam diretamente para a(s) página(s) LOB. 24 bytes para a página LOB inicial de 8k e 12 bytes por cada página adicional de 8k para até mais quatro páginas de 8k.
      • TEXT_TREE — para dados acima de 42.000 bytes, ou se os ponteiros de 1 a 5 não couberem na linha, haverá apenas um ponteiro de 24 bytes para a página inicial de uma lista de ponteiros para as páginas LOB (ou seja, o "text_tree " página).
    • SE sp_tableoption foi usado para definir a large value types out of rowopção, use sempre um ponteiro de 16 bytes para o armazenamento LOB.
  • Eu disse regras "padrão" porque não testei valores em linha em relação ao impacto de certos recursos, como compactação de dados, criptografia em nível de coluna, criptografia transparente de dados, sempre criptografado etc.

• Páginas de excesso de LOB: Se um valor for 10k, isso exigirá 1 página completa de 8k de estouro e, em seguida, parte de uma segunda página. Se nenhum outro dado puder ocupar o espaço restante (ou mesmo for permitido, não tenho certeza dessa regra), você terá aproximadamente 6 kb de espaço "desperdiçado" na página de dados de estouro do 2º LOB.

• Espaço não utilizado: Uma página de dados de 8k é exatamente isso: 8192 bytes. Não varia de tamanho. Os dados e metadados colocados nele, no entanto, nem sempre se encaixam bem em todos os 8192 bytes. E as linhas não podem ser divididas em várias páginas de dados. Portanto, se você tiver 100 bytes restantes, mas nenhuma linha (ou nenhuma linha que caberia naquele local, dependendo de vários fatores) puder caber lá, a página de dados ainda ocupará 8192 bytes e sua segunda consulta estará contando apenas o número de páginas de dados. Você pode encontrar esse valor em dois lugares (lembre-se de que parte desse valor é parte desse espaço reservado):

  • DBCC PAGE( db_name, file_id, page_id ) WITH TABLERESULTS;Procure ParentObject= "PAGE HEADER:" e Field= "m_freeCnt". O Valuecampo é o número de bytes não utilizados.
  • SELECT buff.free_space_in_bytes FROM sys.dm_os_buffer_descriptors buff WHERE buff.[database_id] = DB_ID(N'db_name') AND buff.[page_id] = page_id;Este é o mesmo valor informado por "m_freeCnt". Isso é mais fácil do que o DBCC, pois pode obter muitas páginas, mas também requer que as páginas tenham sido lidas no buffer pool em primeiro lugar.

• Espaço reservado por FILLFACTOR< 100. As páginas recém-criadas não respeitam a FILLFACTORconfiguração, mas fazer uma RECONSTRUÇÃO reservará esse espaço em cada página de dados. A ideia por trás do espaço reservado é que ele será usado por inserções não sequenciais e/ou atualizações que expandem o tamanho das linhas já existentes na página, devido às colunas de comprimento variável serem atualizadas com um pouco mais de dados (mas não o suficiente para causar um divisão de página). Mas você poderia facilmente reservar espaço em páginas de dados que naturalmente nunca obteriam novas linhas e nunca teriam as linhas existentes atualizadas, ou pelo menos não atualizadas de forma a aumentar o tamanho da linha.

• Divisões de página (fragmentação): a necessidade de adicionar uma linha a um local que não tem espaço para a linha causará uma divisão de página. Nesse caso, aproximadamente 50% dos dados existentes são movidos para uma nova página e a nova linha é adicionada a uma das 2 páginas. Mas agora você tem um pouco mais de espaço livre que não é contabilizado pelos DATALENGTHcálculos.

• Linhas marcadas para exclusão. Quando você exclui linhas, elas nem sempre são removidas imediatamente da página de dados. Se eles não puderem ser removidos imediatamente, eles são "marcados para morrer" (referência a Steven Segal) e serão removidos fisicamente mais tarde pelo processo de limpeza fantasma (acredito que seja esse o nome). No entanto, estes podem não ser relevantes para esta questão em particular.

• Páginas fantasmas? Não tenho certeza se esse é o termo apropriado, mas às vezes as páginas de dados não são removidas até que uma RECONSTRUÇÃO do Índice Clusterizado seja feita. Isso também representaria mais páginas do DATALENGTHque o total. Isso geralmente não deveria acontecer, mas já me deparei com isso uma vez, há vários anos.

• Colunas SPARSE: Colunas esparsas economizam espaço (principalmente para tipos de dados de comprimento fixo) em tabelas onde uma grande % das linhas são NULLpara uma ou mais colunas. A SPARSEopção torna o NULLtipo de valor até 0 bytes (em vez da quantidade normal de comprimento fixo, como 4 bytes para um INT), mas valores não NULL ocupam 4 bytes adicionais para tipos de comprimento fixo e uma quantidade variável para tipos de comprimento variável. O problema aqui é que DATALENGTHnão inclui os 4 bytes extras para valores não NULL em uma coluna SPARSE, então esses 4 bytes precisam ser adicionados novamente. Você pode verificar se há alguma SPARSEcoluna via:

  SELECT OBJECT_SCHEMA_NAME(sc.[object_id]) AS [SchemaName],
         OBJECT_NAME(sc.[object_id]) AS [TableName],
         sc.name AS [ColumnName]
  FROM   sys.columns sc
  WHERE  sc.is_sparse = 1;
  

  E então, para cada SPARSEcoluna, atualize a consulta original a ser usada:

  SUM(DATALENGTH(FieldN) + 4)
  

  Observe que o cálculo acima para adicionar 4 bytes padrão é um pouco simplista, pois funciona apenas para tipos de comprimento fixo. E, há metadados adicionais por linha (pelo que posso dizer até agora) que reduz o espaço disponível para dados, simplesmente por ter pelo menos uma coluna SPARSE. Para obter mais detalhes, consulte a página do MSDN para usar colunas esparsas .

• Índice e outras páginas (por exemplo, IAM, PFS, GAM, SGAM, etc): não são páginas de "dados" em termos de dados do usuário. Isso aumentará o tamanho total da tabela. Se estiver usando o SQL Server 2012 ou mais recente, você pode usar a sys.dm_db_database_page_allocationsfunção de gerenciamento dinâmico (DMF) para ver os tipos de página (as versões anteriores do SQL Server podem usar DBCC IND(0, N'dbo.table_name', 0);):

  SELECT *
  FROM   sys.dm_db_database_page_allocations(
                 DB_ID(),
                 OBJECT_ID(N'dbo.table_name'),
                 1,
                 NULL,
                 N'DETAILED'
                )
  WHERE  page_type = 1; -- DATA_PAGE
  

  Nem o DBCC INDnem sys.dm_db_database_page_allocations(com essa cláusula WHERE) relatará nenhuma página de índice e somente o DBCC INDrelatará pelo menos uma página IAM.

• DATA_COMPRESSION: Se você tiver ROWou PAGEa compactação habilitada no índice clusterizado ou no heap, poderá esquecer a maior parte do que foi mencionado até agora. O cabeçalho da página de 96 bytes, a matriz de slots de 2 bytes por linha e as informações de versão de 14 bytes por linha ainda estão lá, mas a representação física dos dados torna-se altamente complexa (muito mais do que já foi mencionado quando a compactação não está sendo usado). Por exemplo, com a compactação de linha, o SQL Server tenta usar o menor contêiner possível para caber em cada coluna, em cada linha. Portanto, se você tiver uma BIGINTcoluna que, de outra forma (supondo que SPARSEtambém não esteja habilitada), sempre ocuparia 8 bytes, se o valor estiver entre -128 e 127 (ou seja, inteiro de 8 bits com sinal), usará apenas 1 byte e, se o valor pode caber em umSMALLINT, ele ocupará apenas 2 bytes. Tipos inteiros que são NULLou 0não ocupam espaço e são simplesmente indicados como sendo NULLou "vazios" (isto é 0, ) em um array mapeando as colunas. E há muitas, muitas outras regras. Possui dados Unicode ( NCHAR, NVARCHAR(1 - 4000), mas não NVARCHAR(MAX) , mesmo se armazenados em linha)? A compactação Unicode foi adicionada no SQL Server 2008 R2, mas não há como prever o resultado do valor "compactado" em todas as situações sem fazer a compactação real devido à complexidade das regras .

Então, realmente, sua segunda consulta, embora mais precisa em termos de espaço físico total ocupado no disco, só é realmente precisa ao fazer um REBUILDíndice clusterizado. E depois disso, você ainda precisa levar em conta qualquer FILLFACTORconfiguração abaixo de 100. E mesmo assim sempre há cabeçalhos de página e, muitas vezes, uma certa quantidade de espaço "desperdiçado" que simplesmente não pode ser preenchido devido a ser muito pequeno para caber em qualquer linha neste tabela, ou pelo menos a linha que logicamente deveria ir nesse slot.

Com relação à precisão da 2ª consulta para determinar o "uso de dados", parece mais justo retroceder os bytes do cabeçalho da página, pois eles não são uso de dados: são custos indiretos de negócios. Se houver 1 linha em uma página de dados e essa linha for apenas um TINYINT, esse 1 byte ainda exigirá que a página de dados exista e, portanto, os 96 bytes do cabeçalho. Esse 1 departamento deve ser cobrado por toda a página de dados? Se essa página de dados for preenchida pelo Departamento nº 2, eles dividiriam igualmente esse custo "overhead" ou pagariam proporcionalmente? Parece mais fácil simplesmente recuar. Nesse caso, usar um valor de 8para multiplicar number of pagesé muito alto. Que tal:

-- 8192 byte data page - 96 byte header = 8096 (approx) usable bytes.
SELECT 8060.0 / 1024 -- 7.906250

Portanto, use algo como:

(SUM(a.total_pages) * 7.91) / 1024 AS [TotalSpaceMB]

para todos os cálculos nas colunas "number_of_pages".

AND , considerando que o uso de DATALENGTHper each field não pode retornar os metadados por linha, que devem ser adicionados à sua consulta por tabela, onde você obtém o DATALENGTHper each field, filtrando cada "departamento":

• Tipo de registro e deslocamento para NULL Bitmap: 4 bytes
• Contagem de colunas: 2 bytes
• Slot Array: 2 bytes (não incluído no "tamanho do registro", mas ainda precisa ser contabilizado)
• Bitmap NULL: 1 byte a cada 8 colunas (para todas as colunas)
• Controle de versão de linha: 14 bytes (se o banco de dados tiver ou estiver ALLOW_SNAPSHOT_ISOLATIONdefinido READ_COMMITTED_SNAPSHOTcomo ON)
• Matriz de deslocamento de coluna de comprimento variável: 0 bytes se todas as colunas forem de comprimento fixo. Se alguma coluna for de comprimento variável, então 2 bytes, mais 2 bytes por cada uma das colunas de comprimento variável.
• Ponteiros LOB: esta parte é muito imprecisa, pois não haverá ponteiro se o valor for NULL, e se o valor couber na linha, ele pode ser muito menor ou muito maior que o ponteiro, e se o valor for armazenado fora linha, o tamanho do ponteiro pode depender da quantidade de dados existentes. No entanto, como queremos apenas uma estimativa (ou seja, "swag"), parece que 24 bytes é um bom valor para usar (bem, tão bom quanto qualquer outro ;-). Isso é por cada MAXcampo.

Portanto, use algo como:

• Em geral (cabeçalho de linha + número de colunas + array de slots + bitmap NULL):

  ([RowCount] * (( 4 + 2 + 2 + (1 + (({NumColumns} - 1) / 8) ))
  

• Em geral (detecção automática se "informações da versão" estiver presente):

  + (SELECT CASE WHEN snapshot_isolation_state = 1 OR is_read_committed_snapshot_on = 1
                   THEN 14 ELSE 0 END FROM sys.databases WHERE [database_id] = DB_ID())
  

• SE houver colunas de comprimento variável, adicione:

  + 2 + (2 * {NumVariableLengthColumns})
  

• SE houver alguma MAXcoluna /LOB, adicione:

  + (24 * {NumLobColumns})
  

• No geral:

  )) AS [MetaDataBytes]
  

Isso não é exato e, novamente, não funcionará se você tiver Row ou Page Compression ativado no Heap ou no Clustered Index, mas definitivamente deve aproximá-lo.

ATUALIZAÇÃO sobre o mistério da diferença de 15%

Nós (inclusive eu) estávamos tão focados em pensar sobre como as páginas de dados são dispostas e como DATALENGTHpodem explicar as coisas que não gastamos muito tempo revisando a segunda consulta. Executei essa consulta em uma única tabela e, em seguida, comparei esses valores com o que estava sendo relatado sys.dm_db_database_page_allocationse não eram os mesmos valores para o número de páginas. Em um palpite, removi as funções agregadas e GROUP BY, e substituí a SELECTlista por a.*, '---' AS [---], p.*. E então ficou claro: as pessoas devem ter cuidado de onde, nessas interwebs obscuras, obtêm suas informações e scripts ;-). A segunda consulta postada na pergunta não está exatamente correta, especialmente para esta pergunta em particular.

• Problema menor: fora disso não faz muito sentido GROUP BY rows(e não ter essa coluna em uma função agregada), o JOIN entre sys.allocation_unitse sys.partitionsnão é tecnicamente correto. Existem 3 tipos de Unidades de Alocação, e uma delas deve se UNIR a um campo diferente. Muitas vezes , partition_ide hobt_idsão os mesmos, portanto, pode nunca haver um problema, mas às vezes esses dois campos têm valores diferentes.

• Problema principal: a consulta usa o used_pagescampo. Esse campo cobre todos os tipos de páginas: Dados, Índice, IAM, etc, tc. Existe outro campo mais apropriado para usar quando se trata apenas dos dados reais: data_pages.

Adaptei a 2ª consulta na Questão com os itens acima em mente e usando o tamanho da página de dados que confirma o cabeçalho da página. Também removi dois JOINs que eram desnecessários: sys.schemas(substituído por chamada para SCHEMA_NAME()) e sys.indexes(o índice clusterizado é sempre index_id = 1e temos index_idem sys.partitions).

SELECT  SCHEMA_NAME(st.[schema_id]) AS [SchemaName],
        st.[name] AS [TableName],
        SUM(sp.[rows]) AS [RowCount],
        (SUM(sau.[total_pages]) * 8.0) / 1024 AS [TotalSpaceMB],
        (SUM(CASE sau.[type]
           WHEN 1 THEN sau.[data_pages]
           ELSE (sau.[used_pages] - 1) -- back out the IAM page
         END) * 7.91) / 1024 AS [TotalActualDataMB]
FROM        sys.tables st
INNER JOIN  sys.partitions sp
        ON  sp.[object_id] = st.[object_id]
INNER JOIN  sys.allocation_units sau
        ON  (   sau.[type] = 1
            AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
        OR  (   sau.[type] = 2
            AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
        OR  (   sau.[type] = 3
            AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE       st.is_ms_shipped = 0
--AND         sp.[object_id] = OBJECT_ID(N'dbo.table_name')
AND         sp.[index_id] < 2 -- 1 = Clustered Index; 0 = Heap
GROUP BY    SCHEMA_NAME(st.[schema_id]), st.[name]
ORDER BY    [TotalSpaceMB] DESC;

Talvez esta seja uma resposta grunge, mas é isso que eu faria.

Portanto, DATALENGTH representa apenas 86% do total. Ainda é uma divisão muito representativa. A sobrecarga na excelente resposta de srutzky deve ter uma divisão bastante uniforme.

Eu usaria sua segunda consulta (páginas) para o total. E use o primeiro (datalength) para alocar a divisão. Muitos custos são alocados usando uma normalização.

E você deve considerar que uma resposta mais próxima aumentará o custo, portanto, mesmo o departamento que perdeu em uma divisão ainda pode pagar mais.