Número de registros de todas tabelas de um banco de dados (SQL Azure)

Olá pessoas,

Quem tentou executar a query presente no meu artigo Número de registros de todas tabelas de um banco de dados, pode ter percebido que a sys.partitions simplesmente não existe (ou é inacessível) no SQL Azure.

Então, com algumas alterações na query anterior, podemos obter o mesmo resultado até mesmo no SQL Azure.

SELECT
  s.[name]
, t.[name]
, [rows] = SUM(p.[row_count])
FROM sys.schemas s
INNER JOIN sys.tables t
  ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
  ON i.[object_id] = t.[object_id]
  AND i.[type] IN (0,1)
INNER JOIN sys.dm_db_partition_stats p
  ON p.[object_id] = t.[object_id]
  AND p.[index_id] = i.[index_id]
GROUP BY
  s.[name]
, t.[name]
ORDER BY
  s.[name]
, t.[name]

Agradecimentos ao Emílio Silva, por me apresentar este desafio.

Anúncios

Performance – Key Lookup e “Fake” Index Seek!?

O Key Lookup é utilizado para situações onde o índice não contempla todas as colunas da pesquisa, como por exemplo, você precisa encontrar o nome de uma pessoa pelo número de CPF, mas você somente possui a coluna CPF indexada por um índice não clustered, assim o Key Lookup será responsável por identificar o nome desta pessoa por meio da coluna do índice clustered que compõe os índices não clustered.

Os índices não clustered são estruturas compostas por uma ou mais colunas “indexadas” que serão utilizadas como critérios das pesquisas, e outras colunas auxiliares (included columns) utilizadas para o retorno das pesquisas, e a(s) coluna(s) do índice clustered utilizado para outros critérios mais complexos.

Uma situação interessante que ilustra bem como funciona um Key Lookup, é a utilização da técnica que denomino “Fake” Index Seek, no qual a consulta mascara um Key Lookup, obrigando o banco de dados pesquisar o índice clustered por meio de um índice não clustered (Index Seek sobre o CPF) e em seguida, pesquisar os outros campos (Codigo, Nome, Data Nascimento, CPF) por meio do índice clustered obtido (Clustered Index Seeek sobre a Primary Key), exemplo:

-- Criação da tabela
CREATE TABLE Clientes (
	Codigo INT IDENTITY PRIMARY KEY,
	Nome VARCHAR(250) NOT NULL,
	DataNasc DATETIME NOT NULL,
	NumeroCPF NUMERIC(11,0) NOT NULL
)

-- Criação do índice sobre a coluna NumeroCPF
CREATE INDEX IX_NumeroCPF ON Clientes(NumeroCPF)
-- Index Seek + Key Lookup
SELECT *
FROM Clientes
WHERE NumeroCPF = 520725

-- Fake Index Seek
SELECT *
FROM Clientes
-- Key Lookup "Mascarado"
WHERE Codigo IN (
	SELECT Codigo
	FROM Clientes
	-- Utilização do Index Seek
	WHERE NumeroCPF = 520725
)

Abaixo a semelhança entre “Fake” Index Seek e Key Lookup.

Fake Index Seek vs Key Lookup

“Fake” Index Seek é uma técnica que complica cenários preocupados em performance, pois é difícil de ser identificada por mascarar o Key Lookup, então evite utilizá-la!!!

Performance – Clustered Index Scan

É difícil identificar todos índices necessários para todas as consultas de um banco de dados em produção, mas tem alguma diferença entre uma consulta numa tabela HEAP (Table Scan) e uma tabela sem índices nos campos pesquisados (Clustered Index Scan)?

Para demonstrar que Table Scan e Clustered Index Scan podem ser igualmente problemáticos, abaixo um plano de execução que ilustra bem esta situação:

HEAP vs Clustered Index Scan

Se existem muitos “Clustered Index Scan”, é sinal que alguma coisa não esta indo muito bem, então, “dá-lhe” índices!

Os índices não clustered são estruturas compostas por uma ou mais colunas “indexadas” e outras colunas auxiliares (included columns), e a(s) coluna(s) do índice clustered (e quando índices não clustered estão em uma tabela HEAP, é usado um “identificador” do registro ao invés da coluna do índice clustered).

Performance – Heap

É comum encontrar chaves primárias como indexes clustered, tendo assim os registros armazenados em disco na mesma ordem das colunas que compõem as chaves, já que é uma configuração padrão das chaves primárias do SQL Server. Em algumas situações pode ser justificável utilizar outro índice da tabela como clustered ao invés da chave primária.

Então o que acontece quando uma tabela não tem uma chave primária clustered ou outro qualquer índex como clustered? Ela fica armazenada numa estrutura conhecida como HEAP (tradução livre: estrutura caótica de armazenamento de registros), que até em consultas simples ou tabelas com poucos registros, pode se tornar um gargalo para o banco de dados.

Para demonstrar um pouco do que seria este gargalo, abaixo a diferença relativa de custos de pesquisas entre uma tabela com chave primária clustered e outra heap, tendo as duas com aproximadamente 350.000 registros.

Clustered vs Heap

Uma tabela somente pode ter um índice ou chave primária como clustered e vários índices não clustered, mas não esqueça de definir ao menos um índice ou chave primária como clustered!

Importante, as estimativas dos planos de execução são igual previsão do tempo, nem sempre são 100% confiáveis, mas são ótimas formas de identificar problemas nas consultas.

Número de registros de todas tabelas de um banco de dados

Boa noite pessoas!

Como de uma forma prática podemos identificar quantos registros possuem todas as tabelas de um banco de dados? Bom, ao executar uma consulta semelhante a que temos abaixo, podemos realizar este procedimento para uma tabela.

SELECT COUNT(*) FROM Tabela

E para identificar quantos registros possuem várias tabelas poderíamos pensar em elaborar um CURSOR (tradução livre: entidade demoníaca que induz o programador aos artifícios técnicos não documentados), que pode executar para cada uma das tabelas do banco de dados a consulta semelhante a que temos acima. Mas será que é a forma mais adequada?

Ok, como alternativa, eu proponho utilizar os índices HEAP (tradução livre: estrutura caótica de armazenamento de registros) e CLUSTERED (tradução livre: estrutura organizada para o armazenamento de registros) das tabelas por meio da consulta:

SELECT *
FROM sys.indexes
WHERE [type] IN (0,1)

E a partir destes índeces, identificar quantas linhas/registros que estão relacionadas a eles:

SELECT
  i.[object_id]
, p.[rows]
FROM sys.indexes i
INNER JOIN sys.partitions p
  ON p.[object_id] = i.[object_id]
  AND p.[index_id] = i.[index_id]
WHERE i.[type] IN (0,1)

Visto que podemos identificar a tabela relacionada a estes índices por meio da coluna object_id, concluímos as consulta desta forma:

SELECT
  s.[name]
, t.[name]
, p.[rows]
FROM sys.schemas s
INNER JOIN sys.tables t
  ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
  ON i.[object_id] = t.[object_id]
  AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
  ON p.[object_id] = t.[object_id]
  AND p.[index_id] = i.[index_id]
ORDER BY
  s.[name]
, t.[name]

Outra pergunta, posso utilizar só a sys.partitions relacionada a sys.tables para identificar a quantidades de registros da tabela? Até pode, mas os resultados serão de acordo com os vários tipos de índices que as tabelas e views podem ter, que algumas vezes estes índices não obedecem a mesma regra aplicada aos índices HEAP e CLUSTERED.