Troubleshooting Parameter Sniffing

Brent Ozar has a guide on troubleshooting parameter sniffing:

Parameter sniffing fixes are based on your career progression with databases, and they go like this:

1. Reboot the server! – Junior folks panic and freak out, and just restart the server. Sure enough, that erases all cached execution plans. As soon as the box comes back up, they run rpt_Sales for China because that’s the one that was having problems. Because it’s called first, it gets a great plan for big data – and the junior admin believes they’ve fixed the problem.

2. Restart the SQL Server instance – Eventually, as these folks’ careers progress, they realize they can’t go rebooting Windows all the time, so they try this instead. It has the same effect.

If a reboot can’t fix the problem, I’m out of ideas…

By the way, I second Brent’s recommendation of Erland’s query plan article.  Erland doesn’t publish frequently, but when he does it’s worth the wait.

Related Posts

What Prevents Plan Reuse?

Eric Blinn walks us through what might cause a query plan not to be used: There are several reasons that a query plan would need to be compiled again, but they can be boiled down to a few popular reasons. The first one is simple.  The plan cache is stored exclusively in memory.  If there […]

Read More

Capturing UDF CPU Times

Jonathan Kehayias notes an improvement in recent versions of SQL Server: Microsoft has been enhancing the contents of the ShowplanXML output for SQL Server over the last few releases and in SQL Server 2017 CU3, they introduced user-defined function (UDF) execution statistics into the QueryTimeStats node of the XML output. This was also back ported to SQL Server […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031