Stored Procedure performance issue

[ZenRaven]

SQL Server 2008 R2

I've got a stored procedure that was taking 30s to run. When I ran it in SSMS it would take under 100ms. The 1st thing I thought was parameter sniffing so I created local variables in the SP and assigned my params to them. That brought the time down to around 5s. I then realized that my SET options were different between SSMS and the ADO call. I set the default on SQL Server to SET ARITHABORT ON and that seemed to fix everything... temporarily.

Now it seems like everything works fine for some time but after the usecount on the cached plan gets into the several thousand range it slows down again. If I use FREEPROCCACHE to clear out the single plan, it speeds up only to slow down again after a few thousand executions. I tried the OPTION(RECOMPILE) hint on my main select in the SP but that only made things worse.

Any suggestions?


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[markros]

Check Parameter Sniffing

In this article Plamen Ratchev gives few other suggestions like query hints.

Try them instead of recompile option or setting parameters to local variables. Try the other approaches from this article.

PluralSight Learning Library

 

[gmmastros]

Is it possible that the statistics for the table(s) involved are getting out of date?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ZenRaven]

The RECOMPILE query hint is what I had tried and it slowed it down. I think the only thing left in the list that may need a try is OPTIMIZE FOR UNKNOWN although that seems like it would do the same thing as my local variable parameter sniffing fix (hack).

I manually updated the statistics and have auto update stats ON. The data I'm working with is fairly static between monthly build cycles.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[ZenRaven]

Thought some of you might be interested in an update.

While I started down the correct path with parameter sniffing, the final issue was a not-so-covering index. I was thrown off by the large variance in execution times because I was not taking into account the buffer cache. Clearing buffer and proc cache in dev gave a consistent execution time around 5s. There was a single column not referenced in the covering index. Adding the column brought it down to about 1s. I'd like to get it even faster but I'm querying a table of about 120 million rows so I won't complain.

One thing that did bother me with this solution was that I had to add a hint to get the optimizer to choose the modified index. Without it, it would opt for another index that was not even close to optimal.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[gmmastros]

Have you looked at the execution plan? Are there any index scans (instead of seeks)? If so, there may still be ways to speed up the query.

Out of curiosity, of the 120 million rows, how many rows are (typically) returned by this query?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ZenRaven]

The only scan is on a temp table which the main table joins to. The temp table unsually has under 5 rows. The seek on the main table is now 73% and the join to the temp table 27%.

The query result set count varies but I would say anywhere between a couple hundred and 15,000 records usually returned.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[gmmastros]

Just for fun, can you try putting an index on the temp table?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ZenRaven]

Just realized I wrote "unusually" in my previous post. Should have been "usually", ha.

The index didn't make a difference since there are so few records in the temp table.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson