How to create a sql script for monitoring the SQL Server Stored Procedure Performance and identify long running stored procedures
We currently have performance issues as I’m sure most data-driven systems do have the same problems. Currently, they basically fall into 2 categories that I think a single solution can solve: a.Stored procedures sometimes get automatically recompiled in the system with a bad plan, that causes it to run really slow. The reason for this is that the set of parameters that it first gets recompiled with are not representative\normal\optimal. This then causes the stored procedure to run really slow and it needs to be recompiled to pick up a better plan b. Due to the dynamic nature of how SQL Server works, as a table grows, as different parts of the system maybe query it differently – the indexes need to change or a code change is required to remove sub-optimal coding, like OR’s, functions in WHERE conditions, etc. Is there any system tables that track the cost of stored procedures? We need to create a script, that should run for every hour for all the week(7 Days) and we need to have/store the data of stored procedure (like execution time, cost of stored procedure and so on). From this, we can identify the list of stored procedures which are performing very worst and running for longer time and generate the list of SP’s. From that, we can perform tuning on this SP’s to improve the performance.
using perfmon is best solution for you ,that my guess and also try SELECT TOP 10 * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY qs.total_elapsed_time DESC to check query status .and if you havent use SP_WHOISACTIVE yet get it from online .it will help you to find out the problem.