Arquivo

Archive for the ‘SQL Server – Performance’ Category

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

8 de agosto de 2008 Deixe um comentário

Seguindo a série de boas práticas em relação a performance…

 

4.      É muito comum durante o desenvolvimento de um código SQL necessitarmos zerar o valor das variáveis que serão utilizadas no código.

Uma dica em relação a performance é que o comando SELECT é mais rápido do que o SET, porém está regra só se aplica quando podemos substituir um bloco de SET por um SELECT por ex:

— Executa um loop zerando o valor de 10 variáveis utilizando SET

— Gere o plano de execução e repare que no plano de execução que no Loop o SQL gera um SELECT para cada comando SET.

DECLARE @i Int, @Test1 int, @Start datetime

DECLARE @V1 Char(6),

        @V2 Char(6),

        @V3 Char(6),

        @V4 Char(6),

        @V5 Char(6),

        @V6 Char(6),

        @V7 Char(6),

        @V8 Char(6),

        @V9 Char(6),

        @V10 Char(6);

 

SET @Test1 = 0

SET @i = 0

SET @Start = GetDate()

WHILE @i < 50000

BEGIN

  SET @V1 =

  SET @V2 =

  SET @V3 =

  SET @V4 =

  SET @V5 =

  SET @V6 =

  SET @V7 =

  SET @V8 =

  SET @V9 =

  SET @V10 =

      SET @i = @i + 1                  

END                               

SET @Test1 = DATEDIFF(ms, @Start, GetDate())

SELECT @test1

 

GO

— Executa um loop zerando o valor de 10 variáveis utilizando SELECT

— Diferente do primeiro plano de execução o SQL gerou apenas 1 instrução para setar os valores para as 10 variáveis.

DECLARE @i Int, @Test1 int, @Start datetime

DECLARE @V1 Char(6),

        @V2 Char(6),

        @V3 Char(6),

        @V4 Char(6),

        @V5 Char(6),

        @V6 Char(6),

        @V7 Char(6),

        @V8 Char(6),

        @V9 Char(6),

        @V10 Char(6);

 

SET @Test1 = 0

SET @i = 0

SET @Start = GetDate()

WHILE @i < 50000

BEGIN

SELECT @V1 = ,

       @V2 = ,

       @V3 = ,

       @V4 = ,

       @V5 = ,

       @V6 = ,

       @V7 = ,

       @V8 = ,

       @V9 = ,

       @V10 = ,

       @i = @i + 1;

END                               

SET @Test1 = DATEDIFF(ms, @Start, GetDate())

SELECT @test1

 

5.       Pequenas dicas para procedures..

a.       Sempre que uma procedure é executada o server envia para o client o número de linhas afetadas pela procedure, normalmente esta informação não é necessária. Desabilitando este comportamento poderemos reduzir o trafego gerado pelo Server e o Client. Portanto sempre que possível inclua o SET NOCOUNT ON no início de suas procedures. Pode ser que isso não gere muita diferença em uma proc que efetua 1 insert mas quando estamos falando de um loop que efetua 1000000 de inserts daí com certeza vai ser um grade ganho de trafego desnecessário que estaria rolando na rede.

b.      Só use a opção WITH ENCRYPTION e WITH RECOMPILE caso seja realmente necessário. Lembre-se de que existem programas que conseguem descriptogravar um proc que está criptografada no banco.

c.       Não inicie o nome de suas procedure com sp… este prefixo é reservado para procedures do sistema, sempre que você executa uma procedure que inicia com sp… o SQL irá procurar a proc no banco Master e se ela não estiver lá então ele irá resolver o nome da proc no banco atual. Evite este passo adicional simplesmente renomeando as procedures para por exemplo usp ou st.

d.      Caso tenha que rodar algum código TSQL no meio da proc evite utilizar o EXEC ao invés disso use a sp_ExecuteSQL pois ao contrário do EXEC a sp_ExecuteSQL irá compilar o SQL para gerar um plano de execução para sua consulta, o quer dizer que caso o plano já tenha sido gerado na próxima execução do código ele irá utilizar o plano que ficou em cachê, evitando a recompilação do código a cada execução.

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

27 de junho de 2008 3 comentários

3.       Sempre que possível substituir condições com OR por UNION ALL, por ex:

SET NOCOUNT ON

GO

IF OBJECT_ID(‘Teste ‘) IS NOT NULL

  DROP TABLE Teste

GO

CREATE TABLE Teste (ID        Int Identity(1,1),

                    CPF       Char(11),

                    Nome      VarChar(200),

                    Sobrenome VarChar(200),

                    Endereco  VarChar(200),

                    Bairro    VarChar(200),

                    Cidade    VarChar(200))

GO

— Inclui 1000 mil de linhas na tabela

INSERT INTO Teste(CPF, Nome, SobreNome, Endereco, Bairro, Cidade)

            VALUES(‘11111111111’, NEWID(), ‘Neves Amorim’, NEWID(), NEWID(), NEWID())

GO 1000

 

CREATE CLUSTERED INDEX ix_ID ON Teste(ID)

GO

CREATE INDEX ix_Nome ON Teste(Nome)

GO

 

/*

  Seleciona todos os registros onde ID = 10 ou então o Nome inicia com 38.

  Esta consulta irá gerar um Scan na tabela pois o OR impede que o SQL use

  o ix_ID ou o ix_Nome.

*/

SELECT *

  FROM Teste

 WHERE ID = 10

    OR Nome Like ‘38%’

GO

 

/*

  A instrução acima deve ser trocada por a consulta abaixo que utiliza o UNION ALL

*/

SELECT Tab.*

  FROM (SELECT *

          FROM Teste

         WHERE ID = 10

         UNION ALL

        SELECT *

          FROM Teste

         WHERE Nome Like ‘38%’) AS Tab

 

Obs.: Sempre que possível utilize “UNION ALL” ao invés de “UNION” pois o “UNION” gera um distinct que geralmente gera um order by o que irá gerar um custo desnecessário comparado a concatenação do “UNION ALL”.

 

Continua…

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

27 de junho de 2008 2 comentários

Performance de querys é sem dúvida uma das maiores causadoras de dor de cabeça em DBAs e afins(J), se vocês já leram algum post neste blog devem ter percebido que gosto muito deste assunto, trato diariamente com problemas deste tipo e tem alguns pontos que acho importantes de serem analisados quando falamos em análise de consultas, vou tentar explicar melhor abaixo.

 

Primeiro vou falar um pouco da empresa onde trabalho a, CNP-M, graças a Deus somos uma empresa certificada MPS.BR pois os processos que foram implantados nos ajudam a diminuir e MUITO possíveis problemas de performance que teríamos e que não deixamos chegar nos clientes pois param no processo de validação. Os pontos que vou mencionar abaixo servem como base para procurar possíveis problemas de performance, bom chega de conversa mole e vamos para a melhor parte(você já sabe, TSQL).

 

Todas as consultas abaixo foram executadas no banco AdventureWorks / SQL Server 2005.

 

1.       Sempre verificar o plano de execução de cada select existente no código SQL, e analisar o uso ou não uso dos índices de cada tabela pertencente a query.

2.       Verificar o uso de Functions. Caso exista alguma function envolvida no SQL analise bem a consulta e verifique se é possível alterar a consulta para fazer um join com a própria tabela ou então até mesmo tabelas temporárias, vamos ver alguns exemplos para ficar mais fácil de entender o que estou querendo dizer.

IF OBJECT_ID(‘VendaPorCliente’) IS NOT NULL

  DROP FUNCTION dbo.VendaPorCliente

 

GO

CREATE FUNCTION dbo.VendaPorCliente(@CustomerID Int)

RETURNS Decimal(18,2)

AS

BEGIN

  DECLARE @Total Decimal(18,2)

 

  SELECT @Total = SUM(OrderQty * UnitPrice)

    FROM AdventureWorks.Sales.SalesOrderHeader a

   INNER JOIN AdventureWorks.Sales.SalesOrderDetail b

      ON a.SalesOrderID = b.SalesOrderID

   WHERE a.CustomerID = @CustomerID

 

  RETURN @Total

