Cláusula WHERE

Na cláusula WHERE, podemos empregar vários operadores que podem afetar a performance da consulta. Sabemos que a cláusula WHERE é usada, na maioria dos casos, para limitar o número de linhas retornadas no RESULT SET. Certos operadores tendem a ser mais rápidos do que outros. Por vezes, nós temos que usar determinados operadores e não podemos mudar essa situação, noutro caso poderemos usar operadores que promoverão uma melhor performance.

A cláusula WHERE pode incluir uma variedade de condições de pesquisa que pode incluir operadores booleanos e predicados tais como LIKE, BETWEEN, EXISTS, IS NULL, IS NOT NULL e CONTAINS.

O uso de índices na cláusula WHERE pode ocorrer ou não, dependendo das condições de pesquisa. A regra é simples. Condições de pesquisa excludentes geralmente fazem com que o Query Optimizer não empregue o índice nas colunas referenciadas na cláusula WHERE. Enquanto que condições de pesquisa includentes fazem com que o Query Optimizer utilize os índices nas colunas referenciadas na cláusula WHERE.

Quando possível devemos evitar as condições de pesquisa excludentes. Aqui apresento uma lista de condições de pesquisa excludentes e includentes.

Condições de pesquisa includentes
=
>
>=
<
<=
BETWEEN
LIKE 'literal%'

Condições de pesquisa Excludentes
<>
!=
!>
!<
NOT IN
NOT LIKE IN
LIKE '%literal'

Podemos empregar também operadores booleanos, incluindo AND, OR, e NOT, quando temos múltiplos critérios na cláusula WHERE. Quando o Mecanismo de Banco de Dados valida e compila uma consulta, as condições são avaliadas na seguinte orderm: NOT, AND, OR. Podemos usar parenteses para controlar a ordem das operações. O uso do operador AND resulta tipicamente em RESULT SETS enxutos, aprimorando a performance. Já o operador NOT prejudica a performance, como vimos nas condições de pesquisa excludentes. No caso do operador OR, todas as colunas referenciadas pela condição OR devem estar incluidas no índice ou nenhum dos índices será usado.

Exemplo onde OrderDate não faz parte do índice: (AdventureWorks2008R2)

SELECT  soh.[SalesOrderID]
,soh.[OrderDate]
,soh.[ShipDate]
,sod.[ProductID]
,sod.[OrderQty]
,sod.[UnitPrice]
,soh.[CustomerID]
,soh.[PurchaseOrderNumber]
FROM    [Sales].[SalesOrderHeader] AS soh
 JOIN [Sales].[SalesOrderDetail] AS sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.OrderDate = '2008-07-31 00:00:00.000' OR
soh.OrderDate = '2008-07-05 00:00:00.000'

O Query Optimizer, índices e a cláusula WHERE

É comum usarmos a cláusula WHERE para limitar o número de linhas retornadas no conjunto restante (result set) de uma consulta.

O hábito de criar cláusulas WHERE bem escritas aumenta a performance pois limita a quantidade de dados a serem retornados para a aplicação cliente.

O componente responsável pela execução da consulta criada por nós é o recurso Query Optimizer do SQL Server. Basicamente, o Query Optimizer irá avaliar se para obter os dados será melhor ler todas as linhas da tabela (TableScan) ou ler muitas linhas mas não todas as linhas (Clustered Index Scan) ou usar uma pesquisa através de índices (Clustered Index Seek ou Index Seek), ou seja, quando o Query Optimizer entra em ação inicia uma série de análises a fim de encontrar qual é a maneira mais eficiente de acessar os dados desejados.

Durante a fase de análise o Query Optimizer realiza algumas tarefas:

– Identificar todos os possíveis argumentos de pesquisa que podem ser usados na cláusula WHERE;
– Verificar se existem JOINS entre as tabelas.

A cláusula WHERE pode contemplar uma grande variedade de condições de pesquisa incluindo operadores booleanos e predicados tais como LIKE, BETWEEN, EXISTS, IS NULL, IS NOT NULL e CONTAINS.

O Query Optimizer trabalha baseado no custo de cada operador de acesso a dados.Por exemplo, o uso do operador AND permite reduzir o conjunto resultante levando a melhora na performance.Já o uso do operador NOT reduz a performance da consulta porque o recurso Query Optimizer não pode utilizar os índices existentes na cláusula WHERE. Para que os índices sejam usados quando emmpregamos o operador OR, todas as colunas referenciadas pelo operador devem fazer parte do índice ou nenhum dos índices existentes será utilizado.

A cláusula LIKE é empregada com os caracteres: percentual(%), sublinhado(_), par de colchetes([ ]) e circunflexo(^). Estes caracteres não permitem que o recurso Query Opimizer empregue os índices existentes para otimizar a pesquisa.

É comum fazermos JOINS envolvendo mais de duas tabelas onde buscamos atender as necessidades das nossas consultas. Um dica básica de performance é evitar operações de junção que incluem mais de quatro ou cinco tabelas pois podemos criar consultas bem demoradas e que consomem muitos recursos do banco.

O SQL Server emprega três tipos de operações de junção:
- Junções de loops aninhados (Nested-loop joins)
- Junções de mesclagem (Merge-joins)
- Junções de hash (Hash-join)

Faz-se necessário verificar se o Query Optimizer está usando um HASH JOIN, o que é muito comum em JOINS que não tem índices adequados para a realizar a consulta solicitada.

HASH JOINS usam muita CPU e I/O e assim reduzem a performance do JOIN. Quando adicionamos índices para as colunas certas, o Query Optimizer poderá usar estes índices, realizando, com melhor performance, um NESTED-LOOP JOIN, e não escolhendo o HASH JOIN.
 
Exemplos em que o Query Optimizer não usa índices (banco AdventureWorks2008R2):

a) Table scan
- Primeiro vamos criar uma cópia da tabela Sales.SalesOrderHeader:

SELECT *
INTO Sales.SalesOrderHeaderCopia
FROM Sales.SalesOrderHeader

- Vamos fazer uma consulta na nova tabela que não possui qualquer índice:

SELECT *
FROM Sales.SalesOrderHeaderCopia

SELECT custo:   0%
Table scan  : 100%

b) Consulta simples (CustomerID faz parte do índice)

SELECT  soh.[SalesOrderID]
,soh.[OrderDate]
,soh.[ShipDate]
,sod.[ProductID]
,sod.[OrderQty]
,sod.[UnitPrice]
,soh.[CustomerID]
,soh.[PurchaseOrderNumber]
FROM    [Sales].[SalesOrderHeader] AS soh
 JOIN [Sales].[SalesOrderDetail] AS sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.[CustomerID] = 29559;

SELECT custo:    0%
Inner Join custo:0%
Inner Join custo:1%
Index Seek (NonClustered) SalesOrderHeader usando índice IX_SalesOrderHeader_CustomerID custo: 9%
Key Lookup (Clustered) SalesOrderHeader usando PK_SalesOrderHeader_SalesOrderID custo : 45%
Clustered Index seek SalesOrderDetail usando PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID custo: 45%

c) Operador OR (OrderDate não faz parte do índice) - faz scan no índice

SELECT  soh.[SalesOrderID]
,soh.[OrderDate]
,soh.[ShipDate]
,sod.[ProductID]
,sod.[OrderQty]
,sod.[UnitPrice]
,soh.[CustomerID]
,soh.[PurchaseOrderNumber]
FROM    [Sales].[SalesOrderHeader] AS soh
 JOIN [Sales].[SalesOrderDetail] AS sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.OrderDate = '2008-07-31 00:00:00.000' OR soh.OrderDate = '2008-07-05 00:00:00.000'

SELECT custo:    0%
Inner Join custo:4%
Clustered Index Scan usando PK_SalesOrderHeader_SalesOrderID custo:  68%
Clustered Index seek SalesOrderHeader usando PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID custo: 28%

d) Operador OR (OrderDate faz parte do índice)

SELECT  soh.[SalesOrderID]
,soh.[OrderDate]
,soh.[ShipDate]
,sod.[ProductID]
,sod.[OrderQty]
,sod.[UnitPrice]
,soh.[CustomerID]
,soh.[PurchaseOrderNumber]
FROM    [Sales].[SalesOrderHeader] AS soh
 JOIN [Sales].[SalesOrderDetail] AS sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.OrderDate = '2008-07-31 00:00:00.000' OR soh.OrderDate = '2008-07-05 00:00:00.000'

SELECT custo:    0%
Inner Join custo:1%
Index Seek (Non-clustered) usando índice IX_SalesOrderHeader_OrderDate custo:  1%
Key Lookup (Clustered) SalesOrderHeader usando PK_SalesOrderHeader_SalesOrderID custo:                  49%
Clustered Index seek SalesOrderDetail usando PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID custo: 50%

e) Cláusula LIKE - faz scan no índice

SELECT  soh.[SalesOrderID]
,soh.[OrderDate]
,soh.[ShipDate]
,sod.[ProductID]
,sod.[OrderQty]
,sod.[UnitPrice]
,soh.[CustomerID]
,soh.[PurchaseOrderNumber]
FROM    [Sales].[SalesOrderHeader] AS soh
 JOIN [Sales].[SalesOrderDetail] AS sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.[PurchaseOrderNumber] LIKE 'PO1%'

SELECT custo:              0%  

Merge - Inner Join custo: 16%
Clustered Index Scan usando PK_SalesOrderHeader_SalesOrderID custo:  29%
Clustered Index Scan usando PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID custo: 56%

No SQL Server, temos 2 tipos de índices:

 - Índices Clusterizados (Clustered Indexes)
 - Índices Não Clusterizados (Nonclustered Indexes)

O índice clusterizado, é um índice gerado na própria estrutura de armazenamento dos dados, pois esse índice, fará com que os dados da tabela, fiquem organizados fisicamente na sequência. Por esse motivo, só podemos ter 1 (um) índice clusterizado por tabela. E em qual coluna devemos criar índices clusterizados? Por exemplo, por padrão, quando criamos uma PK, um clustered index é criado automaticamente.

O índice não clusterizado, é um índice criado em uma estrutura separada dos dados físicos. São criadas páginas de índices que irão apontar para os registros físicos. É eficiente quando precisamos ter várias maneiras de pesquisa de dados dentro de uma tabela como fazemos num livro, ao consultar o índice ou sumário.

Um ponto importante de se ressaltar ao escolher uma coluna para criar índices, é a sua seletividade. Quanto maior a sua seletividade, ou seja, quanto mais valores diferentes a coluna tiver, melhor. Por exemplo, não deve-se criar um índice numa coluna que só contém dois tipos de valores, como no campo sexo (M ou F). Outro fator, é observar as colunas que são mais usadas para filtrar os dados de uma tabela, como por exemplo, colunas com datas, pois não adianta você criar um índice em uma coluna que não é frequentemente utilizada para filtrar os dados.

Existem dois tipos de scans realizados pelo SQL Server:

- Table Scan
- Index Scan

Quando um Table Scan é realizado, todos os registro da tabela são examinados, um a um. Para tabelas grandes, o custo pode ser bem alto. Mas  para tabelas (ainda) pequenas, um table scan pode ser tão rápido quanto um Index Seek. Mas pode ser que essa tabela venha a ter mais linhas levando a criação de um ou mais índices.

Quando um Index Scan é realizado, todas as linhas do nível folha do índice são examinados pois no nível folha do índice cluster temos a própria tabela. O que isso significa? Essencialmente, significa que todas as linhas da tabela ou os índices são examinados. Em certos casos, o Query Optimizer determina que um Index Scan é mais eficiente que um Table Scan, então o index scan é executado, embora a diferença de

performance para um table scan seja pequena, apresenta performance reduzida se comparado ao index seek.

