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

Question: When was Stored Procedure Last Compiled?

When was Stored Procedure Last Compiled? - Interview Question of the Week #292 LastCompiled-800x256

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.

    SCHEMA_NAME(sysobject.schema_id) SchemaName,
    OBJECT_NAME(stats.object_id) SPName, 
    cached_time, last_execution_time, 
            AS avg_elapsed_time
    sys.dm_exec_procedure_stats stats
    INNER JOIN sys.objects sysobject 
        ON sysobject.object_id = stats.object_id 
    sysobject.type = 'P'
    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)

SQL Cache, SQL Performance, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
What Happens If the Clustered Index is Disabled? – Interview Question of the Week #291
Next Post
Where are SQL Jobs Stored? – Interview Question of the Week #293

Related Posts

1 Comment. Leave new

    September 7, 2020 4:23 am

    hi Pinal i have question what is the impact if i had licence for sql server enterprise edition 2014 for 8 cores and after some years we increase the cores of the server and if there is impact what will be the solution


Leave a ReplyCancel reply

Exit mobile version