Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure performance issue

Status
Not open for further replies.

ZenRaven

Programmer
Mar 13, 2007
84
US
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top