Vimos acima que, mesmo que uma tabela tenha índices, um Index Seek pode não ser executado, pois não criamos cláusulas WHERE bem escritas. Em alguns casos escrevemos cláusulas WHERE adequadas, mas continuamos tendo uma performance reduzida. Porém, isto pode indicar que os índices existentes não são seletivos o bastante. Estes são aqueles casos em que o Query Optimizer considera o índice pouco útil, e opta pelo Index Scan.

Quando o Query Optimizer é acionado para otimizar uma consulta e assim criar um plano de execução para a consulta, ele faz o possível para usar um Index Seek.Um Index Seek significa que o Query Optimizer é capaz de encontrar um índice útil a fim de localizar as linhas apropriadas. Sabemos que, os índices fazem com que a recuperação dos dados seja rápida no SQL Server.Mas quando o Query Optimizer não é capaz de fazer um Index Seek, seja porque não existem índices ou não existem índices adequados, então o SQL Server fará um busca em todos os registros para resolver a consulta.

Temos algumas opções quando um index scan continua sendo a escolha do Query Optimizer:

- Não fazer nada, pois a tabela é pequena e poderá permanecer assim por muito tempo;
- Não faze nada, pois a consulta retorna poucos registros mas vamos monitorar seu crescimento;
- Rever os índices, avaliando com cuidado, pois criar índices também tem seu custo;
- Rever os índices, pois a partir do SQL Server 2008 pode-se acrescentar ao comando CREATE INDEX uma cláusula WHERE, filtrando linhas da tabela e  reduzir o tamanho do índice. Onde pode-se criar índices menores, gerando redução do espaço ocupado.
- Refazer a consulta.

Temos ainda mais um recurso de banco de dados que afeta a indexação das tabelas.No caso do SQL Server, quando criamos um índice, existe uma opção chamada Fill Factor. Esse recurso determina a quantidade de espaço em branco que deveremos deixar para as páginas de índices, para que o SQL Server possa inserir novos ponteiros, respeitando a ordenação daquele índice.

Mas e se não existir espaço nas páginas de índices?

O SQL Server irá criar novas paginas, mas não seguirá a ordenação, levando a fragmentação do índice.

O Fill Factor é um fator de preenchimento das páginas de índices. Nesse caso se definirmos um fill factor de 70%, o SQL Server entenderá que deve preencher a pagina de índices, com 70% de sua capacidade total, deixando 30% para novos ponteiros que surgirão de novas atualizações. Logo, saber ajustar o fill factor também afetará na performance das consultas.

Por isso tudo que foi visto os índices podem melhorar muito a performance das consultas mas precisam sempre ser avaliados, desfragmentados e as vezes até recriados.Afinal, o uso de nenhum ou poucos índices impacta na performance e o uso excessivo de índices tem um custo muito alto.

Futuramente falaremos sobre as ferramentas que nos mostram informações sobre a utilização de índices. Novos recursos do SQL Server que auxiliam nesta importante tarefa de otimização dos índices.

Procurando uma string em todas as procedures, views, functions, triggers etc

Aqui apresento uma solução para o problema proposto:

CREATE FUNCTION [dbo].[Search]
(   
@str VARCHAR(100)
) RETURNS TABLE RETURN   

SELECT  DISTINCT   
     s.name AS SchemaName,
     o.name AS ObjectName,
     o.type_desc AS ObjectType   

FROM sys.objects o    INNER JOIN syscomments c
    ON c.Id = o.object_id    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id   
WHERE     is_ms_shipped = 0 AND c.text like '%' + @str + '%'

Ao usar esta function, podemos pesquisar por  uma string em todos os objetos do banco e aplicar um filtro para extrair linhas específicas. 

Vejamos alguns exemplos:

a) SELECT * FROM Search('BusinessEntityID')

b) SELECT * FROM Search('BusinessEntityID')  WHERE ObjectType = 'VIEW'
c) SELECT * FROM Search('BusinessEntityID')
    WHERE ObjectType = 'SQL_STORED_PROCEDURE'  AND SchemaName = 'HumanResources'

Abuse e use...

sp_rename e sp_helptext. Será um erro?

Ao renomear uma procedure utilizando a procedure sp_rename talvez tenha encontrado um erro no SQL Server. Vejamos:

1) Vamo criar a procedure abaixo:

                   USE AdventureWorks2008R2
                   GO
                   CREATE PROCEDURE sp_Employee
                   AS
                   SELECT * FROM HumanResources.Employee
                   WHERE JobTitle LIKE '%i%'
                   ORDER BY 1
                   GO

2) Vamos renomear a procedure:

                   sp_rename 'sp_Employee', 'sp_OtherEmployee'

3)  Vamos verificar o texto da nova procedure:

                   USE AdventureWorks2008R2
                   GO
                   sp_helptext sp_OtherEmployee 

4) Observe o resultado e constate que o nome da procedure retornado ainda é o antigo.

          CREATE PROCEDURE sp_Employee 
          AS 
          SELECT * FROM HumanResources.Employee
          WHERE JobTitle LIKE '%i%'
          ORDER BY 1

5) Observe que ao tentar visualizar o conteúdo da procedure usando o nome antigo ganhamos o erro abaixo:

The object 'sp_employee' does not exist in database 'AdventureWorks2008R2' or is invalid for this operation.

6) Agora execute a procedure com o novo nome!!!

Conclusão:

O problema está na procedure sp_helptext. Além disso, a situação acima também se dá em Views, Triggers e Functions etc.

Questão:

Se o nome da procedure que está no corpo da procedure não é atualizado, como o SQL Server executa a procedure correta?

Resposta:

O nome da procedure que estava no System Metadata foi atualizado, mas não foi atualizado na definição da procedure. Quando executamos uma stored procedure, o SQL Server encontra o ObjectID da stored procedure e recupera o corpo da procedure usando o ObjectID e executa o corpo da procedure. Por isso, ao executar a procedure não ocorreu qualquer erro.

Quando o SQL Server Query Optimizer está Errado

Na maioria dos casos, o SQL Server Optimizer gera planos ótimos. É impossível competir com o seu conhecimento interno do custo de acesso ao disco, tamanho de página e também quanto a taxa de preenchimento de uma página. Mas, há uma área onde a expertise humana é sempre superior.

Você  pode pode pular direto para os resultados ou se quiser crie as tabelas abaixo em um banco vazio:

                                         Create table Sales
                                         (
                                         Id int primary key,
                                        Amount money not null,
                                        Date datetime not null,
                                        Comment varchar(128)
                                        )
                                        GO

                                       Create table SaleItems
                                       (
                                       SaleId int,
                                       ItemId int not null,
                                      Quantity int
                                       )
                                       GO

A tabela de vendas terá 10.000 registros aleatórios. A tabela SalesItems vai ter de 1 a 10 itens para cada registro de venda na tabela Sales. Vamos executar os scripts a seguir para preencher as duas tabelas e criar índices:

set nocount on
GO
declare @n int, @m int
set @n=10000
while @n>0 begin
insert into Sales select @n,$10.0*(@n%100)+$100.,dateadd(hh,-@n,'20070507'),
'This is sale N '+convert(varchar,@n)
set @m=@n%10
while @m>0 begin
insert into SaleItems select @n,@m,@m+@n
set @m=@m-1
end
set @n=@n-1
end
GO
create index SaleItems_Id on SaleItems (SaleId)
GO
create index Sales_Dates on Sales (Date)
GO

Notemos que Sales.Date varia de 2006-03-16 08:00 até 2007-05-06 23:00.
Agora, fazemos uma primeira tentativa de escrever um procedimento  que recupera a quantidade máxima de venda por um período de tempo especificado.

create procedure GetMaxQuantity
@p1 datetime, @p2 datetime
as
select max(Quantity) from SaleItems where SaleId in (
select Id from Sales where Date between @p1 and @p2)
GO

Antes de executar o procedimento, vamos habilitar as Estatísticas de IO, executando:

set statistics io on

Também é importante que o cache do buffer seja limpo, antes de executar o procedimento. Caso contrário, o número de leituras físicas não será preciso:

DBCC DROPCLEANBUFFERS 

Agora, vamos executar o procedimento:

