SQL Poor Stored Procedure Execution Plan Performance - Parameter Sniffing

SQL poor stored procedure execution plan performance - parameter sniffing

Basically yes - parameter sniffing (in some patch levels of) SQL Server 2005 is badly broken. I have seen plans that effectively never complete (within hours on a small data set) even for small (few thousand rows) sets of data which complete in seconds once the parameters are masked. And this is in cases where the parameter has always been the same number. I would add that at the same time I was dealing with this, I found a lot of problems with LEFT JOIN/NULLs not completing and I replaced them with NOT IN or NOT EXISTS and this resolved the plan to something which would complete. Again, a (very poor) execution plan issue. At the time I was dealing with this, the DBAs would not give me SHOWPLAN access, and since I started masking every SP parameter, I've not had any further execution plan issues where I would have to dig in to this for non-completion.

In SQL Server 2008 you can use OPTIMIZE FOR UNKNOWN.

SQL Parameter sniffing is it possible that recompile does not help but local variables do

Ran into the same issue again and wanted to post the answer that worked for me.

I ran UPDATE STATISTICS [TableName] WITH FULLSCAN on the various tables relevant to my query, after this it worked as fast as it did using local variables.

Intresting to note that without the WITH FULLSCAN it would still be slow. Also after updating the stats it was fast the first run and then slow the next runs so I had to keep updating the stats to get it to run fast, I then tried updating the stats, running the query (was a fast run) and then calling DBCC FREEPROCCACHEand this seemed to fix it so it was fast every time I ran it

I guess this means it wasn't parameter sniffing that was the issue.

Parameter Sniffing causing slowdown for text-base query, how to remove execution plan?

If you want to remove a specific plan from the cache then it is really a two step process: first obtain the plan handle for that specific plan; and then use DBCC FREEPROCCACHE to remove that plan from the cache.

To get the plan handle, you need to look in the execution plan cache. The T-SQL below is an example of how you could search for the plan and get the handle (you may need to play with the filter clause a bit to hone in on your particular plan):

SELECT top (10)
    qs.last_execution_time,
    qs.creation_time,
    cp.objtype, 
   SUBSTRING(qt.[text], qs.statement_start_offset/2, ( 
       CASE  
           WHEN qs.statement_end_offset = -1 
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2  
           ELSE qs.statement_end_offset  
       END - qs.statement_start_offset)/2  + 1
   ) AS query_text, 
   qt.text as full_query_text, 
   tp.query_plan,
   qs.sql_handle,
   qs.plan_handle
FROM  
   sys.dm_exec_query_stats qs 
   LEFT JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle=qs.plan_handle
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt 
   OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp 
WHERE qt.text like '%vu_Network_Activity%'

Once you have the plan handle, call DBCC FREEPROCCACHE as below:

DBCC FREEPROCCACHE(<plan_handle>)

SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes

This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance - parameter sniffing

There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time.

The recommended fix for most situations (though it depends on the structure of your query and sproc) is to NOT use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries.

Should you always anticipate problems caused by parameter sniffing?

A simple search for a single value like this shouldn't be vulnerable to parameter sniffing. It's more of a concern when the parameters passed in result in widely different results and the optimal execution plan varies from the one that has been previously produced.

As an example, think of a query that looks for rows where a date column is between a @start_date and an @end_date. Calling the procedure with a date range of 2 days may produce/cache an execution plan that is not optimal for a date range of 1 year.

---

Related Topics