Como corrigir divergências entre a quantidade de linhas de uma tabela e o catálogo de dados no SQL Server
Fala Galera,
Espero que estejam todos bem ?
Vocês já ouviram falar sobre a função DBCC UPDATE_USAGE ? Já aconteceu com vocês ao consultar a quantidade de linhas de um objeto no catálogo de dados ele estar diferente do valor real da tabela?
Pois é, isso aconteceu comigo esses dias e eu tive que pesquisar bastante até encontrar a solução, confesso que na hora deu até um frio na barriga pensando que tivesse sumido informações da base de dados do cliente hehehe.?
A ideia desse post é compartilhar com vocês essa dica muito útil para que saibam como agir em uma situação semelhante.
Hoje vamos simular esse cenário e demonstrar as possíveis formas de como deixar o valor do catálogo igual aos valores da tabela.
Laboratório :
Para simular a divergência no catálogo, começarei “clonando” a estrutura da minha base de dados StackOverflow2013.
Recentemente eu fiz um post ensinando a utilizar a função DBCC CLONEDATABASE, caso não tenham visto, sugiro que leiam o post para ficar por dentro do assunto. ?
https://gustavolarocca.com.br/um-jeito-simples-de-clonar-a-estrutura-do-seu-banco-de-dados/
DBCC CLONEDATABASE (StackOverflow2013,StackOverflow2013_NEW) WITH VERIFY_CLONEDB;
Sabendo que o CLONE é apenas estrutural, faremos um Count na tabela dbo.VoteTypes apenas para confirmar a inexistência de dados nesta tabela da base clonada.
USE [StackOverflow2013_NEW]
GO
SELECT COUNT(*) AS Qtde_Linhas FROM dbo.VoteTypes
OK, nenhuma linha, está perfeito por enquanto!
Agora vamos consultar o catálogo de dados dessa tabela a fim de validar a quantidade de linhas da tabela por lá.
USE [StackOverflow2013_NEW]
GO
SELECT SCHEMA_NAME(schema_id) AS 'schema',
tabledata.name AS 'table',
SUM(partitions.rows) AS 'rows'
FROM sys.tables AS tabledata
JOIN sys.partitions AS partitions
ON tabledata.object_id = partitions.object_id
AND partitions.index_id IN ( 0, 1 )
WHERE tabledata.name = 'VoteTypes'
GROUP BY SCHEMA_NAME(schema_id),
tabledata.name;
Nossa, que loucura!! O que aconteceu aí?? Primeiro foi feito um count na tabela e não trouxe nenhum registro (o que já era esperado), mas ao olhar por dentro do catálogo está constando que a tabela possui 15 linhas??
Validando com o sp_spaceused.
sp_spaceused 'dbo.VoteTypes'
Eita, mesma coisa, errado também!!
E se a gente inserir os dados nessa tabela? O que acontece?
Primeiramente vamos configurar a base clonada para READ_WRITE (Visto que ao clonar ela fica automaticamente em READ_ONLY).
ALTER DATABASE [StackOverflow2013_NEW] SET READ_WRITE;
Populando dados na tabela VoteTypes(da base StackOverflow2013_NEW) com os registros da tabela VoteTypes(da base StackOverflow2013)…
USE [StackOverflow2013_NEW]
GO
INSERT INTO dbo.VoteTypes
(
Name
)
SELECT Name FROM [StackOverflow2013].dbo.VoteTypes
Conferindo a quantidade de registros na tabela VoteTypes..
Consultando o catálogo na sp_spaceused da tabela VoteTypes novamente no banco StackOverflow2013_NEW…
Eitaa… dobrou o valor, e agora?
Entendendo o problema:
É quase certo que esse “problema” ocorreu quando “clonamos” a estrutura da nossa base de dados. O SQL Server nesse processo levou também os metadados armazenados das tabelas… Como as tabelas possuíam dados na origem ao clonar a estrutura essas informações foram mantidas e ao inserirmos os novos dados duplicaram os valores. E foi exatamente esse problema que eu enfrentei nesse cliente que comentei no início deste artigo.
Como resolver:
Temos as duas opções que podem ser uteis neste tipo de cenário:
- DBCC UPDATE_USAGE
Esse comando relata e corrige inexatidões de contagem de páginas e linhas nas exibições do catálogo.
Ex.:
DBCC UPDATEUSAGE (StackOverflow2013_NEW,'dbo.VoteTypes') WITH COUNT_ROWS;
Também podemos atualizar as páginas do catálogo de toda a base de dados se desejarmos.
DBCC UPDATEUSAGE(StackOverflow2013_NEW) WITH COUNT_ROWS;
Esse comando pode ser utilizado de algumas formas, por tabela, por banco, por nome de índice,etc…
Vale ressaltar que só vamos ter a informação atualizada na sp_spaceused se utilizarmos o parâmetro COUNT_ROWS.
O SQL Server consulta as informações para retornar os resultados dessa procedure das DMV’s (sysindexes e sys.dm_db_partition_stats).
Atenção : Caso esteja pensando em utilizar esse comando no seu ambiente de produção , sugiro que execute em uma janela de manutenção planejada, visto que esse comando pode demorar bastante (a depender do tamanho da tabela) e gerar bloqueios durante sua execução.
Para maiores informações consulte a documentação oficial abaixo:
- REBUILD na tabela.
O processo de REBUILD na tabela também irá fazer com que o catálogo da sua tabela seja atualizado. De maneira que o otimizador irá mapear todos os ponteiros novamente e se houver índices reconstruí-los. Isso inevitavelmente deixará o catálogo atualizado.
USE [StackOverflow2013_NEW]
GO
ALTER TABLE dbo.VoteTypes REBUILD
Para maiores informações sobre o Rebuild, segue a documentação.
https://docs.microsoft.com/pt-br/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15
Consultando o catálogo após as ações realizadas:
USE [StackOverflow2013_NEW]
GO
SELECT COUNT(*) AS Qtde_Linhas FROM dbo.VoteTypes
sp_spaceused 'dbo.VoteTypes'
Agora sim!! certinho!! ?
É isso aí pessoal, espero que tenham gostado do artigo!
Qualquer dúvida deixe nos comentários.
Um grande abraço a todos!!
Gustavo Larocca
Consultor SQL Server
Gostou do conteúdo?
Acompanhe novos artigos sobre SQL Server, performance e administração de banco de dados.
