Dica do Mês – Utilizando a Trace Flag 9292 para exibir objetos estatísticos úteis


 

Olá comunidade, boa tarde.

Tudo bem? Estamos se aproximando dos últimos dias de férias ou recesso para grande maioria dos professores e profissionais de educação espalhados por todo Brasil. E ai, já esta preparado para voltar a luta? Posso dizer tranquilamente que sim, eu estou pronto para voltar a conviver com meus alunos e amigos de trabalho.

Antes de começarmos a falar sobre o post de hoje, nada mais justo começar agradecendo como de costume a você está aqui neste momento acessando meu blog. Espero que possa ter encontrado o que precisa, bem como, esteja gostando do conteúdo publicado, fique a vontade para entrar em contato expressando suas opiniões e demais pensamentos.

Neste novo post da sessão Dica do Mês, vou apresentar um recurso que imagino ser conhecido por muitos ou principalmente pelos profissionais de banco de dados, estou me referindo as Trace Flag ou sinalizador de rastreamento em português.

Você já ouvir falar sobre isso ou já utilizou? Eu imagino que sim pois aqui no meu blog diversos posts e artigos foram publicado ao longo dos últimos anos sobre este tipo de recurso. Hoje mais especificamente vou destacar o uso da Trace Flag 9292, por acaso você já utilizou em algum momento esta trace flag?

Bom independente da sua reposta vamos conhecer um pouco mais sobre ela, sua forma de uso e como poderá nos ajudar a entender ainda mais o funcionamento das estatísticas e seus chamados objetos úteis para análise do plano de execução.

E ai esta curioso para saber um pouco sobre este recurso? Eu estou, sendo assim, vamos em frente, seja bem vindo ao post Dica do Mês – Utilizando a Trace Flag 9292 para exibir objetos estatísticos úteis.

Vamos em frente…..


Introdução

Todos sabemos que as estatísticas desempenham um papel muito importante dentro do SQL Server, como também, sabemos que as estatísticas são usadas pelo otimizador de consultas para gerar o plano de execução para cada consulta.
Então a questão é, podemos verificar qual objeto estatístico é útil para a execução da consulta? Sim, usando o Trace Flag do SQL Server 9292. Este é um do sinalizador de rastreamento que pode ser usado durante a solução de problemas.
Esse sinalizador de rastreamento é usado para obter o relatório sobre objetos de estatísticas considerados como “interessantes” ou “úteis” pelo otimizador de consulta durante a compilação ou recompilação de consulta.

Adicionada ao Microsoft SQL Server 2008 após a instalação do service pack 1 e mantida até as atuais versões, no momento em tomamos a decisão de utilizar a Trace Flag 9292, orientamos o SQL Server a apresentar todos os objetos estatísticos considerados úteis por parte do plano de execução para realizar o processamento e retorno dos dados.

O uso da Trace Flag 9292 dentro de uma sessão ou query específica, nos ajuda a entender e conhecer como as estatísticas e seus elementos podem mudar totalmente a maneira que o plano de execução é idealizado, armazenado e processado.

Através dela podemos obter um relatório sobre as estatíticas para cada objeto envolvido em nossa query, onde estes supostos objetos devem ser considerados úteis, ou melhor dizendo válidos e aplicáveis no decorrer do caminho realizado até a apresentação do resultado.

Esta é uma trace flag que pode ser usada durante a resolução de problemas, onde sua função é apresentar na guia de mensagens do Management Studio, um pequeno cabeçalho contendo informações estatísticas sobre cada componente útil e válido para formas os dados estatísticos de processamento da query. Este cabeçalho é conhecido como Stats header loaded.

Para ativar a trace flag utilize o comando DBCC TraceON (9292) ou DBCC TraceOFF (9292) para desativar, ou se preferir utilize a opção QueryTraceOn em sua respectiva query.

Agora que já conhecemos um pouco mais sobre os conceitos desta trace flag, chegou a hora de colocar as mãos no teclado e conhecer melhor o funcionamento da flag 9292 e de que forma ela poderá impactar o trabalho do Microsoft SQL Server, nos permitindo identificar os objetos úteis processados ou candidatos a processamento por parte do Query Processor em conjunto com Execution Plan.

Preparando o ambiente

Nosso ambiente será bastante simples, basicamente criaremos um banco de dados denominado DBTrace9292, constituído de uma tabela denominada TBTrace9292, para tal utilizaremos o Bloco de Código 1 que apresenta a criação dos respectivos objetos:

— Bloco de Código 1 —
— Criando o Banco de Dados DBTrace9292 —
Create Database DBTrace9292
Go

— Acessando —
Use DBTrace9292
Go

— Criando a Tabela TBTrace9292 —
Create Table TBTrace9292
(Codigo Int Identity(1,1) Primary Key,
Valores Int,
Descricao Varchar(100))
Go

Após a criação dos objetos básicos, nosso próximo passo será a criação de índice nonclustered para coluna Valores que nos permitirá fazer o uso de estatísticas de processamento para esta coluna durante o processo de inserção de dados, conforme apresenta o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —
— Criando o Índice NonClustered IND_TBTrace9292Valores —
Create NonClustered Index IND_TBTrace9292Valores on TBTrace9292(Valores)
Go

— Inserindo uma linha de registro na Tabela TBTrace9292 —
Insert Into TBTrace9292
Values(2000,’pedrogalvaojunior.wordpress.com’)
Go

— Inserindo 1.000 linhas de registros na Tabela TBTrace9292 —
Insert Into TBTrace9292
Values(4000,’pedrogalvaojunior.wordpress.com’)
Go 1000

Note que existir uma pequena mais importante diferença entre os dois Inserts, no primeiro estamos inserindo uma linha de registro na tabela TBTrace9292. No segundo criamos em tempo de execução um pequeno bloco de inserção de linhas, sendo este processado 1.000 vezes, inserindo então 1.000 linhas.

Seguindo em frente, o Bloco de Código 3 será utilizado para criarmos uma Stored Procedure denominada P_PesquisarValores como forma para buscar os respectivos registros armazenados na tabela TBTrace9292. A seguir apresento o Bloco de Código 3:

— Bloco de Código 3 –
— Criando a Stored Procedure P_PesquisarValores —
Create Procedure P_PesquisarValores @Valor int
As
Begin
Select Descricao from TBTrace9292
Where Valores = @Valor
OPTION (RECOMPILE)
End
Go

Se você for como eu, normalmente gosto de fazer uma análise de código antes de colocar em prática no meu ambiente, sendo assim, vamos lá. Analisando de forma superficial a Stored Procedure P_PesquisarValores a princípio não apresenta nada muito especial ou de grande complexidade em seu código, mas sim o uso de opção Recompile que justamente vai orientar o plano de execução a recompilar a  P_PesquisarValores no momento da sua execução, forçando assim que a cada execução um novo plano de execução seja criado em conjunto com uma nova análise estatística e seus demais elementos.

O próximo passo consiste na pesquisa de um dos valores armazenados na tabela TBTrace9292 através da execução e processamento da Stored Procedure P_PesquisarValores. Para este passo vamos utilizar o Bloco de Código 4 a seguir, antes de sua execução recomendo habilitar a apresentação do Plano de Execução Atual no SQL Server Management Studio através do botão Include Actual Execution Plan ou simplesmente através da tecla de atalho CTRL+M.

— Bloco de Código 4 —
— Habilitando as TraceFlags 9292 e 3604 —
DBCC TraceOn(9292,3604,-1)
Go

Dica: Utilize o comando DBCC TraceStatus WITH NO_INFOMSGS para verificar quais Trace Flags estão habilitadas em qual nível de escopo.

— Execuntando a Stored Procedure P_PesquisarValores —
Exec P_PesquisarValores 4000
Go

Pois bem, após a execução do Bloco de Código 4, o Microsoft SQL Server realizou o processamento da nossa Stored Procedure P_PesquisarValores realizando uma busca de todas as linhas de registros que possuem o valor 4.000, onde obrigatoriamente foram retornadas 1.000 linhas de registros.

Até ai nada de novo ou surpreende, o que justamente eu quero mostrar para vocês é o que o Management Studio apresenta na guia Messages após o processamento do Bloco de Código 4, conforme apresenta a Figura 1 abaixo:

Note que o cabeçalho retornado pela Trace Flag 9292 conhecido como Stats header loaded esta apresentando os objetos realmente utilizados para o processamento de nossa query, bem como, os objetos considerados úteis e necessários para criação, compilação e processamento do plano de execução envolvidos na execução, sendo eles:

  • Database: DBTrace9292;
  • Table: TBTrace9292,
  • Index: IND_TBTrace9292Valores, sendo este do tipo Nonclustered;
  • Column: Valores; e
  • EmptyTable: False, representa que a tabela possui linhas de registro.

Perfeito, perfeito, ai esta a prova que a Trace Flag 9292 nos permite identificar de forma simples, coerente e muito intuitiva todos os objetos envolvidos na execução de uma query, stored procedure ou demais elementos que permitem a criação de um plano de execução.

Desta forma, chegamos ao final de mais um post, tendo a sensação de dever cumprido, espero que você tenha gostado, como sempre o Microsoft SQL Server nos surpreende com a sua capacidade e potencialidade de recursos.


Referências

https://thomaslarock.com/2016/06/sql-server-Trace-flags/

https://pedrogalvaojunior.wordpress.com/2016/02/26/02-para-que-serve/

DBCC TRACEOFF (Transact-SQL)

DBCC TRACEON (Transact-SQL)

DBCC TRACESTATUS (Transact-SQL)

EXECUTE (Transact-SQL)

Query Hints (Transact-SQL)

Post Anteriores

https://pedrogalvaojunior.wordpress.com/2017/05/23/conhecendo-e-aplicando-o-uso-de-atualizacao-de-estatisticas-incrementais/

https://pedrogalvaojunior.wordpress.com/2017/04/13/dica-do-mes-microsoft-sql-server-identificando-as-transacoes-que-estao-utilizando-o-transact-log/

https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/

https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Muitas vezes temos dificuldade em entender como um determinado recurso ou funcionalidade esta sendo utilizada pelo Microsoft SQL Server, com também, de que maneira este elemento poderá impactar nosso ambiente.

Neste post foi possível apresentar como a Trace Flag 9292 nos permite identificar quais objetos estão sendo utilizando durante o processamento e execução de uma determinada query. Um recurso de fácil configuração tanto para ser ativado como também desativado a qualquer momento ou necessidade.

Recomendo que você realize diversos testes e validações antes de fazer qualquer tipo de uso de uma trace flag em seu ambiente de produção, isso também se aplica a Trace Flag 9292.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, nos encontramos em breve com mais um post da sessão Dica do Mês.

Valeu….

Short Scripts – Junho 2016


Salve, salve minha gente, boa tarde!!!

Mais uma segunda – feira começando e mais uma semana se iniciando, já passamos da metade do mês de Junho tão aguardado devido ao lançamento do novo Microsoft SQL Server 2016. Caso você tenha perdido alguma informação sobre este lançamento, aproveito para compartilhar aqui um dos diversos posts publicados no meu blog sobre esta nova versão:

Voltando a falar sobre o Short Scripts, esta é uma das sessões mas visitadas do meu blog, onde o objetivo  é compartilhar os scripts existentes em minha biblioteca de códigos  dedicados exclusivamente para o SQL Server. Muitos destes scripts são resultado de pesquisas, posts publicados nos fóruns MSDN e também de respostas encontradas em outros websites de profissionais, administradores de banco de dados, professores e comunidades.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso. Todos os scripts publicados nesta sessão são devidamente testados antes de serem publicados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Caso você não tenha acessado os últimos posts, não perca tempo, utilize os links publicados a seguir:

https://pedrogalvaojunior.wordpress.com/2016/03/22/short-scripts-marco-2016/

https://pedrogalvaojunior.wordpress.com/2015/12/14/short-script-dezembro-2015/

https://pedrogalvaojunior.wordpress.com/2015/10/21/short-scripts-outubro-2015/

https://pedrogalvaojunior.wordpress.com/2015/08/26/short-scripts-agosto-2015/

https://pedrogalvaojunior.wordpress.com/2015/06/03/short-script-junho-2015/

Para esta relação você vai encontrar scripts relacionados aos seguintes assuntos, conceitos, recursos ou funcionalidades:

  • Acesso a banco de dados;
  • Collation;
  • Comando Exec;
  • Comando Union;
  • Comando Union All;
  • Comando Create Table;
  • Comando Alter Table;
  • Common Table Expression;
  • DBCC CheckPrimaryFile;
  • DMV sys.dm_db_index_usage_stats;
  • DMV sys.dm_os_buffer_descriptors;
  • Índices Clustered e NonClustered;
  • Option MaxRecursion;
  • Plano de Execução;
  • Querys consideradas pesadas;
  • Tabela de sistema sys.allocation_units;
  • Tabela de sistema sys.partitions;
  • Tabela de sistema sys.indexes;
  • Tabela e caracteres Unicode; e
  • Recursividade.
A seguir, apresento a relação de short scripts:

— Short Script 1 – DBCC CheckPrimaryFile Verificando se o arquivo realmente é um Primary File —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,0)
Go

 

— Short Script 2 – DBCC CheckPrimaryFile – Retornando o conjunto completo de informações associados ao Primary File que formam a estrutura do respectivo Banco de Dados —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,1)
Go

 

— Short Script 3 – DBCC CheckPrimaryFile – Retornando o nome do banco de dados, versão e collation relacionados ao Primary File —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,2)
Go

— Short Script 4 – DBCC CheckPrimaryFile – Retornando o status, fileid, name e filename associados ao Primary File que formam a estrutura do respectivo Banco de Dados —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,3)
Go
— Short Script 5 – Informações sobre acesso ao Banco de Dados —
WITH agg AS
(SELECT last_user_seek,
                  last_user_scan,
                  last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
)
SELECT last_read = MAX(last_read),
                 last_write = MAX(last_write)
FROM
(SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);
Go

 

— Short Script 6 – Observando a mudança de comportamento após a troca de Collation —

