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.
New versions SQL 2016 – we can disable PARAMETER_SNIFFING, instead of using Trace Flag 4136.
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
Some interesting DMVs to work with PSP issue:
use Northwind
go
--- look the plan cache
SELECT TOP 10
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
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
SELECT *
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_start_offset,
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)
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:
| PARAMETERIZATION |
| 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. |
| Recompile | OPTIMIZE FOR | PLAN GUIDE |
| 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)
Conclusion:
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.
