Sql-server – Stored procedure running for a long time

I would create a polling job and auditing table to track this information, much like I do currently with space usage in my environment. This seems fairly straightforward and will not be as high impact as running a continuous trace. While the frequency of your polling would depend largely on your procedure cache churn, even executing a snapshot every few minutes would be fairly lightweight.

Auditing Table

CREATE TABLE sproc_tracking
(sproc_name sysname
,last_execution_time datetime
,CONSTRAINT pk_sproc_tracking PRIMARY KEY CLUSTERED (sproc_name));

Auditing MERGE Statement (usable since we're talking SQL 2008)

MERGE INTO sproc_tracking
USING (SELECT p.name, max(ps.last_execution_time) last_time
    FROM sys.dm_exec_procedure_stats ps
    JOIN sys.procedures AS p ON (ps.object_id = p.object_id)
    GROUP BY p.name
) AS sproc_stats(name,last_execution_time)
ON (sproc_stats.name = sproc_tracking.sproc_name)
WHEN MATCHED THEN
    UPDATE SET last_execution_time=sproc_stats.last_execution_time
WHEN NOT MATCHED THEN
    INSERT (sproc_name,last_execution_time)
    VALUES (sproc_stats.name,sproc_stats.last_execution_time);

Once you create the table, I would place the auditing statement in a SQL Agent Job to execute periodically based on your cache churn. Probably a good place to start, if your system is reasonably active, is every 15 minutes.

Gotchas

• Since this is still based on procedure cache, any stored procedures that are created WITH RECOMPILE will not be gathered no matter what.
• This process will need to execute/collect within the context of your specific database. If you need multiple databases, you'll need to adapt the process to execute in each one across the whole server. There are a variety of ways to do this.

Had a situation where a production environment had DB schema updated

The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why.

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query.

From BOL - Recompiling Execution Plans :