CREATE TABLE [dbo].[Authors]
([id] [INT] NULL,
   [author] [VARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [dateposted] [DATE] NULL) ON [PRIMARY]
GO
INSERT Authors  VALUES (1, ‘Steve’, ‘20160101’),
                                                   (2, ‘STEVE’, ‘20160201’),
                                                   (3, ‘Andy’, ‘20160301’),
                                                   (4, ‘andy’, ‘20160401’)
GO
CREATE PROCEDURE GetAuthors @author VARCHAR(50)
AS
BEGIN
SELECT a.id, a.author FROM dbo.Authors a
WHERE a.author = @author
END
GO
— If I run the procedure with a parameter of ‘Steve’, it returns two rows. I then run this code:
Exec GetAuthors ‘Steve’
Go
ALTER TABLE dbo.Authors
ALTER COLUMN author VARCHAR(50) COLLATE SQL_Latin1_General_CP437_BIN2 NULL
— If I were to execute the stored procedure, what would happen?
Exec GetAuthors ‘Steve’
Go

 

— Short Script 7 – Descobrindo o código Unicode de um caracter ou String —

— Exemplo 1 —
DECLARE @n CHAR(10);
SET @n = N’Abc’;
SELECT UNICODE(@n);
Go
— Exemplo 2 —
DECLARE @n NCHAR(10);
SET @n = N’??????????’;
SELECT UNICODE(@n);
Go

 

— Short Script 8 – Criando CTEs —

— Exemplo 1 – Criando uma simples CTE —
;With Exemplo1(Valor, Nome)
As
(
Select 1, ‘Pedro Galvão’ As Nome
)
Select * from Exemplo1
Go
— Exemplo 2 – Criando uma CTE com Union de Selects —
;With Exemplo2(Valor)
As
( Select 10
Union
Select 50
Union
Select 8
Union
Select 10 + 2
)
Select Valor = (Select Max(valor) From Exemplo2) + (Select Sum(Valor) From Exemplo2)
Go
– Short Script 9 – Criando CTEs com Recursividade —
— Exemplo – Criando uma nova CTE Recursiva concatenando dados —
;With ConcatenarNomes(nome)
AS
( SELECT Nome = CONVERT(Varchar(4000),’Pedro Antonio’)
UNION ALL
SELECT CONVERT(Varchar(4000),nome + ‘ Galvão Junior’) FROM ConcatenarNomes
WHERE LEN(nome) < 30
)
SELECT Nome FROM ConcatenarNomes
Go
— Exemplo 2 – Criando uma CTE com Union + Recursividade – Simulando uma sequência de números pares —
;With CTENumerosPares(Numero)
As
( Select 0 As Numero
Union All
Select Numero + 2 As Numero From CTENumerosPares
Where Numero < 100
)
Select Numero From CTENumerosPares
Go
— Exemplo 3 – Criando uma CTE com Union + Recursividade – Simulando uma sequência de números —
;With CTENumerosSequenciais(Numero)
AS
(   SELECT 1 AS Numero
UNION ALL
SELECT Numero + 1 AS num FROM CTENumerosSequenciais
WHERE Numero < 1000
)
SELECT * FROM CTENumerosSequenciais
OPTION (MAXRECURSION 0)
Go
— Short Script 10 – Obtendo o tamanho de índices Clustered e NonClustered —
SELECT COUNT(*) AS cached_pages_count,
COUNT(*)/128.0000 MB,
name AS BaseTableName,
IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN (SELECT s_obj.name,
s_obj.index_id,
s_obj.allocation_unit_id,
s_obj.OBJECT_ID,
i.name IndexName,
i.type_desc IndexTypeDesc
FROM
(SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id,
allocation_unit_id,
OBJECT_ID
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.TYPE = 1 OR au.TYPE = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id,
allocation_unit_id,
OBJECT_ID
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.TYPE = 2
) AS s_obj LEFT JOIN sys.indexes i
ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
and name not like ‘sys%’
and IndexName <> ‘null’
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
Muito bem, mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância e apresentam um valor e conhecimento do mais alto nível.

Chegamos ao final de mais um post, fique a vontade para compartilhar este conteúdo com seus contatos e redes sociais, contribua também enviando dicas, sugestões, dúvidas, críticas, enfim participe e ajude a melhorar cada vez mais esta sessão.

Mais uma vez obrigado por sua visita, nos encontramos em breve em mais um post da sessão Short Scripts.

Uma ótima semana, abraços.

Impactos na ordenação de colunas em índices Não-Clusterizados, no Microsoft SQL Server 2005, 2008 e R2.

Dica – Impactos na ordenação de colunas em índices Não-Clusterizados, no Microsoft SQL Server 2005, 2008 e R2.


Pessoal, bom dia!

Tudo bem?

———————————————————————————————————————————————

No artigo de hoje, vou apresentar algo que normalmente não se preocupamos quando estamos criando um índice. Estou falando dos impactos que podem ocorrer em nosso ambiente quando utilizamos índices não-clusterizados compostos.

Naturalmente ao criamos um novo índice, estamos adicionando mais um mecanismo de consulta, pesquisa e organização de dados, alocados em uma tabela. Isso quando estamos criando um índice simples, formado por uma única coluna.

Mas todo e qualquer índice pode conter mais de uma coluna, sendo o mesmo, reconhecido como índice composto. Com base, neste cenário, algumas dúvidas podem surgir, dentre elas se o SQL Server pode sofrer alguns impactos quando estamos fazendo uso deste índice dependendo da ordem de criação e utilização das colunas.

Para começar, vou montar nosso ambiente de Exemplo, composto por suas tabelas: Tabela1 e Tabela2, conforme o Código 1 apresentado abaixo:

Código 1 – Criando as Tabelas fisicamente:

CREATE TABLE dbo.Tabela1

(Coluna1 int NOT NULL,

 Coluna2 int NULL,

 Coluna3 int NULL,

 Coluna4 varchar(50) NULL)

GO

 

CREATE TABLE dbo.Tabela2

(Coluna1 int NOT NULL,

 Coluna2 int NULL,

 Coluna3 int NULL,

 Coluna4 varchar(50) NULL)

GO

Note que ambas as tabelas foram criadas inicialmente sem qualquer tipo de índice, inclusive chave primária, sendo este, o próximo passo, apresentado no Código 2:

Código 2 – Adicionando as Chaves Primárias:

ALTER TABLE dbo.Tabela1

 ADD CONSTRAINT PK_Tabela1 PRIMARY KEY CLUSTERED (Coluna1)

GO

 

ALTER TABLE dbo.Tabela2

 ADD CONSTRAINT PK_Tabela2 PRIMARY KEY CLUSTERED (Coluna1)

GO

Acabamos de adicionar em ambas as tabelas uma chave primária do tipo Clusterizada, com isso, estas tabelas possuem neste momento um índice clusterizado, que será utilizado conforme sua necessidade, mas por padrão o SQL Server vai utilizar a chave primária como mecanismo que garanta a unicidade de nossos dados.

Vamos então adicionar algumas linhas de registros em nossas tabelas, conforme apresenta a seguir o Código 3:

Código 3 – Populando as tabelas:

DECLARE @valor INT

SET @valor=1

 

WHILE @valor < 1000

BEGIN 

   INSERT INTO dbo.Tabela1(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,’Valores’)

   INSERT INTO dbo.Tabela2(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,’Valores’)

   SET @valor=@valor+1

END

GO

Agora com nossas tabelas criadas, com suas respectivas chaves primárias e dados, vou começar a preparar o ambiente para demostrar o pode ocorrer em uma tabela que possui índices compostos não-clusterizados, para isso apresenta o Códiogo 4.

Código 4 – Criando um novo Índice na Tabela 1, composto por duas colunas:

CREATE NONCLUSTERED INDEX IND_Tabela1_Coluna2_Coluna3

ON dbo.Tabela1 (Coluna2,Coluna3)

 WITH (STATISTICS_NORECOMPUTE = OFF,

            IGNORE_DUP_KEY = OFF,

            ALLOW_ROW_LOCKS = ON,

            ALLOW_PAGE_LOCKS = ON)

  ON [PRIMARY]

GO

Nosso ambiente já esta praticamente concluído, e sua estrutura encontra-se distribuída da seguinte forma, conforme apresenta a Figura 1:

Figura 1: Representação da estrutura do ambiente, tabelas e índices criados.

Tudo pronto e preparado para começarmos a brincadeira. Para que tentar aproximar e ilustrar ainda mais o comportamento do SQL Server, por parte dos seus processos de execução e informações estatísticas, vou fazer uso de uma Diretiva SET existente no SQL Server, responsável em apresentar estas informações.

Estou me referindo a Diretiva: SET STATISTICS IO ON, responsável em apresentar o retorno de informações estatísticas existentes no SQL Server conforme as transações são processadas pelo Query Processor. Através do Código 5, vou realizar a ativação deste diretiva.

Código 5 – Ativando a exibição de informações sobre Estatísticas com retorno em Tela:

SET STATISTICS IO ON

 

A partir do momento em que ativamos o retorno de informações estatísitcas, a Guia Messagem apresentada no Result Set do SQL Server, vai apresentar informações sobre o processamento estatístico realizado pelo SQL Server e não informações quantitativas sobre o que foi afetado pelo bloco de transações.

 

O próximo passo será utilizar o comando DBCC DropCleanBuffers, responsável em limpar e remover todos os buffers limpos que possam exisitir dentro do Pool do SQL Server. Para isso, vou utilizar o Código 6.

 

Código 6 – Removendo todos os buffers limpos do Pool de buffers do SQL Server:

DBCC DROPCLEANBUFFERS

GO

O objetivo de limparmos o Pool de buffers no SQL Server é apresentar o retorno de informações estatísticas da forma mais precisa de acordo com a sua quantidade de leituras e escritas.

Nossa brincadeira esta ficando boa, vamos então, por um pouco mais de açúcar neste bolo e verificar através do retorno de informações estatísticas o que pode acontecer quando estamos fazendo de um índice não-clusterizado composto, com base, no código 7, apresentado a seguir:

Código 7 – Consultando os Dados na Tabela 1, através da Coluna3:

SELECT * FROM dbo.Tabela1

WHERE Coluna3=99

GO

Este simples Select esta fazendo a consulta de dados com base no Coluna3, limitando o resultado de registros que possuam valor igual 88, mas o que importa neste cenário é o retorno estatístico que o SQL Server nos apresenta na guia Message, consolidado na Tabela 1 apresentada abaixo:

Retorno – Estatísticos – Código 7

Row(s)

Scan Count

Logical Reads

Physical Reads

Read-ahead reads

1

1

6

5

4

 

O próximo passo será fazer a execução de um Select similar ao apresentado no Código 7, mas com uma pequena mudança, ao invés de utilizar a Coluna3 vamos utilizar a Coluna2, conforme apresenta o Código 8:

Código 8 – Consultando os Dados na Tabela 1, através da Coluna2:

–Removendo todos os buffers limpos do Pool de buffers do SQL Server–

DBCC DROPCLEANBUFFERS

GO

 

–Consultando dados–

SELECT * FROM dbo.Tabela1

WHERE Coluna2=99

GO

Retorno – Estatísticos – Código 8

Row(s)

Scan Count

Logical Reads

Physical Reads

Read-ahead reads

1

1

4

4

0

A princípio já podemos observar que existe uma diferença de retorno estatístico de dados na execução do Código 8 em relação ao Código 7, nos indicando uma diferença considerável de leituras Lógicas e Físicas entre eles.

Mas o que pode ter acontecido para ocorrer esta diferença de valores estatísticos entre estes códigos?

Para tentar responder esta pergunta, vou utilizar o Plano de Execução apresentado pelo SQL Server, executando novamente os Código 7 e Código 8.

Após executar novamente o Código 7, nos deparemos com o seguinte Plano de Execução, conforme apresenta a Figura 2:

Figura 2 – Plano de Execução – Código 7.

Podemos notar que o SQL Server realizou uma busca direta de registros fazendo uso de nosso índice Clusterizado que se encontra amarrado a nossa chave primária. O que normalmente nos oferece um ganho de performance.

Após executarmos novamente o Código 8, nos deparemos com o seguinte Plano de Execução, conforme apresenta a Figura 3:

Figura 3 – Plano de Execução – Código 8.

Observando este Plano de Execução, já conseguimos identificar facilmente o que temos de diferença em relação ao Plano de Execução apresentado no Código 7. O Query Optimizer identificou como melhor caminho para processo o Código 8, a utilização de dois operadores para encontrar o dado desejado, neste caso: Index Seek(Non-Clustered) e Key Lookup para depois consolidar estes dados no operador Nested Loops.

Então, parece ser algo de outro mundo esta mudança radical de comportamento do SQL Server, mas não é. Muito pelo contrário é algo bastante simples de se entender, como criamos um índice não-clusterizado composto e a primeira coluna declarada na composição deste índice foi a Coluna 2, o Query Optimizer entende que ao usar esta Coluna que faz parte de um índice e a mesma é a primeira coluna do índice.

Neste cenário, o Query Optimizer faz uso do operador Key Lookup para vasculhar dados no índice Clusterizado que em conjunto com este operador utiliza o operador Nested Loops. Em contra partida o operador Index Seek(Non-Clustered) realiza a pesquisa de dados sobre a coluna utilizada no índice não-clusterizado.

Conclusão

Podemos concluir que o uso de índices em nossas tabelas pode de alguma forma melhor a performance no nosso ambiente, mas por outro lado apresentar um comportamento totalmente diferente em relação ao que entendemos ser o mais indicado. Sendo assim, utilizar muitos índices em uma tabela ou muitas colunas pode acabar impactando em nosso ambiente.

———————————————————————————————————————————————

Mais uma vez quero agradecer a sua visita.

Nos encontramos em breve.