When was Stored Procedure Last Compiled? – Interview Question of the Week #292

Question: When was Stored Procedure Last Compiled?

Answer: Honestly, it is difficult to figure out when was the stored procedure last compiled. As a matter of fact, SQL Server does not keep this information out in the public, that is why I understand. However, just like everything else in the SQL Server, I believe there is a workaround for this one.

In SQL Server DMV sys.dm_exec_procedure_stats there is a column which is called cached_time. While not 100% accurate but we can assume that when the stored procedure was compiled at that time it might have got cached in the memory. Now there may be other reasons why the cache was removed and re-created. In those cases, our assumption may be wrong.

To be on the same side we can say that we may not know when the stored procedure was last compiled but we for sure know when it was last cached in memory and that is stored in the DMV sys.dm_exec_procedure_stats and in the column cached_time.

I have previously blogged about this one here: Recent Execution of Stored Procedure – SQL in Sixty Seconds #118. Here is the script which you can use to find out the cached time.

SELECT 
    SCHEMA_NAME(sysobject.schema_id) SchemaName,
    OBJECT_NAME(stats.object_id) SPName, 
    cached_time, last_execution_time, 
    execution_count,
    total_elapsed_time/execution_count 
            AS avg_elapsed_time
FROM  
    sys.dm_exec_procedure_stats stats
    INNER JOIN sys.objects sysobject 
        ON sysobject.object_id = stats.object_id 
WHERE 
    sysobject.type = 'P'
ORDER BY
    stats.last_execution_time DESC

Here is the video where you can see how you can execute the script listed here.

Let me know if you have any question, I am eagerly looking forward to helping with any of your SQL Server Performance Tuning issues via the Comprehensive Database Performance Health Check. You can follow me on twitter here.

Reference: Pinal Dave (https://blog.sqlauthority.com)