Hi,

Does anyone know how do I use the profiler or performance monitor to identify which stored procedure need to be recompile? I think as the best practice before we go and dig more details with the performance issue, do a recompilation in the first place will help dba to shorten the investigating the cause.

Of coz I know there are many many thing need to be consider in order to hunt for the performance issue, but I strongly believe a recompilation is the first attempt to resolve a problem with query time out. 

Samkry,

I am not aware of any SQL Server related configuration that will time out a query.  Some middle tier applications and web servers will do so, but I am not familiar with any default configuration that will stop a query from running.

As far as capturing the recompiled stored procedures, Profiler would be the right tool to capture the metrics.  Once you load Profiler, click on the Events Selection Tab, then click on the 'Show all Events' check box on the bottom right, scroll down to Stored Procedures and select the SP:Recompile check box.  Run Profiler to capture the metrics then review the results to see if your hunch is correct.

Here are some tips related to Profiler to help you get started:

• http://www.mssqltips.com/tip.asp?tip=1264
• http://www.mssqltips.com/tip.asp?tip=1234
• http://www.mssqltips.com/tip.asp?tip=1035
• http://www.mssqltips.com/tip.asp?tip=1222

If you are having a performance problem, check out the Performance Tuning category on the web site - http://www.mssqltips.com/category.asp?catid=9

Good luck and let us know if this works!

Thank you,
The MSSQLTips.com Team

 Hi,

Yes u are rite, sql profiler will do the job, but what is the criteria to indicate that is the procedure that causing the performance issue? shoul i says all query above 1 sec must be recompile ? 

Samkry,

These events in Profiler are what you need: Stored Procedures and select the SP:Recompile.

The a long execution time for a stored procedure does not necessarily mean that it is recompiling.

Thank you,
The MSSQLTips.com Team