Identificando consultas demoradas com o SQL Server Profiler

Antes de publicar os artigos sobre eventos com SMO (Trace e Events) que serão os temas a serem trabalhados nas próximas semanas, identifiquei a necessidade de mostrar o funcionamento do SQL Server Profiler, antes que alguém tente criar um SQL Server Profiler com o SMO.

O SQL Server Profiler é uma ferramenta normalmente utilizada por DBAs para identificar consultas lentas, diagnosticar problemas com deadlock e desempenho, e gerar cargas de consultas com o Database Engine Tuning Advisor.

Caso você nunca tenha falado de SQL Server Profiler, fique tranquilo, só pense que ele permite capturar eventos do SQL Server, como no caso, execução de consultas e procedures.

Para um exemplo bem simples, vamos executar o “Performance Tools\SQL Server Profiler”:

Ir em “File/New Trace…”:

Conectar a um servidor SQL Server:

E vamos nos deparar com a seguinte tela:

Especifique nesta tela um nome para seu Trace e o template a ser utilizado, no meu caso, o “TSQL_Duration”:

E você também pode definir um arquivo ou tabela para salvar o trace, exemplo:

Na aba “Events Selecion”, será possível definir filtros dos eventos a serem monitorados, assim como adicionar/remover eventos e especificar os dados a serem capturados de cada evento:

Prosseguindo com o botão “Run”, será possível ver em tempo real o que esta sendo executado no SQL Server e o tempo de execução de cada evento:

Ferramenta interessante, não? Então após se divertir com ela e conhecer um pouco mais de cada template, procure aprender como utilizar filtros e ordenação, criar seus próprios templates, assim como identificar recursos desta ferramenta para diagnosticar gargalos de desempenho e consultas lentas, e como trabalhar com ele junto com o Perfmon e o Database Engine Tuning Advisor.

Após utilizar o SQL Server Profiler, não se esqueça de parar o Trace, para não o deixar rodando sem necessidade no seu SQL Server.

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 é 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:

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.

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.

Consultas diferentes, o mesmo plano de execução

E ai pessoas!

Neste final de semana depois de ler alguns artigos perdidos pela internet, como o sobre “SQL SERVER – Convert IN to EXISTS – Performance Talk” e “SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best” do Pinal Dave, fiquei com vontade de criar um plano de execução diferente entre IN e EXISTS, mas realmente é impossível (pelos meios normais)…

Com sono, até vi um plano de execução diferente entre os dois, mas era o frio e o sono me fazendo escrever consultas erradas… #FAIL

Para demonstrar isso segue alguns exemplos de consultas diferentes que retornam o mesmo resultado e possuem o mesmo plano de execução (que surgiram no blog do Pinal Dave e algumas adaptações):

-- use of = any
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ANY (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO
-- use of = some
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = SOME (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO
-- use of in
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID IN (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO
-- use of exists
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS (SELECT *
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- use of cross apply
SELECT *
FROM HumanResources.Employee E
CROSS APPLY (SELECT *
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID) EA
GO
-- use of join
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress EA
ON E.EmployeeID = EA.EmployeeID
GO
-- use of join with subquery
SELECT *
FROM HumanResources.Employee E
INNER JOIN (SELECT *
FROM HumanResources.EmployeeAddress EA) EA
ON EA.EmployeeID = E.EmployeeID
GO

E o plano de execução:

Lembrando que há uma pequena diferença entre os quatro primeiros planos de execução e os três últimos, visto que o algoritmo de MERGE JOIN executado pelo primeiro grupo é um “LEFT SEMI JOIN” e o do segundo grupo é um “INNER JOIN”, assim o segundo grupo somente é valido para relacionamentos 1:0..1 (a não ser que você deseje que apareça dados da tabela “filha” no resultado, podendo fazer os dados da tabela “mãe” se repetirem para cada resultado encontrado na “filha”) e o primeiro grupo é valido tanto para relacionamentos 1:0..1 e 1:0..N.

As diferenças aparecem quando mudamos um pouco as consultas:

-- segundo grupo (INNER JOIN)
SELECT COUNT(*)
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress EA
ON E.EmployeeID = EA.EmployeeID
GO
-- primero grupo (LEFT SEMI JOIN)
SELECT COUNT(*)
FROM HumanResources.Employee E
WHERE E.EmployeeID IN (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO

E o plano de execução fica bem diferente, visto o INNER JOIN será semelhante a um COUNT(*) direto na tabela “filha” por causa de influência da foreign key:

Ou quando utilizamos NOT EXISTS/IN ou LEFT JOIN, podendo criar um plano de execução um pouco diferente, mas com o mesmo custo de execução:

-- segundo grupo (LEFT OUTER JOIN + FILTER)
SELECT *
FROM HumanResources.Employee E
LEFT JOIN HumanResources.EmployeeAddress EA
ON E.EmployeeID = EA.EmployeeID
WHERE EA.EmployeeID IS NULL
GO
-- primero grupo (LEFT ANTI SEMI JOIN)
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID NOT IN (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO

Espero que tenham gostado, por enquanto eu fico por aqui (neste frio de 3ºC de Curitiba-PR), até mais!!!