Iniciei esta série com o post intitulado: Pequenas Mesas Bobby, Injeção SQL e EXECUTAR COMO. Passei então a discutir algumas das diferenças com o post intitulado: EXEC e sp_executesql – como são diferentes?
Hoje, quero abordar alguns dos comentários bem como continuar com algumas dicas e truques usando estes comandos.
First off – poderíamos ter ajudado o desempenho da declaração sp_executesql?
Sim…
Se soubermos que uma declaração devolve uma quantidade variável de dados (com base nos parâmetros fornecidos) então podemos usar a funcionalidade SQL Server 2005 COM RECOMPILE para dizer ao SQL Server que a declaração a ser executada deve ter o seu próprio plano criado e que os planos anteriores (se existirem) não devem reutilizar a declaração. Também diz ao SQL Server que este plano específico é um “plano de utilização única” que não deve ser reutilizado para utilizadores posteriores. Para ver a combinação de todas estas coisas – vou usar alguns dos DMVs que rastreiam a cache de planos e a utilização de planos.
DBCC FREEPROCCACHE
GO
SELECT st.text, qs.EXECUTION_COUNT -, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WHERE st.text like ‘%FROM dbo.member%’
GO
P>Direito agora, esta consulta retorna 0 linhas.
ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.membro WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Tripp’
GO
Agora, temos uma fila para o nosso plano de consulta parametrizado:
text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 1
Então, o que é que isto nos mostra… mostra-nos que existe um plano em cache para esta afirmação. E, se estivermos interessados em ver o plano, podemos remover o cp.* comentado na consulta acima para obter a coluna cp.query_plan. Clique num showplan XML e o SSMS irá DIRECTAMENTE para uma janela gráfica do plano de consulta (2008 em diante):
E, mais uma vez, vemos o plano óptimo (para usar o índice e fazer uma pesquisa de bookmark) porque esta consulta é altamente selectiva (apenas 1 linha).
Executaremos de novo exactamente a mesma declaração – usando o valor de Anderson apenas para chegar ao ponto em que estivemos na semana passada:
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.membro ONDE O ÚLTIMO nome LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
GO
E, vemos que utiliza o mesmo plano EXACTO (olhando para o showplan). De facto, podemos ver isso também a partir da verificação da nossa cache de planos:
text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2
E, embora saibamos que este plano (para usar o índice) é bom para o valor altamente selectivo de ‘Tripp’, não é bom para o valor de Anderson, pois há muitas filas que correspondem. Se suspeitássemos disto e/ou soubéssemos disto quando executávamos (do cliente) então poderíamos usar OPTION (RECOMPILE) para forçar o SQL Server a obter um novo plano:
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.membro ONDE O último nome LIKE @lastname OPÇÃO (RECOMPILE)’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
go
p>O plano de consulta como resultado desta execução é:
.png)
E, este é o plano mais óptimo dado este parâmetro. Neste ponto, as perguntas (IMO) são:
- Alguém seria realmente capaz de programar que um parâmetro submetido pelo cliente é “atípico” e/ou que justifica uma recompilação? E, acho que posso responder sim – para alguns parâmetros – como os que têm um wildcard. Mas, se estamos apenas a falar de dois valores diferentes contra uma única coluna, isto seria adivinhar estatísticas dos dados.
- O que fez o SQL Server com o plano em si?
li>Deve esta afirmação em particular ser sempre executada para recompilar e nunca guardar um plano?
Irei com a resposta #3 primeiro, uma vez que essa é fácil de responder. Usando a mesma declaração, vou novamente consultar a cache do plano:
text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2
P>Even embora esta seja a terceira vez que executamos esta declaração, esta execução final NÃO foi colocada em cache. Foi utilizada apenas para a execução com OPÇÃO (RECOMPILE). E, NÃO afectará futuras execuções. Se eu voltar atrás e executar sem a OPÇÃO (RECOMPILE) então obterei o plano anterior (para usar o índice).
Agora, as outras duas perguntas – estas são muito mais interessantes e é aqui que eu penso que os procedimentos armazenados devem ser usados. Pessoalmente, penso que os programadores que conhecem os dados e conhecem a aplicação – serão muito melhores na criação do código DIREITO especialmente quando compreenderem todas as opções disponíveis.
Aqui está a forma como penso nos procedimentos armazenados e na recompilação:
- Se eu souber que uma determinada declaração devolve sempre o mesmo número de linhas e utiliza o mesmo plano (e, eu saberia isso através de testes), então criarei o procedimento armazenado normalmente e deixarei o plano ficar em cache.
- Se eu souber que uma determinada declaração varia muito de execução para execução e o plano óptimo varia (mais uma vez, eu deveria saber isto a partir do teste de múltiplas execuções de amostras), então criarei o procedimento armazenado normalmente e usarei OPTION (RECOMPILE) para me certificar de que o plano da declaração não é armazenado em cache ou guardado com o procedimento armazenado. Em cada execução que o procedimento armazenado obterá parâmetros diferentes e a declaração particularmente desagradável obterá um novo plano em cada execução.
No entanto, é também aqui que as coisas se tornam mais difíceis. Tenho visto frequentemente procedimentos armazenados onde as pessoas tentam e usam a lógica condicional para quebrar os diferentes tipos de parâmetros e isto normalmente não funciona tão bem quanto o esperado (vou escrever isto no blogue a seguir). E, é sempre aqui que alguns decidem que querem construir dinamicamente a declaração que é executada – agora, precisamos de determinar se devemos ou não usar sp_executesql ou EXEC. E, neste momento, existem realmente algumas opções. Finalmente, em mais um ou dois posts – vou finalmente mostrar-vos onde o EXEC é um vencedor claro sobre o sp_executesql porque mesmo a OPÇÃO (RECOMPILE) nem sempre ajuda TODOS os tipos de planos (e especialmente um dos tipos de planos mais comuns que vejo).