END

GO

/*

                Seleciona o total de venda por Customer

   Aqui temos um problema, pois para cada linha na tabela Customer o SQL Server irá

   executar a Function VendaPorCliente, ou seja se minha tabela Customer tiver

   50000 linhas o SQL irá acessar as tabelas de header e Detail 50000 vezes.

*/

SELECT AccountNumber, dbo.VendaPorCliente(CustomerID) as Total

  FROM AdventureWorks.Sales.Customer

 

/*

   Para resolver o problema da consuta acima poderiamos fazer o seguinte

   Criar uma nova function do tipo "multi-statement table-valued"

*/

 

IF OBJECT_ID(‘VendaTotalClientes’) IS NOT NULL

  DROP FUNCTION dbo.VendaTotalClientes

 

GO

CREATE FUNCTION dbo.VendaTotalClientes()

RETURNS @tb_result TABLE

(

  CustomerID Int,

  Total      Decimal(18,2),

  PRIMARY KEY(CustomerID)

)

AS

BEGIN

  INSERT INTO @tb_result 

  SELECT a.CustomerID, SUM(OrderQty * UnitPrice) Total

    FROM AdventureWorks.Sales.SalesOrderHeader a

   INNER JOIN AdventureWorks.Sales.SalesOrderDetail b

      ON a.SalesOrderID = b.SalesOrderID

   GROUP BY a.CustomerID

 

  RETURN

END

GO

/*

               Seleciona o total de venda por Customer

   Desta vez ao invés de acessar a function para cada linha da tabela Customer

   o SQL Server irá ler os dados das tabelas Header e Detail apenas 1 vez pois a function

   irá retornar todos os dados em uma tabela.

*/

SELECT AccountNumber, b.Total

  FROM AdventureWorks.Sales.Customer a

 INNER JOIN dbo.VendaTotalClientes() b

    ON a.CustomerID = b.CustomerID

GO

/*

   Agora chegamos onde eu queria!, imagine que eu queria retornar o total de venda apenas do

   Customer ‘AW00000001’, eu iriá escrever o seguinte select.

   O que acontece abaixo é que o SQL irá primeiro retornar todos os dados da function, ou seja,

   todas as vendas por customer e depois aplicar o filtro de AccountNumber = ‘AW00000001’

*/

SELECT AccountNumber, b.Total

  FROM AdventureWorks.Sales.Customer a

 INNER JOIN dbo.VendaTotalClientes() b

    ON a.CustomerID = b.CustomerID

 WHERE a.AccountNumber = ‘AW00000001’

GO

/*

  O ideal neste caso seria usar:

*/

SELECT AccountNumber, dbo.VendaPorCliente(CustomerID) as Total

  FROM AdventureWorks.Sales.Customer

 WHERE AccountNumber = ‘AW00000001’

GO

/*

  Ou então simplesmente não utilizar a function e fazer o select direto nas tabelas

*/

SELECT a.AccountNumber, SUM(OrderQty * UnitPrice) AS Total

  FROM AdventureWorks.Sales.Customer a

 INNER JOIN  AdventureWorks.Sales.SalesOrderHeader b

    ON a.CustomerID = b.CustomerID

 INNER JOIN AdventureWorks.Sales.SalesOrderDetail c

    ON b.SalesOrderID = c.SalesOrderID

 WHERE a.AccountNumber = ‘AW00000001’

 GROUP BY a.AccountNumber

GO

 

Obs.: Atenção, o comando SET STATISTICS IO ON não leva em consideração as leituras efetuadas nas suas functions, o que certas vezes acaba gerando uma má interpretação do comando, portanto fique ligado nisso.

 

Continua…

Performance – Caso Real – Parte2

17 de abril de 2008 Deixe um comentário

