It’s Not Always Parameter Sniffing

But It Used To Be Fast

Parameter sniffing gets blamed for a lot of things, and, well, sometimes it is parameter sniffing.

It’s probably not parameter sniffing if:

  • You use local variables
  • You use optimize for unknown
  • You’re recompiling anyway

ButWhatAbout

While working with a client recently, they were absolutely sure they had a parameter sniffing issue.

The general proof given was that as the day went on, queries got slower and slower.

The next day, they’d magically be fast again, and then the same slowdown would happen.

When we looked at the stored procedures in question, it looked like they might be right.

So I set up a test.

Pile and Recompile

We stuck a recompile hint on a stored procedure that people are always complaining about, and watched the runtime throughout the day.

Sure enough, it got slower and slower, but not because it got a bad plan. The server just got busier and busier.

  • 6am: 2 seconds
  • 7am: 6 seconds
  • 8am: 15 seconds
  • 9am: 20 seconds
  • 10am: 30 seconds

I left out some details, and I’m sorry about that. You probably want the last 2 minutes of your life back.

Get in line.

Missing Persons

This poor server had hundreds of database totaling almost 4TB.

With 96 GB of RAM, and 4 cores, there was no good way for it to support many user requests.

When things got slow, two wait stats would tick up: PAGEIOLATCH_SH, and SOS_SCHEDULER_YIELD.

SQL Server had a hard time keeping the data people needed in memory, and it got really busy trying to make sure every query got a fair amount of CPU time.

In this case, it wasn’t parameter sniffing, it was server exhaustion.

Last Farewell

Wait stats aren’t always helpful, but they can help you with investigations.

This kind of resource contention won’t always be the issue, of course.

But when you’re investigating performance issues, it’s important to know what things look like when the server is running well, and what things look like when the’re not.

That includes

  • Wait stats
  • Query plans
  • Overall workload
  • Blocking

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *