DBA,  Performance

Parameter sniffing problem PSP SQL server database capture and analysis options


Being a DBA we could see many of PSP parameter sniffing problem is SQL server database. I have seen many of it, following is the way to capture and analysis.

Issue 1 – We had issue for one of the important claim data loads every night, which suddenly running long time to load the data.

Issue 2 – The website is going unresponsive state daily once or twice

By default SQL server will sniffing the parameter, meaning the SQL engine will store the plan in cache for any parameters the same plan will be used in the future. It is good most of the time, since parameter Sniffing is useful for reusing the same execution plan and values without creating, but for some parameter values the same plan will not work efficiently. We have to test and work out for those cases.

We have to capture the query plan to see how it is generating for each parameters, is it making difference in each parameters or using the same plan etc.

We can use extended events, Profiler, DMVs. I use Whoisactive – This will store a query plan as also.

Use SQL server management studio 17 to compare the plan.

You can open the execution plan and right click in the plan and select compare plan, in that if you have many batch statements, come to multi statements and select which batch to compare it.


From the above plan, sometimes it takes 1 CPU and sometimes 8 CPU. The total CPU of the server is 8. It is a legacy server 2008. When it takes 1 CPU it runs and loads data 1:30+ hours and with 8 CPU parallel plans it takes nearly 30 minutes.


I have tested by changing instance level maxdop and finally used maxdop 8 query hint for the code with recompile option.

We have to test the query with all the possibilities. I have tested many times, with literal –means direct value, with recompile, OPTION (MAXDOP no), OPTION (RECOMPILE) and by using local variable same as using the OPTION (OPTIMIZE FOR UNKNOWN), this will use the Density victor instead of histogram.

Read this https://www.sqlskills.com/blogs/kimberly/stored-procedure-execution-with-parameters-variables-and-literals/

New versions SQL 2016 – we can disable PARAMETER_SNIFFING, instead of using Trace Flag 4136.


Some interesting DMVs to work with PSP issue:

use Northwind
--- look the plan cache
    dm_exec_sql_text.text AS TSQL_Text,
    dm_exec_query_stats.total_worker_time AS total_cpu_time,
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%Order%';

-- you can look plan and plan handle, we can clear particular plan from cache
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE text like '%Orders_%';

SELECT qs.plan_handle, a.attrlist,*
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
              WHERE  epa.is_cache_key = 1
              ORDER  BY epa.attribute
              FOR    XML PATH('')) AS a(attrlist)
WHERE  est.objectid = object_id ('dbo.List_orders_6')
  AND  est.dbid     = db_id('Northwind') 

dbcc show_statistics ('Orders','OrderDate')

-- DMV to get SET values, Statement, Parameter, Sniffed Value and Query Plan

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)
SELECT @dbname = 'Northwind',
       @procname = 'dbo.List_orders_11'

; WITH basedata AS (
   SELECT qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,
       CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
       CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
       CAST (query_plan AS xml) AS [Query plan]
FROM   next_level n
CROSS  APPLY   n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter


Here is Erland Sommarskog’s post http://www.sommarskog.se/query-plan-mysteries.html , this is having more and more good to read. I have copied DMV’s from his post, like settings, reindexing etc.

Advanced hint usages:

SQL will always try to use a query as parameterized default simple, Some complex query we can force it. Some query may behave differently for that can use plan guide as well.


Parameter is highly unpredictable, recompile every time will be good. Needs testing. Execution for the particular parameter has high selectivity, instead of actual value. It will be useful. When we do not have control of code, like sharepoint. We can create plan and use the option to give any query hint (maxdop, PARAMETERIZATION,optimizer for, recompile) etc.

One of other database server – it is a SQL 2005 and it is a SharePoint, No control to change anything, we used to clear the cache, whenever there is a complaint for accessing web page. We can create plan guide as well.

Dbcc freesystemcache (all)

Dbcc freeproccache

Dbcc dropcleanbuffers

Note: This will clear all from buffer not good to run in production. You can get plan handle ID and clear particular problematic plan from cache Dbcc freeproccache (plan handle ID)


It is always good to test with all the combination and configure the same for your environments.

Parameter sniffing is good all the time, since there is no need to recreate a plan every time it is called. When we face the performance slowness, we can test and fix it.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: