Arquivo

Archive for the ‘SQL Server – Performance’ Category

Azure Premium Storage… Testes com SQLIO

29 de maio de 2015 5 comentários

Fala galera, faz tempo eim?

Bom, semana passada fiz uns testes em um cliente acho que a informação vai ser útil pra vocês.

Esse é um cliente que sofria demais com a péssima performance dos discos do Azure, todo a sua infra-estrutura de banco de dados está em VMs.

Depois de aplicar várias técnicas para minimizar o custo e melhorar a performance das operações de I/O, finalmente o storage premium ficou disponível (só pra País de primeiro mundo, adivinha se tem no Brasil?…) para compra, e conseguimos migrar tudo para o novo storage. Porém ficávamos sempre com aquela dúvida, e ai, vai melhorar mesmo? Quantos %? Qual a diferença de performance dos discos? … Para responder essas nossas dúvidas e ter certeza de que o novo storage está melhor, fiz alguns testes com SQLIO e criei alguns gráficos.

Antes de te mostrar os gráficos, deixa eu mencionar algumas técnicas que usamos que ajudam a minimizar o I/O pra caso você tenha tenha tempo (a.k.a. money) de migrar para o novo storage que é claro, custa mais caro.

Segue uma lista de itens que você pode rever no seu ambiente para otimizar o I/O:

  • · Use minimum Standard Tier A2 for SQL Server VMs.
  • · Keep the storage account and SQL Server VM in the same region.
  • · Disable Azure geo-replication on the storage account.
  • · Avoid using operating system or temporary disks for database storage or logging.
  • · Avoid using Azure data disk caching options (caching policy = None).
  • · Stripe multiple Azure data disks to get increased IO throughput.
  • · Format with documented allocation sizes.
  • · Separate data and log file I/O paths to obtain dedicated IOPs for data and log.
  • · Enable database page compression.
  • · Enable instant file initialization for data files.
  • · Limit or disable autogrow on the database.
  • · Disable autoshrink on the database.
  • · Move all databases to data disks, including system databases.
  • · Move SQL Server error log and trace file directories to data disks.
  • · Apply SQL Server performance fixes.
  • · Setup default locations.
  • · Enable locked pages.
  • · Backup directly to blob storage.

Sorry o inglês, fiquei com preguiça de traduzir… ;P Peguei a lista acima do seguinte link: https://msdn.microsoft.com/en-us/library/azure/dn133149.aspx

Bom, se mesmo assim você não conseguir a performance desejada, então vai precisar migrar para os novos discos com premier. A performance foi realmente surpreendente e fez o que prometeu.

Seguem os gráficos que são bem auto-explicativos, em laranja servidor novo, em azul servidor velho:

clip_image002

IPOS foi lindo de ver… Conseguimos bater 15k por segundo, contra ridículos 1400 que estávamos executando no servidor antigo.

clip_image004

Como podemos ver, a latência ficou entre 1ms e 34ms o que é MUITO melhor que o tempo resposta de 33ms a 182ms nos discos velhos.

clip_image005

MBs por segundo também melhorou demais… Agora consigo ler até 252mbs por segundo, contra 47mb…

Espero que seja útil…

Abs.

Fabiano Amorim

Anúncios

Novo artigo simple-talk–Sort warnings…

20 de março de 2015 2 comentários

Depois de quase 1 ano sem escrever pro Simple-Talk… here we go com um novo artigo !

https://www.simple-talk.com/sql/performance/never-ignore-a-sort-warning-in-sql-server/

Abs.

Fabiano

Arquivos, SQLSat284 e Fórum Tecnico MS Brasil

28 de abril de 2014 3 comentários

Fala galera, os últimos dias foram corridos porém extremamente prazerosos.

Fiz duas apresentações, uma na MS Brasil onde falei sobre Otimizador de Consultas do SQL2014 e outra no SQLSat284 em POA onde falei sobre Tuning do Início ao Fim.

Segue o link para download dos arquivos utilizados nas minhas sessões:

Fórum Tecnico na MS Brasil “Novidades para otimização e performance no SQL Server 2014”: http://1drv.ms/1fmpsuI

SQLSat284 – “Tuning do Início ao Fim”: http://1drv.ms/1m2Hv7z

Algumas imagens dos ppts:

Fórum técnico na Microsoft Brasil:

clip_image002

clip_image004

SQLSat284 em POA:

clip_image006

clip_image008

Pensar set-based, porque é tão difícil?

20 de setembro de 2012 11 comentários

Cá estou, no avião voltando pra casa depois de mais um trabalho de Tuning no SQL Server, processo que demorava 10 horas agora demora 24 minutos, cliente feliz e eu voltando pra casa pra curtir filho lindo e esposa maravilhosa. #SrNimbusRocks

 

Estou lendo um livro do Joe Celko chamado “Thinking in Sets” (pensando em sets) e eu gostaria de falar um pouco sobre isso.

Uma das dificuldades mais comuns para desenvolvedores que querem escrever códigos SQL que tenham boa performance, é abandonar o pensamento de linguagens procedurais (onde você diz tudo o que quer fazer) e começar a pensar em códigos declarativos (onde você apenas diz o que quer).

Desde pequenos (no meu caso com 16 anos) aprendemos que em programação devemos fazer tudo passo a passo. Quem lembra de quando seu professor de lógica de programação (se é que existe lógica nessa bagaça) disse: – “Boa noite turma, hoje vamos escrever nosso primeiro código!” e ele continuou dizendo: “- Quero que você pegue uma folha e escreva todos os passos necessários para trocar um pneu, ou fritar um ovo (no meu caso foi a do fritar o ovo)”. Eu tive essa aulinha no curso técnico de processamento de dados, lembro que escrevi algo parecido com:

 

1.       Pegar o ovo

2.       Abrir o ovo

3.       Colocar na frigideira

4.       Fritar o ovo

5.       Pegar o ovo da frigideira

6.       Colocar no prato

 

Escrevemos estes passos e achamos que estamos abafando… Dai o professor pega o papel e diz… ué, mas vai pegar o ovo da onde? Tá na geladeira? O ovo tá onde? Dai você diz… aaa … é verdade, vou corrigir me da ai… dai você adiciona:

 

1.       Pegar o ovo da geladeira

2.      

 

Dai o professor pega a folha e diz… ué, na sua casa você deixa a geladeira aberta?… Dai você diz… caramba (já começando a ficar irritado) é verdade.

 

1.       Abrir a geladeira

2.       Pegar o ovo da geladeira

3.      

 

Dai o professor pega a folha e diz… ué, e se não tiver mais ovo na geladeira? E se a geladeira estiver quebrada e o ovo estragado, e se a geladeira tiver um cadeado? E se você estiver com a mão ocupada com alguma coisa? Como vai pegar o ovo? … Dai você diz… Professor, na boa, vai a merda! Smile with tongue out E aprende a lição! Tem que prever TUDO, e dizer passo a passo o que deve ser feito.

 

É assim que aprendemos a programar, linha a linha, passo a passo! Porém, quando estamos falando de banco de dados, precisamos pensar que vamos trabalhar com um set, ou seja, um conjunto de linhas (melhor dizendo, um conjunto de elementos). É de responsabilidade do banco de dados decidir qual será a melhor maneira para ler os dados. Ainda que eles serão lidos linha a linha, quem decide a melhor maneira de fazer isso é o banco, não você. Por exemplo, o SQL Server pode optar por fazer uma soma, utilizando um algoritmo de hash, ou um join utilizando um merge e etc…

Lembre-se de que cada instrução (comando SQL) enviada para o banco de dados passa por uma série de validações (parse, binding, optimize…) e isso tem um custo. Por isso cursores e loops são tão ruims em relação a performance. Uma frase sensacional do Celko sobre cursores é a seguinte:

 

“A melhor técnica que você pode utilizar para melhorar a performance de um cursor é não usar cursor.”

Sarcastic smile Sensacional.

 

Quando você vê uma tabela chamada População você acha que nela existem populações, mas na verdade existem pessoas. Uma tabela de Floresta não contem florestas contem árvores… Temos que pensar em um todo, não no individuo. Ao invés de criar uma tabela chamada Abelhas para armazenar dados sobre abelhas, crie uma tabela chamada Colmeia! (é sério esse foi o único coletivo que eu conhecia quando estava escrevendo este artigo kkk Smile with tongue out)

 

Quer exemplos de como nós programadores pensamos em código procedural e não em sets? Vejamos…

 

Se eu disser, nesta mesa eu tenho 8 caixas de ovos (de galinha e codorna) fechadas e de tamanhos variados, quero que você olhe cada caixa e me entregue todas as caixas que contêm a mesma quantidade de ovos.

O que pensamos em fazer?

 

·    Contar quantos ovos tem em cada caixa, separar as caixas que tem a mesma quantidade e me entregar.

 

O que seria mais eficiente?

 

·    Olhar a quantidade exibida na embalagem, separar as caixas que tem a mesma quantidade e me entregar.

 

Reparou na diferença? … Uma solução conta todos os ovos, e a outra usa uma agregação… Uma solução vê os ovos e outra vê as caixas (um todo).

 

Vejamos outro exemplo que usa o caso clássico das tabelas de Pedidos e Itens de Pedidos.

 

Se eu disser, quero uma consulta que retorne todos os pedidos em que a quantidade de itens comprados é igual para todos os itens.

 

Uma alternativa seria escrever uma consulta que varre a tabela de “Itens de Pedidos” e executa uma subquery para verificar se existe algum item de pedido diferente da quantidade do item lido… Algo mais ou menos assim:

 

select a.CodigoPedido from itens_pedidos a

where not exists (select * from itens_pedidos b

                             where a.CodigoPedido = b.CodigoPedido

                                 and a.quantidade <> b.quantidade)

 

Ou seja, se existir algum pedido com quantidade diferente, ele será desconsiderado da consulta. Eu poderia escrever um cursor para responder a consulta, mas não vou fazer isso Smile.

 

Outra forma mais interessante seria o seguinte:

 

select CodigoPedido from itens_pedidos

group by CodigoPedido

having min(quantidade) = max(quantidade)

 

Repararam na diferença?… Eu sei que é mais difícil pensar assim, encontrar soluções deste tipo não é fácil, mas acreditem, é possível.

 

Estudem e entendam as “window functions” no SQL Server, entendam como usar funções de agregação e evitem cursores, sempre.

 

Outra coisa interessante que sempre falo, é em relação a formatação de dados via SQL e ordenação.

 

Não faça formatação no SQL, please, deixe para formatar alguma coisa, na aplicação, banco de dados sofre para incluir uma “mascara” no CNPJ, faça isso na aplicação e não no banco.

Para ordenação eu digo o mesmo. Ordenação é na aplicação e não no banco! Você sabia que a clausula ORDER BY não faz parte do SELECT ? Pois é, no padrão ANSI/ISO da linguagem SQL (structured query language) não existe order by no select, no ISO o order by faz parte da sintaxe de cursores. Os fabricantes de bancos de dados criam uma extensão dessa opção para que você a utilize nos selects. Fica a dica, quase sempre podemos ordenar do lado da aplicação com pouquíssimo trabalho.

 

Fecho o artigo com um ditado Turco bem interessante:

“Não importa o quão longe você foi por uma estrada errada, volte!”

 

Abraços e bom código!

Hypothetical indexes, making it easy to use…

31 de maio de 2012 Deixe um comentário

Hi there, yes, I know, it’s being a while since I wrote something here… So, sorry about that.

Yesterday the guy I’m a big fan (if you already join one of my SQL trainings you know that Smile) Query Optimizer Guru Paul White (blog|twitter) answered a question on twitter (#sqlhelp hash tag) and he mentioned a post I wrote some time ago about hypothetical indexes on SQL Server. That is a very nice feature but unfortunately is not pretty to use because it requires you to use DBCC AUTOPILOT and set AUTOPILOT to ON…

The parameters you have to use are not straightforward to find and may discourage you to use it… I’ve created a procedure to make it a little easier to use.

Originally I created this procedure after a student question about how to make it easier to use hypothetical indexes on SQL Server. So I thought you may like it.

Unfortunately it relies on a CLR stored procedure to SET the AUTOPILOT, but if you don’t mind to use it in a develop environment (which is something normal to do) then you can use it, following is the CLR code, and if you are interested you can download the project code here:

-- CLR Proc
/*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CLR_GetAutoPilotShowPlan
    (
         SqlString SQL,
         out SqlXml PlanXML
    )
    {
        //Prep connection
        SqlConnection cn = new SqlConnection("Context Connection = True");

        //Set command texts
        SqlCommand cmd_SetAutoPilotOn = new SqlCommand("SET AUTOPILOT ON", cn);
        SqlCommand cmd_SetAutoPilotOff = new SqlCommand("SET AUTOPILOT OFF", cn);
        SqlCommand cmd_input = new SqlCommand(SQL.ToString(), cn);

        if (cn.State != ConnectionState.Open)
        {
            cn.Open();
        }

        //Run AutoPilot On
        cmd_SetAutoPilotOn.ExecuteNonQuery();

        //Run input SQL
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        da.SelectCommand = cmd_input;
        ds.Tables.Add(new DataTable("Results"));

        ds.Tables[0].BeginLoadData();
        da.Fill(ds, "Results");
        ds.Tables[0].EndLoadData();

        //Run AutoPilot Off
        cmd_SetAutoPilotOff.ExecuteNonQuery();

        if (cn.State != ConnectionState.Closed)
        {
            cn.Close();
        }

        //Package XML as output
        System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
        //XML is in 1st Col of 1st Row of 1st Table
        xmlDoc.InnerXml = ds.Tables[0].Rows[0][0].ToString();
        System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
        PlanXML = new SqlXml(xnr);
    }
};
*/

Following is the code to compile it on SQL Server and to create another procedure to simulate the hypothetical indexes:

-- Publishing Assembly
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLR_ProjectAutoPilot')
BEGIN
  IF OBJECT_ID('st_CLR_GetAutoPilotShowPlan') IS NOT NULL
    DROP PROC st_CLR_GetAutoPilotShowPlan

  DROP ASSEMBLY CLR_ProjectAutoPilot
END
GO
CREATE ASSEMBLY CLR_ProjectAutoPilot FROM 'C:\ProjectAutoPilot\ProjectAutoPilot\bin\Release\ProjectAutoPilot.dll' WITH PERMISSION_SET = SAFE
GO

CREATE PROCEDURE st_CLR_GetAutoPilotShowPlan (@Query NVarChar(MAX), @ShowPlan XML OUTPUT)
AS
  EXTERNAL NAME CLR_ProjectAutoPilot.StoredProcedures.CLR_GetAutoPilotShowPlan
GO

IF OBJECT_ID('st_TestHipotheticalIndexes', 'p') IS NOT NULL
  DROP PROC dbo.st_TestHipotheticalIndexes
GO
CREATE PROCEDURE dbo.st_TestHipotheticalIndexes (@SQLIndex NVarChar(MAX), @Query NVarChar(MAX))
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRAN
    DECLARE @CreateIndexCommand NVarChar(MAX),
            @IndexName NVarChar(MAX),
            @TableName NVarChar(MAX),
            @SQLIndexTMP NVarChar(MAX),
            @SQLDropIndex NVarChar(MAX),
            @SQLDbccAutoPilot NVarChar(MAX),
            @i Int,
            @QuantityIndex Int,
            @Xml XML

    IF SubString(@SQLIndex, LEN(@SQLIndex), 1) <> ';'
    BEGIN
      RAISERROR ('Last caracter in the index should be ;', -- Message text.
                 16, -- Severity.
                 1 -- State.
                 );
    END

    SET @SQLDropIndex = '';
    SET @QuantityIndex = LEN(@SQLIndex) - LEN(REPLACE(@SQLIndex, ';', ''))
    SELECT @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))

    SET @i = 0
    WHILE @i < @QuantityIndex
    BEGIN
      SET @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))
      SET @CreateIndexCommand = SUBSTRING(@SQLIndexTMP, 0, CharIndex(' ON ',@SQLIndexTMP))
      SET @IndexName = REVERSE(SubString(REVERSE(@CreateIndexCommand), 0, CharIndex(' ', REVERSE(@CreateIndexCommand))))
      SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex(' ', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', '')))
      IF ISNULL(@TableName,'') = ''
        SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex('(', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', '')))
      SET @SQLIndex = REPLACE(@SQLIndex, @SQLIndexTMP + ';', '')
      --SELECT @SQLIndex, @SQLIndexTMP, @CreateIndexCommand, @TableName, @IndexName

      -- Creating hypotetical index
      IF CharIndex('WITH STATISTICS_ONLY =', @SQLIndexTMP) = 0
      BEGIN
        SET @SQLIndexTMP = @SQLIndexTMP + ' WITH STATISTICS_ONLY = -1'
      END
      -- PRINT @SQLIndexTMP
      EXEC (@SQLIndexTMP)

      -- Creating query to drop the hypotetical index
      SELECT @SQLDropIndex = @SQLDropIndex + 'DROP INDEX ' + @TableName + '.' + @IndexName + '; '
      -- PRINT @SQLDropIndex

      -- Executing DBCC AUTOPILOT
      SET @SQLDbccAutoPilot = 'DBCC AUTOPILOT (0, ' + 
                                               CONVERT(VarChar, DB_ID()) + ', '+ 
                                               CONVERT(VarChar, OBJECT_ID(@TableName),0) + ', ' +
                                               CONVERT(VarChar, INDEXPROPERTY(OBJECT_ID(@TableName), @IndexName, 'IndexID')) + ')'

      EXEC (@SQLDbccAutoPilot)
      --PRINT @SQLDbccAutoPilot

      SET @i = @i + 1
    END

    -- Executing Query
    DECLARE @PlanXML xml

    EXEC st_CLR_GetAutoPilotShowPlan @Query = @Query, 
                                     @ShowPlan = @PlanXML OUT
    SELECT @PlanXML

    -- Droping the indexes
    EXEC (@SQLDropIndex)

    COMMIT TRAN
  END TRY
  BEGIN CATCH
    ROLLBACK TRAN
    -- Execute error retrieval routine.
    SELECT ERROR_NUMBER()    AS ErrorNumber,
           ERROR_SEVERITY()  AS ErrorSeverity,
           ERROR_STATE()     AS ErrorState,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE()      AS ErrorLine,
           ERROR_MESSAGE()   AS ErrorMessage;
  END CATCH;
END
GO

The proc st_TestHipotheticalIndexes expect two input parameters:

  • @SQLIndex: Here you should specify the command to create the index you and to try (the hypothetical indexes), if you want to try more than one index, just call it separating many “create index” commands by coma. For instance:
@SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);',
  • @Query: Here you should write the query you want to try.

Here is a sample of how to call it on :

-- Sample 1
EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix ON Order_Details(Quantity);', 
@Query = 'SELECT * FROM Order_Details WHERE Quantity < 1'

The results of the query above is an XML with the query plan considering the suggested index:

clip_image001

Another sample:

— Sample 2

-- Sample 2
EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);', 
                                    @Query = 'SELECT p.ProductName, p.UnitPrice, s.CompanyName, s.Country, od.quantity FROM Products as P INNER JOIN Suppliers as S ON P.SupplierID = S.SupplierID INNER JOIN order_details as od ON p.productID = od.productid WHERE P.CategoryID in (1,2,3) AND P.Unitprice < 20 AND S.Country = ''uk'' AND od.Quantity < 90'

clip_image002

Yep, now it is easier… Let me know what do you think and please don’t mind on the crappy code Smile  in the proc to get the tablename, indexname…

Have fun…

O que é melhor? Um Index Seek ou Clustered Index Scan?

9 de setembro de 2008 8 comentários

O que você acha, Um Index Seek é melhor ou pior que um Index Scan?

A reposta é depende, existem casos onde com certeza fazer um Index Scan é a melhor opção. Vamos a um exemplo que criei.

— Script para criação da base de teste.

USE master

GO

IF DB_ID(‘INSIDE’) IS NULL

CREATE DATABASE INSIDE

GO

USE INSIDE

GO

ALTER DATABASE INSIDE SET AUTO_UPDATE_STATISTICS ON

GO

IF OBJECT_ID(‘TabTeste’) IS NOT NULL

DROP TABLE TabTeste

GO

CREATE TABLE TabTeste (ID INT IDENTITY(1,1) PRIMARY KEY,

Nome VarChar(200),

Nome2 VarChar(250) DEFAULT NEWID())

GO

Abaixo vamos alimentar a tabela TabTeste que criamos com o código acima, irei incluir 10000 mil registros com o valor A e apenas 10 com o valor B.

SET NOCOUNT ON

DECLARE @i Int

SET @i = 0

— Vamos inserir 10000 registros com o valor A

WHILE @i < 10000

BEGIN

INSERT INTO TabTeste (nome) VALUES(‘A’)

SET @i = @i + 1;

END

SET @i = 0;

— Agora vamos inserir apenas 10 registros com o valor B

WHILE @i < 10

BEGIN

INSERT INTO TabTeste (nome) VALUES(‘B’)

SET @i = @i + 1;

END

/*

Ao efetuar um select na tabela TabTeste o SQL só tem uma opção de acesso ao seus dados

fazer um Clustered Index Scan, ou seja, ler toda a sua tabela e verificar quais as linhas

onde o nome é igual a ‘A’ e então retornar somente estas linhas.

*/

SELECT * FROM TabTeste

WHERE Nome = ‘A’

GO

clip_image002

/*

Mas e se tivermos um indice na coluna Nome?

Neste caso o SQL terá 2 opções de acesso aos dados da tabela.

1º – Optar por ler os dados dados utilizando o indice gerando assim um Index Seek mais um

bookmark lookup já que estou querendo todas as colunas da tabela(*).

2º – Fazer o Clustered Index Scan.

*/

— Vamos criar o indice e ver como ele se comporta.

CREATE NONCLUSTERED INDEX ix_Nome ON TabTeste(Nome)

GO

/*

Um mesmo assim ele não usou o indice? Ok isso porque não vale a pena fazer o bookmark

para saber porque ele optou em não usar o indice leia o artigo "Indice / Lookup / Recompile"

http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!230.entry

Na consulta abaixo o SQL verificar quantas páginas serão necessárias

*/

SELECT * FROM TabTeste

WHERE Nome = ‘A’

GO

clip_image004

/*

Se eu fizer um select passando o valor ‘B’ o SQL irá validar quantas linhas meu select

irá retornar e verificar se vale a pena fazer o bookmark ou clustered scan para

retornar os dados da minha coluna Nome2.

*/

SELECT * FROM TabTeste

WHERE Nome = ‘B’

clip_image006

/*

Agora ele fez um Index Seek

Vamos entender o que aconteceu aqui:

O SQL foi nas estatisticas e verificou que seriam retornadas aproximadamente 10 linhas

na consulta portanto valia a pena fazer o bookmark para o indice cluster.

*/

DBCC SHOW_STATISTICS (TabTeste, ix_Nome)

clip_image008

/*

Mas e se as estatisticas estiverem desatualizadas o que vai acontecer?

Pois é, dai o SQL Server irá fazer pensar que vale a pena fazer um bookmark

pois serão retornados poucos dados mas isso não vai acontecer porque irei incluir

mais dados na tabela com o valor ‘B’. Vamos aos testes.

*/

— Vamos desabilitar o AUTO_UPDATE_STATISTICS para fazer as estatisticas

— ficarem desatualizadas

ALTER DATABASE INSIDE SET AUTO_UPDATE_STATISTICS OFF

GO

— Vamos inserir mais 1000 mil registros com o valor B

DECLARE @i Int

SET @i = 0

WHILE @i < 100

BEGIN

INSERT INTO TabTeste (nome) VALUES(‘B’)

SET @i = @i + 1;

END

GO

/*

O que acontece abaixo é que o SQL continua achando que o valor ‘B’ irá retornar apenas

10 linhas, vamos rodar o DBCC SHOW_STATISTICS para confirmarmos.

*/

DBCC SHOW_STATISTICS (TabTeste, ix_Nome)

clip_image008[1]

/*

Nas consultas abaixo fiz um select igual ao anterior, e outro select forcando o uso

do clustered index scan repare que o número de IOs necessários para o select com o

Index Seek é BEM maior que o Clustered Scan.

*/

SET STATISTICS IO ON

SELECT * FROM TabTeste

WHERE Nome = ‘B’

— Table ‘TabTeste’. Scan count 1, logical reads 222, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

clip_image010

GO

SELECT * FROM TabTeste WITH(INDEX=0)

WHERE Nome = ‘B’

–Table ‘TabTeste’. Scan count 1, logical reads 73, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO OFF

clip_image012

/*

Podemos terminar com 2 conclusões.

1º Nem sempre um Index Seek será melhor que um Index Scan.

2º Atualize as estatisticas de suas tabelas.

*/

10 pontos que devem ser observados quanto a performance de uma consulta Parte – 4

8 de agosto de 2008 3 comentários

6.       Substituir o uso de CURSOR pelo comando WHILE + tabelas temporárias ou Variáveis do tipo table.

DECLARE @VendorID Int,

        @Name     VarChar(80)

 

DECLARE Cur_Vendor CURSOR FOR

 SELECT VendorID, Name

   FROM Purchasing.Vendor

 

OPEN Cur_Vendor

 

