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%'
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.