Encontre o tamanho descompactado de todas as tabelas em um banco de dados

Eu preciso do tamanho real que os dados descompactados terão.
...
Eu preferiria ter o tamanho o mais correto possível.

Embora o desejo por essas informações seja certamente compreensível, obter essas informações, especialmente no contexto do "correto quanto possível", é mais complicado do que todos esperam devido a suposições incorretas. Seja fazendo a ideia da tabela de sombra descompactada mencionada na pergunta ou a sugestão de @sp_BlitzErik em um comentário sobre como restaurar o banco de dados e descompactar lá para verificar, não deve ser assumido que o tamanho da tabela descompactada == o tamanho dos referidos dados na memória no servidor de aplicativos:

1. Todas as linhas da tabela estão sendo armazenadas em cache? Ou apenas dentro de um intervalo? A suposição aqui é que é tudo, e isso pode estar correto, mas achei que deveria pelo menos ser mencionado que esse pode não ser o caso (a menos que a documentação indique o contrário, mas esse é um ponto menor de qualquer maneira, só não queria que não seja mencionado).

   A pergunta foi atualizada para informar: sim, todas as linhas estão sendo armazenadas em cache.

2. Sobrecarga da estrutura

   1. No lado do banco de dados:
      Sobrecarga de página e linha no lado do banco de dados: quantas linhas cabem em uma página é determinada por muitos fatores que podem prejudicar as estimativas. Mesmo com um FILLFACTORde 100 (ou 0), ainda é provável que haja algum espaço não utilizado na página devido ao fato de não ser suficiente para uma linha inteira. E isso além do cabeçalho da página. Além disso, se qualquer funcionalidade de isolamento de instantâneos estiver habilitada, haverá, acredito, 13 bytes extras por linha ocupados pelo número da versão, e isso prejudicará as estimativas. Há outras minúcias relacionadas ao tamanho real da linha (bitmap NULL, colunas de comprimento variável, etc), mas os itens mencionados até agora devem fazer o ponto.
   2. No lado do servidor de aplicativos:
      que tipo de coleção está sendo usada para armazenar os resultados em cache? Eu suponho que este é um aplicativo .NET, então é um DataTable? Uma lista genérica? Um dicionário classificado? Cada tipo de coleção tem uma quantidade diferente de overheard. Eu não esperaria que nenhuma das opções necessariamente espelhasse as sobrecargas de página e linha no lado do banco de dados, especialmente em escala (tenho certeza de que uma pequena quantidade de linha pode não ter vários o suficiente para importar, mas você não está procurando diferenças em centenas de bytes ou apenas alguns kB).
3. Tipos de dados
   1. No lado do banco de dados:
      CHAR/ VARCHARos dados são armazenados em 1 byte por caractere (ignorando os caracteres de byte duplo no momento). XMLé otimizado para não ocupar tanto espaço quanto a representação de texto implicaria. Esse tipo de dados cria um dicionário de nomes de elementos e atributos e substitui as referências reais a eles no documento por seus respectivos IDs (na verdade, meio legal). Caso contrário, os valores de string são todos UTF-16 (2 ou 4 bytes por "caractere"), assim como NCHAR/ NVARCHAR. DATETIME2é entre 6 e 8 bytes. DECIMALestá entre 5 e 17 bytes (dependendo da precisão).
   2. No lado do servidor de aplicativos:
      Strings (novamente, assumindo .NET) são sempre UTF-16. Não há otimização para strings de 8 bits, como o que VARCHARé válido. MAS, strings também podem ser "internadas", que é uma cópia compartilhada que pode ser referenciada muitas vezes (mas não sei se isso funciona para strings em coleções ou, se sim, se funciona para todos os tipos de coleções). XMLpode ou não ser armazenado da mesma maneira na memória (terei que procurar isso). DateTimeé sempre 8 bytes (como T-SQL DATETIME, mas não como DATE, TIME, ou DATETIME2). Decimalé sempre 16 bytes .

Tudo isso para dizer: não há praticamente nada que você possa fazer no lado do banco de dados para obter um tamanho de espaço de memória bastante preciso no lado do servidor de aplicativos. Você precisa encontrar uma maneira de interrogar o próprio servidor de aplicativos, depois de ser carregado com uma tabela específica, para saber o tamanho dela. E não tenho certeza se um depurador permitiria ver o tamanho do tempo de execução de uma coleção preenchida. Caso contrário, a única maneira de chegar perto seria percorrer todas as linhas de uma tabela, multiplicando cada coluna pelo tamanho .NETINT apropriado (por exemplo, = * 4, VARCHAR= DATALENGTH() * 2, NVARCHAR= DATALENGTH(), XML= ?, etc), mas isso ainda deixa a questão da sobrecarga da coleção mais cada elemento da coleção.

Dada alguma nova definição na pergunta, provavelmente poderíamos fazer a seguinte consulta para chegar bem perto. E não importa se a tabela está compactada ou não, mas cabe a cada pessoa determinar se a verificação de todas as linhas é apropriada em Produção (talvez a partir de uma restauração ou fora do horário de pico):

SELECT
   SUM( DATALENGTH([NVarcharColumn_1]) + DATALENGTH([NVarcharColumn_N]) ) + 
   SUM( (DATALENGTH([VarcharColumn_1]) + DATALENGTH([VarcharColumn_N])) * 2 ) + 
   SUM(4 * [number_of_INT_columns]) +
   SUM(8 * [number_of_BIGINT_and_DATETIME_columns]) +
   SUM(16 * [number_of_DECIMAL/NUMERIC_and_UNIQUEIDENTIFIER_columns]) +
   etc..
