Sniff that Parameter

How many times does it happen, you have this masterful plan of what you are going to do as soon as you get into the office and bam ~ haven’t even made it out of the car and emails start exploding and coming in.

I would gather with most that this occurance is not out of the norm. In fact it probably happens more times than not. Today, such an issue came up ~ I was anticipating working with some VLF files when a process decided to timeout, a process that we shall say is of the utmost importance. I will do my best to lay out the scenario.

The issue was narrowed down to one procedure. That procedure had one parameter being passed in. Here are initial steps that were thought of

  • Check Indexes (found one to be missing so it was added; will say that Aaron Bertrand’s blog where I found the SQL Sentry utility a few months ago was very helpful in looking at the estimated and actual execution plans).

This did not improve performance. Initially the timeout period for the app has a threshold of 30 seconds. We were consistently hitting over 2 minutes.

  • Check the SP – nothing really stood out. Had some views that were being selected but they were pretty straight forward.
  • Check for any blocking (this was done multiple times throughout the whole assesment process)
  • Check any other sytem abnormalities

So with the procedure still timing out not many other options remaining one of the team members recalled a session they sat in on ~ Parameter Sniffing. The parameter, after being read in was then set to an internal parameter to that stored procedure. Then the newly created parameter was then passed down through the procedure where needed.

Upon executing the proc it took a mere second!

I’ve done a little bit of reading on parameter sniffing and find it very interesting. I would suggest reading Greg Larson’s article ~ he has it laid out very nicely.

This is one aspect I enjoy of being part of a core DBA team. All backgrounds come together and bring different aspects to solutions. I look forward to the next problem to find that unique solution.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s