July 2, 2010 at 2:21 am
if a stored procedure has 50 Tsql statment and it is suddenly showing slow performance, how wud i trouble shoot n tune it??
July 2, 2010 at 2:38 am
sachinsoni84 (7/2/2010)
if a stored procedure has 50 Tsql statment and it is suddenly showing slow performance, how wud i trouble shoot n tune it??
look at the table's it uses....stored procedure wont become slow itself, but rather your tables..
Check indexes, fragmentation etc..
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 2, 2010 at 6:26 am
Same way you troubleshoot & tune anything else. Identify the most costly statement and tune that first. One way to identify this would be to set up a server-side trace, filtered to only capture the execution of the stored procedure in question, and look at the statements executed by the procedure. This way you can see which statements are running the slowest.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2010 at 6:49 am
how to set up a server side trace??
July 2, 2010 at 10:47 am
Google is your friend
http://www.google.com/search?q=how+to+set+up+a+server+side+trace+%22SQL+Server%22
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply