SET options and stored procedure

Last Post 04 Apr 2008 11:05 AM by trans53. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
04 Apr 2008 10:37 AM
Hi all,

All of the sudden one of our stored procedures which was run very fast begin to take more than 30 sec generating timeouts.
I simply recompiled the proc and everything is fine now but wondering what can cause this.

One of the questions is the SET options. We don't have any SET options inside the proc but i was wondering how i can monitor which SET options are running when executing this stored procedure.

Is there any way to find this using profiler?

Thanks
trans53
New Member
New Member

--
04 Apr 2008 11:05 AM
Russel, thanks.

Before i recompiled i run this sp in QA and it was very fast. Indexes defrag daily and statistics are also updated. I also did not see anything wierd in execution plan.

Can you please clarify about "infer the SET options from that"?
trans53
New Member
New Member

--
04 Apr 2008 01:02 PM
Thank you!
SQLUSA
New Member
New Member

--
05 Apr 2008 06:17 AM
Same happened to me many times on extremely dynamic tables.

I cannot afford manual recovery.

I have key sprocs on sp_recompile every 1/2 hour or so.

My take on it that due to rapid/great number of inserts the execution plan become obsolete.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/
stephenmoore56
New Member
New Member

--
16 Apr 2008 02:37 PM
We had a performance degradation issue with similar symptoms. Statistics seemed to go out of date very quickly, bad plans were executing and we were running sp_updatestats every night and rebuilding indexes every weekend. Running sp_updatestats and recompiling everything / clearing the procedure cache during the day sometimes helped to alleviate the symptoms. To make a long story short, we began running a procedure every night that performed an UPDATE STATISTICS [<table name>] WITH FULLSCAN on every table in the database. The problem disappeared completely and forever. The sample size used by sp_updatestats was apparently the issue. Comments by gurus on why this might have solved our problem much appreciated. It's mysterious to me, although no longer a problem.
You are not authorized to post a reply.

Acceptable Use Policy