When was Stored Procedure Last Compiled?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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.

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