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.
Nenhum comentário:
Postar um comentário