Plan Cache – Proc Cache – Como melhor utiliza-lo…

Pretendo com este post, continuar falando sobre Plan Cache/Proc Cache, no primeiro (Que pode ser acessado aqui) eu expliquei o que era e para que servia, agora, irei comentar sobre sua melhor utilização.

Com este, espero que desenvolvedores de banco de dados entendam o quão importante é manter um plano de execução em memoria, e qual a melhor maneira para se escrever uma query.

Utilizarei as querys passadas no post anterior, alem de uma tabela propria que ja possuo aqui em meu banco de dados local, uma vez que o intuito não é mostrar os dados, e sim o plano de execução em memoria.

--PRIMEIRO PASSO - LIMPAR O CACHE
DBCC FREEPROCCACHE

--SEGUNDO PASSO - VISUALISAR PLANOS EM CACHE
USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY dbid,usecounts DESC;
GO
/*
UseCounts	RefCounts	Cacheobjtype	Objtype	DatabaseName	SQL
1			2			Compiled Plan	Adhoc	ResourceDB		SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,   ISNULL(DB_NAME

(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL   FROM sys.dm_exec_cached_plans   CROSS APPLY sys.dm_exec_sql_text(plan_handle)   ORDER BY dbid,usecounts DESC;  
2			1			Parse Tree		View	ResourceDB		CREATE FUNCTION sys.dm_exec_sql_text(@handle varbinary(64))  

RETURNS TABLE  AS   RETURN SELECT * FROM OPENROWSET(TABLE FNGETSQL, @handle)  
2			1			Parse Tree		View	ResourceDB		create view sys.dm_exec_cached_plans as select * from 

OpenRowset(TABLE SYSDMEXECCACHEDPLANS)  
*/

--TERCEIRO PASSO - QUERY ADHOC
SELECT * FROM Cursos..Completo
WHERE Id_Geral = 1

--QUARTO PASSO - VISUALISAR PLANOS EM CACHE
USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY dbid,usecounts DESC;
GO
/*
1	2	Compiled Plan	Adhoc	ResourceDB	SELECT * FROM Cursos..Completo  WHERE Id_Geral = 1
*/

--QUINTO PASSO - QUERY ADHOC
SELECT * FROM Cursos..Completo
WHERE Id_Geral = 2

--SEXTO PASSO - VISUALISAR PLANOS EM CACHE
USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY dbid,usecounts DESC;
GO
/*
1	2	Compiled Plan	Adhoc	ResourceDB	SELECT * FROM Cursos..Completo  WHERE Id_Geral = 2
1	2	Compiled Plan	Adhoc	ResourceDB	SELECT * FROM Cursos..Completo  WHERE Id_Geral = 1
*/

/*
Podemos observar que apesar das querys serem iguais, ele recompilou a cada execução, e colocou os 2 planos em memoria
*/

--SETIMO PASSO - EXECUTE_SQL COM PARAMETRO 1
DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT * FROM Cursos.dbo.Completo
WHERE Id_Geral = @Id'
DECLARE @Variavel NVARCHAR(100)
SET @Variavel = '@Id INT'
DECLARE @Parametro INT
SET @Parametro = 1
EXEC sp_executesql @SQL, @Variavel, @Id = @Parametro;

--OITAVO PASSO - VISUALISAR PLANOS EM CACHE
USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY dbid,usecounts DESC;
GO
/*
1	2	Compiled Plan	Prepared	ResourceDB	(@Id INT)SELECT * FROM Cursos.dbo.Completo  WHERE Id_Geral = @Id
*/

--NONO PASSO - EXECUTE_SQL COM PARAMETRO 1
DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT * FROM Cursos.dbo.Completo
WHERE Id_Geral = @Id'
DECLARE @Variavel NVARCHAR(100)
SET @Variavel = '@Id INT'
DECLARE @Parametro INT
SET @Parametro = 2
EXEC sp_executesql @SQL, @Variavel, @Id = @Parametro;

--DECIMO PASSO - VISUALISAR PLANOS EM CACHE
USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY dbid,usecounts DESC;
GO
/*
2	2	Compiled Plan	Prepared	ResourceDB	(@Id INT)SELECT * FROM Cursos.dbo.Completo  WHERE Id_Geral = @Id
*/

/*
Com a utilização do EXECUTE_SQL, podemos ver que apesar do parametro variar, ele utilizara o mesmo plano de execução novamente.
Podemos ter a confirmação pelo campo UseCounts, aonde, utilizando EXECUTE_SQL, foi para 2 ao invez de 1, como quando executamos ad-hoc
*/
Anúncios
Post a comment or leave a trackback: Trackback URL.

Comentários

  • Alexandre.  On 25/01/2012 at 15:39

    Muito legal esse artigo, sabia que o sp_executesql tinha algumas vantagens pra optimizar execução de queries dinâmicas, mas sempre tive a impressão de que reaproveitar o plano de acesso não era uma delas.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: