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.
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 is 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;
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.|
|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)
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.