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!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.