Stored Procedure Performance

I have a performance issue in my production database. I have a global search procedure where all the user use this very freequently for all types of searches. This procedure is taking 10-20 seconds initially and after 2-3 weeks the same is taking 2 to 5 mins!!! <br /><br />Once after restarting the sql server services the procedure is taking normal time. I tried by clearing the LOG but no result. If I restart the Services then only the problem is solving. Now I added a task "Restarting the services for every 2 weeks" to my task list <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Can any one help me out what is the reason behid this and how to resolve this problem?<br /><br />Thanks for your help in advance<br />Raj

Does it just abruptly go from 10-20 seconds to 2 to 5 minutes? Or does it start going to 30 seconds after a day or 2, then to 1 minute after 5-6 days etc?

Try to recompile the stored procedure and see if that resolves the issue before restarting services etc as something to try.

Hi Martin,

Thanks for your reply.

I have enough space in the disk almost 500 GB. And the tempdb database file size properties are automatically grow file and unrestricted file size. And Transaction log size also unrestricted.

It is gradually increasing the time. I recompiled the procedure but same result.

No I dont have any maintainence plans

Luis,

I had a strong suspicion indexes and or statistics could be at issue and likely had no plan in place, however, my head kept asking the same question "Why would restarting the services correct the problem and cause the stored procs to run faster again?" I don't think a shutdown/restart of services would correct index fragmentation in anyway, but I don't know.

Is it possible that the disk i/o is really backing up so the tran log has a lot of uncommited "stuff", that there becomes so many locks, causing blocking, and then the shutdown allows things to catch up and be commited during the shutdown and startup? I have zero proficiency in watching performance monitor statistics to know what to recommend to watch to see if that could be the case.

I understand the concept of the sysindexes table not being totally 100% accurate with the statistics regarding tables. Is it possible that they become so out of tune with reality that it could cause this kind of problem, and then during the shutdown they are brought back to reality so the problem goes away?

Could it be some type of memory problem where things have to constantly get swapped out to the disk and the system can't keep anything in cache stable for long? I could understand how things would run faster after a startup then, but once the paging in/out starts occuring I can't figure why it would continue to degrade instead of reaching a point of thrashing and just stay there.

if restarting the service fixes a problem, it is mostly likely a problem with the virtual address space, look in the SQL Server logs, for entries like: failed to allocate 64K-128K
also run perfmon at 30-60sec interval from the time SQL starts to when this problem is evident, logging under the process object -> the sqlservr instance, for the virtual bytes, private bytes and working set counters

Of all suggestions I think the best chance to be true are first and last one. First one, because sql server restart recreates tempdb, that might be heavily fragmented in the meantime. What are tempdb initial size and files growth settings?

if any modifications happened in your procedure..try 2 save latest execution plans.<br />if any new data added in procedure related tables, try 2 recreate the index ordo update statistics.<br />if not working above well try to create the procedure with different name and see..<br /><br />Thanks<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by rajasekharcj@gmail.com</i><br /><br />I have a performance issue in my production database. I have a global search procedure where all the user use this very freequently for all types of searches. This procedure is taking 10-20 seconds initially and after 2-3 weeks the same is taking 2 to 5 mins!!! <br /><br />Once after restarting the sql server services the procedure is taking normal time. I tried by clearing the LOG but no result. If I restart the Services then only the problem is solving. Now I added a task "Restarting the services for every 2 weeks" to my task list <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Can any one help me out what is the reason behid this and how to resolve this problem?<br /><br />Thanks for your help in advance<br />Raj<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><font color="blue"><br /><b><br />SURYA<br /></b><br /></font id="blue"> <br /><br />

Thanks for all your replies

Based on your comments and in my observation I understand that tempdb is the cause of problem. The tempdb size is increased almost 20 MB in one day. I guess after few weeks it is reaching huge size (I have enough space in the disk 500GB and the database size is unrestricted) and causing the poor performance. When i restarted the services, sql server is clearing the tempdb database and taking to normal size. Is this correct?

I dont have any maintainence plans on tempdb database, how to clear the tempdb database without restarting the services?

Raj

TempDb might be the cause for your problem, not tempDB size itself but the way how you handle it. 20 MB increase is not much. Is it 20 GB actually?

Hi Martin,

I found this articlehttp://www.sql-server-performance.com/tp_automatic_reindexing.asp for reindexing. I have a quick question, this will not create any other problems after reindexing the production database (when no users are connected)?

And also in my observation the procedure is taking time because of one FULL OUTER JOIN to a VIEW which is contain one complex query on main transaction tables with unions. I am thinking to use a temp table instead of directly applying FULL OUTER JOIN on the view. What I mean is first i will insert the data from VIEW to Temp table by applying conditions, and then i will use the temp table for OUTER JOIN. This is the correct way of doing OR is there any other way to avoid FULL OUTER JOIN?

Thanks for your help
Raj