FETCH NEXT FROM Cur_Vendor

 INTO @VendorID, @Name

 

WHILE @@FETCH_STATUS = 0

BEGIN

       PRINT @Name

 

             FETCH NEXT FROM Cur_Vendor

    INTO @VendorID, @Name

END

 

CLOSE Cur_Vendor

DEALLOCATE Cur_Vendor

GO

 

DECLARE @ROWID Int,

        @Name  VarChar(80)

 

SET @ROWID = 0;

 

SELECT TOP 1

       @ROWID = VendorID,

       @Name  = Name     

  FROM Purchasing.Vendor

 WHERE VendorID > @ROWID

 ORDER BY VendorID

 

WHILE @@ROWCOUNT > 0

BEGIN

  PRINT @Name

 

  SELECT TOP 1

         @ROWID = VendorID,

         @Name  = Name     

    FROM Purchasing.Vendor

   WHERE VendorID > @ROWID

   ORDER BY VendorID

END

GO

 

7.       Verificar se as variáveis no WHERE são do mesmo DataType que a coluna da tabela.

DECLARE @TMP TABLE(Nome VarChar(80) PRIMARY KEY)

 

DECLARE @Nome NVarChar(80)

SET @Nome = ‘Teste’

 

— Gera FULL SCAN

SELECT * FROM @TMP

 WHERE Nome = @Nome

 

GO

 

DECLARE @TMP TABLE(Nome VarChar(80) PRIMARY KEY)

 

DECLARE @Nome VarChar(80)

SET @Nome = ‘Teste’

 

— Gera SEEK

SELECT * FROM @TMP

 WHERE Nome = @Nome

 

8.       Verificar se existe uso de variáveis do tipo Table para grande volume de dados, pois isso pode causar problema de performance já que variáveis do tipo table não usam proveito de paralelismo e não criam estatísticas com os dados da tabela.

9.       Verificar se é possível usar o conceito de Hash Index usando o CheckSum para gerar o número HASH, Caso existam colunas com valores muito grandes as vezes compensa usar o CheckSum para gerar o Hash e depois indexar a coluna hash.

— Create a checksum index.

SET ARITHABORT ON;

USE AdventureWorks;

GO

ALTER TABLE Production.Product

ADD cs_Pname AS CHECKSUM(Name);

GO

CREATE INDEX Pname_index ON Production.Product (cs_Pname);

GO

/*Use the index in a SELECT query. Add a second search

condition to catch stray cases where checksums match,

but the values are not the same.*/

SELECT *

FROM Production.Product

WHERE CHECKSUM(N’Bearing Ball’) = cs_Pname

AND Name = N’Bearing Ball’;

GO

10.       Evite usar a clausula IN.

IF OBJECT_ID(‘tempdb.dbo.#TMP’) IS NOT NULL

BEGIN

  DROP TABLE #TMP

END

 

CREATE TABLE #TMP (ID Int IDENTITY(1,1) PRIMARY KEY)

GO

 

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

GO

 

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

 

SELECT * FROM #TMP

 WHERE ID IN (1,2,3,4,5)

 

 

/*

Coluna argument

 

OBJECT:([tempdb].[dbo].[#TMP]),

       SEEK:(

               [tempdb].[dbo].[#TMP].[ID]=(1) OR

               [tempdb].[dbo].[#TMP].[ID]=(2) OR

               [tempdb].[dbo].[#TMP].[ID]=(3) OR

               [tempdb].[dbo].[#TMP].[ID]=(4) OR

               [tempdb].[dbo].[#TMP].[ID]=(5)

            )

       ORDERED FORWARD

 

Repare no argument que o SQL gerou, ou seja irá acessar uma vez a tabela #TMP para cada opção do IN

podemos confirmar isso no Scan Count do statistics io

 

— IO

Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

*/

GO

 

SELECT * FROM #TMP

 WHERE ID BETWEEN 1 AND 5

/*

OBJECT:([tempdb].[dbo].[#TMP]),

       SEEK:(

              [tempdb].[dbo].[#TMP].[ID] >= (1) AND

              [tempdb].[dbo].[#TMP].[ID] <= (5)

            )

       ORDERED FORWARD

 

Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Repare na diferença de IO.

*/

 

Espero que tenham gostado, eu adorei J, gostei tanto que vou começar a escrever uma série de artigos sobre o Query Optimizer, fiquem de olho…