Yesterday there was an interesting question which was floating around twitter.The question was when was the stored procedure last executed,Is it stored some where?

Immediate answer which came to my mind was there is no way we can get this information until and unless we were running some sort of tracing.

I decided to do some more R&D to find that this information can be found if a particular stored procedure is still in cache.

As we know if we dont clean up the cache or use RECOMPILE statements,the data will be available in the cache.

Let me show a demo to demonstrate this concept –

1. Create a database

--Create a database
CREATE DATABASE Test_SP_Cache
GO

2. Create a table

--Create a Table
Use[Test_SP_Cache]
CREATE TABLE Test_Table(ID Int Identity,Name Varchar(20))
GO

3.Insert data to the table

--Insert data to the table
Use[Test_SP_Cache]
INSERT INTO Test_Table VALUES('John')
GO
INSERT INTO Test_Table VALUES('Jack')
GO
INSERT INTO Test_Table VALUES('Mary')

4. Select data

--Select data
SELECT ID,Name FROM Test_Table

5. Create a procedure

--Create a stored procedure
Use[Test_SP_Cache]
CREATE PROCEDURE dbo.sp_Pull_Names
AS
 SELECT ID, Name FROM dbo.Test_Table

6.Execute the stored procedure

--Execute the procedure
EXEC dbo.sp_Pull_Names

Now as we have executed the procedure and we are pretty sure that the SP will be there in the cache.As mentioned earlier if the SP is in cache then we can get the details of execution by querying the DMV sys.dm_exec_query_stats and sys.dm_exec_sql_text

SELECT QT.[text] AS [SP Name], QS.last_execution_time, QS.execution_count AS [Execution Count] 
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS QT
WHERE QT.dbid = db_id() -- Filter by current database
AND QT.text LIKE '%dbo.sp_Pull_Names%'

Conclusion

We have yet again explored the power of DMV’s.I greatly respect the man who has helped me a lot to learn DMV’s and he is none other than Glenn Berry (@GlennAlanBerry)

Thanks for reading.