exec GetMaxQuantity '20070301' , '20070302'

No ambiente de teste,  recebo as seguintes estatísticas de IO:

Table 'SaleItems'. Scan count 25, logical reads 191, physical reads 1, read-ahead reads 4.
Table 'Sales'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.

Vamos executar o procedimento novamente com um intervalo de datas diferentes:

dbcc dropcleanbuffers
exec GetMaxQuantity '20060301','20070302

Desta vez, as seguintes estatísticas de IO são retornadas:

Table 'SaleItems'. Scan count 8417, logical reads 55564, physical reads 90, 
read-ahead reads 173.
Table 'Sales'. Scan count 1, logical reads 17, physical reads 1, read-ahead reads 16.

É importante que você execute a primeira consulta antes da segunda. Não há nenhuma surpresa quanto a segunda execução, para todo o ano, exija muito mais leituras lógicas e físicas. Embora, 55.564 leituras lógicas seja muito alto. Então o que aconteceu?

Quando verificamos o plano de exução percebemos que o problema está no Row count, onde o valor esperado é 23. Mas, quando executamos a instrução abaixo encontramos o problema:

select count(*) from Sales where Date between '20060301' and '20070302'

Na verdade, há 8.417 registros em vez de 23, o que era esperado pelo Query Optimizer.
Existem 37.884 registros em vez dos 118 esperados!

select count(*) from SaleItems where SaleId in (
select Id from Sales where Date between '20060301' and '20070302')

Como podemos ver, quando as constantes são fornecidas, o SQL Server pode obter informações das tabelas estatísticas. Quando o Query Optimizer percebe que há muitos registros, ele muda o plano de execução e, usa um Hash Join.

Este é o cenário mais comum: o SQL Server subestimou o número de linhas. O Query Optimizer pode superestimar o número de linhas, e usar um Hash Join e ler toda a tabela quando realmente precisaria de apenas algumas linhas.

Técnicas de análise e otimização requerem abordagem individual, mas também de todo o conjunto de consultas. Em geral, recompilar os planos de execução deve ser evitado devido à perda de desempenho. Isto pode ser evitado pelo uso de consultas com parâmetros e procedimentos, evitando também cursores sobre tabelas temporárias. No entanto, recompilar planos pode trazer benefícios quando o Query Optimizer é capaz de criar um plano de execução mais eficiente.
É muito importante que monitoremos por meio de estatísticas (se forem mantidas atualizadas) e usar o Profiler para execução de consultas custosas, bem como para verificação e fiscalização da utilização dos recursos.

A técnica de otimização mais importante supõe limitar a quantidade de dados retornados, limitando o número de registros (cláusula WHERE) e os campos especificados na lista SELECT. Isto levará a uma utilização eficiente dos índices. Em princípio, uma cláusula WHERE deve ser seletiva, pois podemos usar os índices existentes nas colunas.

SQL Server - DBCC Log

Recurso não documentado, a declaração DBCC Log permite que você obtenha informações sobre as informações contidas no Transaction Log. A sintaxe é:

              DBCC Log (<nomedobanco>,<tipo de saída>)

Para o parâmetro tipo de saída temos:

0 - informação mínima (operação, contexto, id de transação) - DEFAULT.
1 - mais informação (flags, tags, tamanho da linha).
2 - innformações bem detalhadas (nome do objeto, nome de índice, id de página, id do slot).
3 - detalhamento de cada operação.
4 - detalhamento de cada operação mais dump hexadecimal da transação corrente da linha do transaction log.
-1 - detalhamento de cada operação mais dump hexadecimal da transação corrente da linha do transaction log, mais o Checkpoint Begin, DB Version, Max XDESID.

Alguns produtos que podem ser ussados na leitura de logs:

• Apex SQL Log (para MSSQL 2000 e MSSQL 2005)
• Log Explorer
• SQL Log Rescue (somente para SQL Server 2000)

Exemplos:

1) DBCC LOG (Master)
2) DBCC LOG (AdventureWorks,4)

Outro comando não documentado é o DBCC LOGINFO.