Como o SQL Server executa um comando ou procedure

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Nesse artigo, o Agnaldo mostra como o SQL Server executa um comando ou procedure

Como o SQL Server executa um comando ou procedure

Mas, como o SQL Server executa um comando (ou procedure)?

O processo de execução está dividido em quatro partes:

  • Parse - nessa fase o SQL Server verifica se há erros de sintaxe. Se tudo estiver OK, o comando ou procedure é transformado em uma estrutura capaz de ser lida pelo compilador;
  • Normalização - agora o SQL Server procura por inconsistências nos nomes de objetos ou colunas de tabelas e verifica se o comando faz sentido, por exemplo evitando que façamos coisas do tipo:
    • executar um select;
    • executar uma view;
    • deletar uma procedure;
  • Compilação - é o passo que gera o plano de execução, levando em conta a estrutura das tabelas, existência de índices ou não, se há views envolvidas. Finalmente, armazena o plano de execução criado no cache;
  • Otimização - cada comando que é executado consome disco, memória e processador. O SQL Server analisa esses consumos e usa o plano de execução que tem o MENOR CUSTO.

Para ilustrar como é o processo, criamos um database, uma tabela de produtos e a populamos usando o script abaixo:

CREATE DATABASE EXEMPLO_EXECUCAO
GO
USE EXEMPLO_EXECUCAO

CREATE TABLE PRODUTO
(
      COD INT IDENTITY,
     
NOME VARCHAR(50),
      TIPO VARCHAR(10)
)

INSERT INTO PRODUTO VALUES (‘‘ALIMENTO 1‘‘, ‘‘ALIMENTO‘‘)

DECLARE @X INT
SET @X = 1
WHILE @X <= 5000
BEGIN
      INSERT INTO PRODUTO VALUES (‘‘FERRAMENTA ‘‘ + CONVERT(VARCHAR, @X), ‘‘FERRAMENTA‘‘)
      SET @X = @X + 1
END

INSERT INTO PRODUTO VALUES (‘‘ALIMENTO 2‘‘, ‘‘ALIMENTO‘‘)

Agora, criamos um índice do tipo non-clustered para a tabela, onde a chave é o campo TIPO:

CREATE NONCLUSTERED INDEX INDICE_PRODUTO_TIPO ON PRODUTO(TIPO)

Fazemos os seguintes selects e analisamos os planos de execução gerados (o comando DBCC abaixo é para esvaziar o cache de comandos):

DBCC FREEPROCCACHE
SELECT * FROM PRODUTO WHERE TIPO = ‘‘ALIMENTO‘‘
SELECT * FROM PRODUTO WHERE TIPO = ‘‘FERRAMENTA‘‘

planos.gif

Para o primeiro select, o SQL Server identifica que deverão ser retornados somente 2 registros entre os 5002 existentes. Para o segundo, ele sabe que serão retornados 5000 em 5002. Isso explica a escolha dos operadores que usam SEEK ou SCAN. Como a tabela somente tem um índice non-clustered, a opção que temos para o SCAN é TABLE SCAN e para o SEEK é INDEX SEEK.

Devemos observar que, como o segundo comando é diferente do primeiro, o SQL Server não utiliza o plano de execução em cache, criando planos distintos.

Agora, uma outra situação - vamos usar uma query parametrizada, ou seja, usamos uma variável para armazenar a condição do filtro:

DBCC FREEPROCCACHE
DECLARE @TIPO VARCHAR(10)
SET @TIPO = ‘‘ALIMENTO‘‘
SELECT * FROM PRODUTO WHERE TIPO = @TIPO
SET @TIPO = ‘‘FERRAMENTA‘‘
SELECT * FROM PRODUTO WHERE TIPO = @TIPO

Aqui temos problemas. O SQL Server não conseguiu entender que o primeiro select retorna poucos registros em relação ao total da tabela. Por isso, usou o SCAN.

Temos algumas formas de forçar o SQL Server a criar um plano adequado. A primeira delas é usar a procedure sp_executesql:

DBCC FREEPROCCACHE
DECLARE @SQL NVARCHAR(100)
SET @SQL = ‘‘SELECT * FROM PRODUTO WHERE TIPO = ‘‘‘‘ALIMENTO‘‘‘‘‘‘
EXEC SP_EXECUTESQL @SQL
SET @SQL = ‘‘SELECT * FROM PRODUTO WHERE TIPO = ‘‘‘‘FERRAMENTA‘‘‘‘‘‘
EXEC SP_EXECUTESQL @SQL

Outra é criar uma procedure como a seguinte:

CREATE PROC USP_BUSCA_PRODUTO_POR_TIPO
      @TIPO
VARCHAR(10)
AS
      SELECT * FROM PRODUTO WHERE TIPO = @TIPO
GO

Testamos a execução da mesma, com os seguintes comandos (e vemos que o mesmo plano é gerado para as duas situações - o plano foi gerado com base na primeira execução - como os comandos são os mesmos, o SQL Server usou o plano já em cache):

DBCC FREEPROCCACHE
EXEC USP_BUSCA_PRODUTO_POR_TIPO ‘‘FERRAMENTA‘‘
EXEC USP_BUSCA_PRODUTO_POR_TIPO ‘‘ALIMENTO‘‘

Para tirar a dúvida, invertemos a ordem das duas procedures acima, executamos novamente (com o DBCC antes, claro) e vemos que o plano gerado foi o da primeira execução.

Concluímos, então (corretamente), que o DBCC FREEPROCCACHE (segundo modo de forçar o plano de execução) limpa o cache e obriga o SQL Server recompilar as procedures novamente. Veja que quando era com comando nem o DBCC resolveu. Tivemos que apelar para o sp_executesql.

Para indicar que uma procedure deve ser recompilada na execução, podemos usar o comando abaixo (terceiro modo):

EXEC USP_BUSCA_PRODUTO_POR_TIPO ‘‘ALIMENTO‘‘ WITH RECOMPILE
EXEC USP_BUSCA_PRODUTO_POR_TIPO ‘‘FERRAMENTA‘‘ WITH RECOMPILE

Ou, podemos criar a procedure com o parâmetro with recompile (quarta forma), que nos permite executar a procedure sem usar a cláusula WITH RECOMPILE:

DROP PROC USP_BUSCA_PRODUTO_POR_TIPO
GO
CREATE PROC USP_BUSCA_PRODUTO_POR_TIPO
      @TIPO
VARCHAR(10)
      WITH RECOMPILE
AS
      SELECT * FROM PRODUTO WHERE TIPO = @TIPO
GO

Prestenção, fiote: eu não disse que uma forma é melhor ou pior que a outra… Por enquanto estou somente mostrando opções.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?