Blog – Fabiano Neves Amorim

Estou em um cliente e desconfiei de um problema relacionado ao parameter snifing em uma proc, pra variar um pouco…

Dai lembrei de uma sessão do Jason Strate sobre XML e Execution Plans sensacional…

http://www.jasonstrate.com/presentations/past-presentations/

A sessão é a “Using XML to Query Execution Plans”, e tem um arquivo na pasta de scritps chamado “005 – Parameterization.sql”. Eu usei o mesmo conceito utilizado por lá e fiz uma modificação para especificar uma proc em uma variável e verificar quais foram os parâmetros utilizados para criação do plano em cache para a proc.

Sensacional, segue um exemplo:

— test
DBCC FREEPROCCACHE
GO
use master
GO
sp_help sysobjects
GO

Para testar rodei um exec na proc sp_help passando como parâmetro a “tabela” sysobjects.

Agora consigo consultar os parâmetros utilizados para gerar o plano de execução para a proc sp_help usando o código abaixo:

— Query para consultar parâmetros utilizados para criação de um plano
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

DECLARE @ProcName VarChar(500)
SET @ProcName = ‘sp_help’;

—http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/27/digging-into-the-sql-plan-cache-finding-missing-indexes.aspx
WITH XMLNAMESPACES  (DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)
,PlanHandles AS (
    SELECT DISTINCT plan_handle
    FROM sys.dm_exec_query_stats
    )
,PlanParameters
AS (
    SELECT ph.plan_handle, qp.query_plan, qp.dbid, qp.objectid
    FROM PlanHandles ph
        OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
    WHERE qp.query_plan.exist(‘//ParameterList’)=1
    and OBJECT_NAME(qp.objectid, qp.dbid) = @ProcName
)
SELECT
    DB_NAME(pp.dbid) AS DatabaseName
    ,OBJECT_NAME(pp.objectid, pp.dbid) AS ObjectName
    ,n2.value(‘(@Column)[1]’,’sysname’) AS ParameterName
    ,n2.value(‘(@ParameterCompiledValue)[1]’,’varchar(max)’) AS ParameterValue
    ,n1.query(‘.’) AS N1_XML
    ,n2.query(‘.’) AS N2_XML
From PlanParameters pp
    CROSS APPLY query_plan.nodes(‘//ParameterList’) AS q1(n1)
    CROSS APPLY n1.nodes(‘ColumnReference’) as q2(n2)

It’s that nice or what?

Abs