O segundo problema foi mais interessante e vou explicar porque, normalmente focamos nossas forças em otimizar o SELECTs, mas neste caso o maior problema estava em um DELETE, operações de DELETE são muito pesadas para o banco de dados, mais ainda caso a tabela que esta sendo deletada tenha uma primary key que é referenciada em outras tabelas(foreign keys). Antes de efetuar o DELETE o SQL terá que consultar cada tabela referenciada pra verificar se o DELETE não está violando nenhuma constraint. No meu caso a tabela que estava sendo apagada tinha relacionamento com outras 15 tabelas, portanto antes de apagar os dados o SQL tinha que ler as outras 15 tabelas para verificar se o DELETE era valido. Para piorar, em 3 dessas tabelas referenciadas a coluna da Foreign Key não estava indexada conclusão, Clustered Scan. Para resolver o problema bastou criar os devidos índices nas colunas da Foreign Key e pronto.

 

Para baixar o arquivo com um exemplo clique no link abaixo,  

 http://cid-52eff7477e74caa6.skydrive.live.com/embedrowdetail.aspx/Publica/DELETEComForeignKeyssemIndice.sql

Performance – Caso Real – Parte1

17 de abril de 2008 Deixe um comentário

Esta semana tivemos um problema de performance em uma rotina que estava funcionando normalmente e começou a apresentar lentidão chegando a exibir uma mensagem de timeout para o usuário. Comecei a analisar as query da procedure e encontrei o 1º problema. Segue um exemplo:

 

USE TempDB

GO

 

CREATE TABLE TesteA(ID_TesteA Int IDENTITY(1,1) PRIMARY KEY,

                    Data      DateTime DEFAULT GETDATE())

 

CREATE TABLE TesteB(ID_TesteB   Int IDENTITY(1,1),

                    Nome        VarChar(200) DEFAULT NEWID(),

                    ID_TesteA   Int REFERENCES TesteA(ID_TesteA),

                    Primary Key(ID_TesteB, Nome))

GO

 

SET NOCOUNT ON

DECLARE @I INT

— Insere 5000 linhas nas tabelas

SET @I = 0

WHILE @I < 5000

BEGIN

  INSERT INTO TesteA DEFAULT VALUES

  INSERT INTO TesteB(ID_TesteA) VALUES(@@IDENTITY)

  SET @I = @I + 1;

END

GO

 

— Por causa de statisticas desatualizadas e o TOP + Order By DESC

— o SQL estava fazendo Scan na tabela TesteA

— No exemplo abaixo ele não irá fazer Scan, porem irá gerar um Sort

— por causa do TesteA.Data DESC. Como a tabela não está ordenada por Data e sim por

— ID_TesteA(Primary key e indice cluster) ele tem que ordenar a tabela por Data.

SELECT TOP 1 TesteA.Data

    FROM TesteB

   INNER JOIN TesteA

      ON TesteB.ID_TesteA = TesteA.ID_TesteA

   WHERE TesteB.ID_TesteB = 1

   ORDER BY TesteA.Data DESC

GO

 

— A solução encontrada foi alterar a consulta acima para esta abaixo

— A idéia é pegar o ultimo registro incluido na tabela, portanto simplismente

— troquei o TOP 1 + Desc por MAX. No exemplo abaixo ele geou um Stream Aggregate na coluna

— TesteA.Data ordenando o resultado por TesteA.ID que é o indice Cluster e já está ordedado

— fisicamente.

SELECT MAX(TesteA.Data)

    FROM TesteB

   INNER JOIN TesteA

      ON TesteB.ID_TesteA = TesteA.ID_TesteA

   WHERE TesteB.ID_TesteB = 1

 

 

/*

O Edvaldo que trabalha junto comigo fez uma observação importante:

 

Cuidado em utilizar funções de agregação (MAX, MIN, COUNT…) com EXISTS pois sempre vai existir valor.

Exemplo:

IF EXISTS (SELECT MAX(campo) FROM tabela WHERE condição que não encontrou nenhum registro)

BEGIN

   

END

ELSE

BEGIN

 

END

Sempre vai passar no IF e nunca no ELSE.

*/

 

 

Continua…