Início > Parameter Sniffing, T-SQL, Virtual PASS BR > PSP–Parameter Sniffing Problem + DMV CachePlans

PSP–Parameter Sniffing Problem + DMV CachePlans

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

image

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)

image

It’s that nice or what?

Abs

Anúncios
  1. 31 de agosto de 2011 às 19:49

    Show de Bola. Gostei..bookmarked 🙂

  2. Leandro Ribeiro
    1 de setembro de 2011 às 10:31

    Show mesmo…vlw mestre!!

  1. No trackbacks yet.

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: