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
