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 produtose 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 selectse 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‘‘

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) evemos 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 eobriga o SQL Server recompilar as procedures novamente. Veja que quando era com comando nem o DBCC resolveu. Tivemos que apelar para o sp_executesql.

Paraindicar que uma proceduredeve ser recompiladana 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