FROM [SchemaName].[TableName] WITH (NOLOCK) -- assuming no Snapshot Isolation

Mas lembre-se, isso não leva em conta a sobrecarga do elemento de coleção ou coleção. E não tenho certeza se podemos obter esse valor sem um depurador (ou possivelmente algo como ILSpy, mas não estou recomendando isso, pois pode violar o EULA dependendo das leis locais).

Pela sua pergunta, parece que você tem um tamanho máximo de cache Se não deseja carregar tabelas no cache que excedam esse tamanho. Se isso for verdade, você não precisa saber o tamanho exato de cada tabela. Você só precisa saber se uma tabela é maior ou menor que o tamanho máximo do cache S. Esse é um problema significativamente mais fácil, dependendo das definições de coluna e das contagens de linhas de suas tabelas.

Concordo com a ótima resposta de Solomon Rutzky em que olhar para dados descompactados não é o caminho a seguir e pode ser difícil encontrar uma boa aproximação para o tamanho real de uma tabela em cache. No entanto, vou trabalhar dentro da estrutura da pergunta e assumir que você pode desenvolver uma fórmula que seja próxima o suficiente com base nas definições de coluna para tipos de dados estáticos e no comprimento real de suas colunas dinâmicas.

Se você tiver esse mapeamento de tipos de dados para o tamanho do cache, poderá avaliar algumas tabelas sem nem olhar para os dados nelas:

1. Se uma tabela tiver apenas tipos de dados estáticos (sem strings ou blobs), você poderá aproximar o número de linhas observando sys.partitionse calculando o tamanho da tabela usando definições de coluna.
2. Se uma tabela com muitas linhas tiver colunas de tipo de dados estáticos suficientes, você poderá eliminá-la por ser muito grande sem examinar seus dados. Por exemplo, uma tabela com 10 milhões de linhas e 5 BIGINTcolunas pode ter o tamanho desses dados como 10000000 * (8+8+8+8+8) = 400 M bytes, o que pode ser maior que o limite de tamanho do cache S. Não importa se também tem um monte de colunas de string.
3. Se uma tabela com poucas linhas for pequena o suficiente, você poderá confirmar que está abaixo do limite simplesmente assumindo que cada tipo de dados dinâmico tem o tamanho máximo possível. Por exemplo, uma tabela de 100 linhas com uma BIGINTcoluna e uma NVARCHAR(20)coluna não pode exceder 100 * (8 + 2 * 20) = 4800 bytes.
4. Pode ser verdade que, se uma tabela tiver um tamanho compactado no SQL Server que seja maior por algum fator S, é extremamente improvável que caiba no cache. Você teria que fazer testes para descobrir se esse valor existe.
5. Você pode ter sorte se todas as colunas dinâmicas tiverem estatísticas sobre elas. As estatísticas contêm informações sobre o comprimento médio e que podem ser precisas o suficiente para seus propósitos.

Você pode ter que consultar os dados de tabelas que não se encaixam em nenhum dos critérios acima. Existem alguns truques que você pode usar para minimizar o impacto no desempenho disso. Eu diria que você tem duas prioridades concorrentes aqui: você valoriza a precisão, mas também não deseja verificar todos os dados em seu banco de dados. Pode ser possível adicionar algum tipo de buffer aos seus cálculos. Não sei se é mais aceitável excluir uma tabela que está um pouco abaixo do tamanho máximo de cache Sou incluir uma tabela que está um pouco acima do tamanho máximo de cache.

Aqui estão algumas ideias para tornar as consultas que analisam os dados da tabela mais rápidas:

1. Para tabelas grandes, você pode usar TABLESAMPLEdesde que o tamanho da amostra seja grande o suficiente.
2. Para tabelas grandes com uma chave clusterizada, pode ser útil processá-las em lotes na chave clusterizada. Infelizmente, não conheço uma maneira de calcular um SUM()que saia cedo com base no valor desse agregado. Eu só vi esse trabalho para ROW_NUMBER(). Mas você pode escanear os primeiros 10% da tabela, salvar o tamanho dos dados calculados, escanear os próximos 10% e assim por diante. Para tabelas que são muito grandes para o cache, você pode economizar uma quantidade significativa de trabalho com essa abordagem saindo mais cedo.
3. Para algumas tabelas, você pode ter a sorte de ter índices de cobertura em todas as colunas dinâmicas. Dependendo do tamanho da linha ou de outros fatores, a verificação de cada índice de cada vez pode ser mais rápida do que uma verificação de tabela. Você também pode encerrar esse processo antecipadamente se o tamanho da tabela for muito grande após a leitura de um índice em uma única coluna.
4. Os comprimentos médios de suas colunas dinâmicas podem não mudar muito ao longo do tempo. Pode ser prático economizar os comprimentos médios que você calcula e usar esses valores em seus cálculos por um tempo. Você pode redefinir esses valores com base na atividade DML nas tabelas ou com base em alguma outra métrica.
5. Se for possível executar testes em todas as tabelas para desenvolver um algoritmo, você poderá aproveitar os padrões nos dados. Por exemplo, se você processar tabelas começando com a menor primeiro, poderá descobrir que, depois de processar 10 (eu inventei esse número) tabelas em uma linha que são muito grandes para o cache, é muito improvável que quaisquer tabelas maiores caibam no cache. Isso pode ser aceitável se for possível excluir algumas tabelas que poderiam caber no cache.

Percebo que não incluí nenhum código SQL nesta resposta. Deixe-me saber se seria útil escrever um código de demonstração para qualquer uma das ideias que discuti aqui.