none
Stored Procedure Last Execution

    Question

  • Hi,

    Is there a way to tell when a Stored Procedure was last executed in SQL Server 2005?

    I saw a dmv that lists only the procedures that are in cache, but I wonder if is there a way to track all the storede procedures.


    TIA,
    Marcos Galvani
    MarcosGalvani
    Monday, August 10, 2009 5:46 PM

Answers

  • Hi Marcos

    There is no method for us to figure out when the stored procedure is last executed.

    So, based on the current situation, we just could use profiler and your experience to assume which stored procedure is never called.

    if you have any questions on the above, please let me know.

    Regards
    Mark Han
    • Marked as answer by Marcos Galvani Thursday, August 13, 2009 1:40 PM
    Thursday, August 13, 2009 7:54 AM

All replies

  • Marcos,

    As far as I know, there is no such option available via DMV's in SQL Server 2008 also. Your other options will be to poll the dmv's constantly, use tracing or extended events. All of these can add up if the system is a high traffic one.



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Monday, August 10, 2009 7:07 PM
  • If the SP is in the cache, you can find the last execution time like this:

    -- Find one SP in the procedure cache
    	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 '%YourSPName%'

    Unless the SP has WITH RECOMPILE, it should be in the cache if it is being used (unless something has happened to flush the cache).
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    • Proposed as answer by Bikash Dash Tuesday, August 11, 2009 11:21 AM
    • Unproposed as answer by Marcos Galvani Tuesday, August 11, 2009 12:39 PM
    • Proposed as answer by yup. _ Thursday, October 21, 2010 1:23 PM
    Monday, August 10, 2009 8:17 PM
  • As Glenn said if it is in cache you can query


       SELECT object_name(m.object_id), MAX(qs.last_execution_time) 
       FROM   sys.sql_modules m 
       LEFT   JOIN (sys.dm_exec_query_stats qs 
                    CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st) 
              ON m.object_id = st.objectid 
             AND st.dbid = db_id() 
    	where (qs.last_execution_time)  is not null
       GROUP  BY object_name(m.object_id) 
    


    Thanks,
    Nimit

    Tuesday, August 11, 2009 2:04 AM
  • Glenn,

    Thanks for the reply.

    As I told in my quetion, I already know about this dmv. I was wondering if there is a way to get this information for ALL the procedures, not only the ones in cache.

    Looks like it does not. I will keep searching, if someone knows, for sure, that there is not, please, tell me.

    Take Care!
    Marcos Galvani
    MarcosGalvani
    Tuesday, August 11, 2009 12:38 PM
  • Thanks, but this is not what I am looking for.
    MarcosGalvani
    Tuesday, August 11, 2009 12:39 PM
  • Do you have a good reason to believe that the stored procedures you are concerned with are not in the cache?  They normally will be, unless they are using something like WITH RECOMPILE that prevents them from being cached.

    You could also set up a SQL Profiler Trace to capture every stored procedure that is submitted to your server. This would have more overhead and impact on the server. If you have a relatively small amount of SPs that you know are not cached, you could have those SPs INSERT to a logging table, so you could capture when they were called.

    What are you trying to do here?
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    Tuesday, August 11, 2009 3:16 PM
  • Glenn,

    Thanks for your time.

    I need to figure out what are the stored procedures that are not beeing used in the system. That is, they are not in cache, but I have to make sure they are not used anymore for something like the past two years.

    I know that If I run a profiler for some months I will be 99% sure about what are the procedures that have never beeing called but I need to be 100% sure. Maybe the procedure wasn´t called because some special condition that will only happen once a year.

    So I am looking for some information from SQL Server like "Created Date" that let me know when the procedure was last executed. It has to be a persistent information between Server reboots. Do you know if SQL Server store such information?

    Thanks again.
    Marcos Galvani
    MarcosGalvani
    Tuesday, August 11, 2009 4:11 PM
  • Hi Marcos

    There is no method for us to figure out when the stored procedure is last executed.

    So, based on the current situation, we just could use profiler and your experience to assume which stored procedure is never called.

    if you have any questions on the above, please let me know.

    Regards
    Mark Han
    • Marked as answer by Marcos Galvani Thursday, August 13, 2009 1:40 PM
    Thursday, August 13, 2009 7:54 AM
  • Hi,

    Just to share, I devepoled a process to count the execution of Stored Procedures, it is provided as is, please, if you use this script, let me know what do you think about, and send me your comments.

    Review the script and alter the parameters you need to suit your need.
    -- 1. CREATE A DATABASE
    USE [master]
    GO
    -- Have to adjust the Drive and DataBase file Names.
    -- Check your sistem to make sure you will have enought room for this database.
    CREATE DATABASE [Trace] ON  PRIMARY 
    ( NAME = N'Trace', FILENAME = N'D:\Trace.mdf' , SIZE = 1207552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'Trace_log', FILENAME = N'D:\Trace_log.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB)
    GO
    EXEC dbo.sp_dbcmptlevel @dbname=N'Trace', @new_cmptlevel=90
    GO
    ALTER DATABASE [Trace] SET AUTO_SHRINK ON 
    GO
    ALTER DATABASE [Trace] SET AUTO_UPDATE_STATISTICS ON 
    GO
    ALTER DATABASE [Trace] SET AUTO_UPDATE_STATISTICS_ASYNC ON 
    GO
    ALTER DATABASE [Trace] SET RECOVERY SIMPLE 
    GO
    ALTER DATABASE [Trace] SET PAGE_VERIFY CHECKSUM  
    GO
    
    
    -- 2. CREATE THE TABLES USED
    USE [Trace]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- This table is used as a stage table to import the data from the Trace Files.
    CREATE TABLE [dbo].[SP_Trace]
    (
    	[int_RowNumber]		[int]			IDENTITY(1,1)	NOT NULL,
    	[int_EventClass]	[int]							NULL,
    	[nvch_ServerName]	[nvarchar](128)					NULL,
    	[int_DatabaseID]	[int]							NULL,
    	[int_ObjectID]		[int]							NULL,
    	[int_SPID]			[int]							NULL,
    	[nvch_DatabaseName] [nvarchar](128)					NULL,
    	[nvch_ObjectName]	[nvarchar](128)					NULL,
    	[img_BinaryData]	[image]							NULL,
    	PRIMARY KEY CLUSTERED
    		( 
    			[int_RowNumber] ASC
    		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
    				ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- This table will be used to agregate the data from SP_trace Table.
    CREATE TABLE [dbo].[SP_Trace_Count]
    (
    	[nvch_ServerName]	[nvarchar](128)		NULL,
    	[int_DatabaseID]	[int]				NULL,
    	[int_ObjectID]		[int]				NULL,
    	[nvch_ObjectName]	[nvarchar](128)		NULL,
    	[int_ExecCount]		[int]				NULL CONSTRAINT [DF_SP_Trace_Count_ExecCount]  DEFAULT ((1))
    ) ON [PRIMARY]
    GO
    
    
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- This table contains only one record to track which trace file will be imported next.
    CREATE TABLE [dbo].[indice](
    	[int_indc]	[int]	NULL
    ) ON [PRIMARY]
    GO
    
    -- Start with the trace file number 1. 
    -- Do not use the first one because the naming conventions.
    INSERT INTO [Trace].[dbo].[indice] ([int_indc]) VALUES (1)
    GO
    
    
    USE [Trace]
    GO
    /****** Object:  Index [IDX_4_5]    Script Date: 08/07/2009 12:32:59 ******/
    CREATE NONCLUSTERED INDEX [IDX_4_5] ON [dbo].[SP_Trace] 
    (
    	[int_ObjectID] ASC,
    	[int_DatabaseID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
    GO
    
    USE [Trace]
    GO
    /****** Object:  Index [IDX_2,3]    Script Date: 08/07/2009 12:33:45 ******/
    CREATE UNIQUE CLUSTERED INDEX [IDX_2_3] ON [dbo].[SP_Trace_Count] 
    (
    	[int_ObjectID] ASC,
    	[int_DatabaseID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
    GO
    
    
    
    -- 3. CREATES A SERVER SIDE TRACE as a startup procedure.
    -- Also execute it to start the trace capture process.
    USE [master]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE Procedure [dbo].[usp_autostartObjExecCount] as
    -- The directory must be customized to your particular instalation.
    -- Be aware that you will need some space on the drive that holds the trace files,
    -- since the process is assinchronous.
    DECLARE @Cmd	Varchar(8000)
    --Deletes to prevent the error if the system restarts.
    SET @Cmd = 'xp_cmdshell ''Del G:\sp_trace*.trc'''
    EXEC(@Cmd)
    
    -- Have to adjust the Drive and DataBase file Names.
    -- Check your sistem to make sure you will have enought room for this database.
    -- Create a Queue
    DECLARE @int_rc				int
    DECLARE @int_TraceID		int
    
    DECLARE @bint_maxfilesize	bigint
    SET @bint_maxfilesize = 50			-- in MegaBytes.
    
    EXEC @int_rc = sp_trace_create @int_TraceID OUTPUT, 2, N'G:\sp_trace', @bint_maxfilesize, NULL 
    IF (@int_rc != 0) GOTO error
    
    -- Set the events
    DECLARE @bit_on		bit
    SET @bit_on = 1
    
    EXEC sp_trace_setevent @int_TraceID, 42, 22, @bit_on
    EXEC sp_trace_setevent @int_TraceID, 42, 26, @bit_on
    EXEC sp_trace_setevent @int_TraceID, 42, 34, @bit_on
    EXEC sp_trace_setevent @int_TraceID, 42, 3,  @bit_on
    EXEC sp_trace_setevent @int_TraceID, 42, 35, @bit_on
    EXEC sp_trace_setevent @int_TraceID, 42, 12, @bit_on
    
    -- Set the Filters
    DECLARE @int_filter		int
    DECLARE @bint_filter	bigint
    
    -- Set the trace status to start
    EXEC sp_trace_setstatus @int_TraceID, 1
    
    -- Reset the File Counter
    Update Trace.dbo.indice set indc = 1
    
    -- display trace id for future references
    SELECT TraceID = @int_TraceID
    GOTO finish
    
    error: 
    SELECT ErrorCode = @int_rc
    
    finish: 
    GO
    
    -- Use this query to check if the trace is up and running. 
    -- If the status = 1 the it is running.
    -- select * from sys.traces
    
    -- Use this query to stop and delete the trace once you don't need it anymore.
    -- exec sp_trace_setstatus 3, 0
    -- exec sp_trace_setstatus 3, 2
    
    GO
    EXEC sp_procoption N'[dbo].[usp_autostartObjExecCount]', 'startup', '1'
    GO
    
    exec [usp_autostartObjExecCount]
    GO
    
    -- 4. CREATE THE JOBS
    -- Adjust the schedule to your needs.
    USE [msdb]
    GO
    /****** Object:  Job [DBA - SP_Count - Consume]    Script Date: 08/12/2009 10:12:16 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/12/2009 10:12:16 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - SP_Count - Consume', 
    		@enabled=1, 
    		@notify_level_eventlog=0, 
    		@notify_level_email=0, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'No description available.', 
    		@category_name=N'[Uncategorized (Local)]', 
    		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [Consume]    Script Date: 08/12/2009 10:12:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Consume', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'DECLARE	@int_RowNumber		int
    DECLARE @int_EventClass		int
    DECLARE @nvch_ServerName	nvarchar(128)
    DECLARE @int_DatabaseID		int
    DECLARE @int_ObjectID		int
    DECLARE @nvch_DatabaseName	nvarchar(128)
    DECLARE @nvch_ObjectName	nvarchar(128)
    
    While exists(Select top 1 int_RowNumber from SP_Trace)
    Begin
    
    DECLARE CC CURSOR FOR
    	Select top 100000 int_RowNumber, nvch_ServerName, int_DatabaseID, int_ObjectID, nvch_ObjectName from SP_Trace order by int_RowNumber
    
    Open CC
    
    Fetch next from CC into @int_RowNumber, @nvch_ServerName, @int_DatabaseID, @int_ObjectID, @nvch_ObjectName
    While @@Fetch_Status = 0
    Begin
    	SELECT @int_RowNumber, @nvch_ServerName, @int_DatabaseID, @int_ObjectID, @nvch_ObjectName	
    	BEGIN TRY
    		update SP_Trace_Count set int_ExecCount = int_ExecCount + 1 where int_ObjectID = @int_ObjectID AND int_DatabaseID = @int_DatabaseID
    	END TRY
    	BEGIN CATCH
    		Insert into SP_Trace_Count (nvch_ServerName, int_DatabaseID, int_ObjectID, nvch_ObjectName, int_ExecCount) VALUES (@nvch_ServerName, @int_DatabaseID, @int_ObjectID, @nvch_ObjectName, 1)
    	END CATCH
    	Fetch next from CC into @int_RowNumber, @nvch_ServerName, @int_DatabaseID, @int_ObjectID, @nvch_ObjectName
    End
    
    CLOSE CC
    Deallocate CC
    
    DELETE from SP_Trace Where int_RowNumber <= @int_RowNumber
    End
    ', 
    		@database_name=N'Trace', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily - Every 5 minutes', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=4, 
    		@freq_subday_interval=5, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20081211, 
    		@active_end_date=99991231, 
    		@active_start_time=0, 
    		@active_end_time=235959
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    USE [msdb]
    GO
    /****** Object:  Job [DBA - SP_Count - Insert]    Script Date: 08/07/2009 10:22:36 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/07/2009 10:22:36 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - SP_Count - Insert', 
    		@enabled=1, 
    		@notify_level_eventlog=0, 
    		@notify_level_email=0, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'No description available.', 
    		@category_name=N'[Uncategorized (Local)]', 
    		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [teste]    Script Date: 08/07/2009 10:22:37 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Upload Data', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'DECLARE @int_Indc	int
    
    SELECT @int_Indc = int_indc 
    	FROM Indice
    
    DECLARE @vch_Cmd	Varchar(8000)
    
    --Deletes to prevent the error if the system restarts.
    SET @Cmd = ''xp_cmdshell ''''Del G:\sp_trace.trc''''''
    EXEC(@Cmd)
    
    SET @vch_Cmd = ''IF exists(SELECT EventClass, ServerName, DatabaseID, 
    	ObjectID, SPID, DatabaseName, ObjectName  FROM fn_trace_gettable(''''G:\sp_trace_'' + convert(varchar(3), @int_Indc+1) + ''.trc'''', 1) WHERE DatabaseID IS NOT NULL)
    BEGIN
    	INSERT INTO SP_Trace (int_EventClass, nvch_ServerName, int_DatabaseID, 
    		int_ObjectID, int_SPID, nvch_DatabaseName, nvch_ObjectName)
    	SELECT EventClass, ServerName, DatabaseID,
    		ObjectID, SPID, DatabaseName, ObjectName  FROM fn_trace_gettable(''''G:\sp_trace_'' + convert(varchar(3), @int_Indc) + ''.trc'''', 1) WHERE DatabaseID IS NOT NULL;
    END''
    EXEC(@vch_Cmd)
    
    SET @vch_Cmd = ''xp_cmdshell ''''Del G:\sp_trace_'' + convert(varchar(3), @int_Indc) + ''.trc''''''
    EXEC(@vch_Cmd)
    
    UPDATE Indice SET int_indc = int_indc + 1',
    		@database_name=N'Trace', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily - Every 5 minutes', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=4, 
    		@freq_subday_interval=5, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20081211, 
    		@active_end_date=99991231, 
    		@active_start_time=0, 
    		@active_end_time=235959
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    
    
    -- 5. CREATE THE REPORTS
    SELECT count(*) 
    FROM SP_Trace
    
    SELECT * 
    FROM SP_Trace_Count 
    WHERE databaseID = 5 
    ORDER BY execcount DESC
    
    SELECT sum(execCount) 
    FROM SP_Trace_Count 
    WHERE databaseID = 5
    


    Take Care!
    MarcosGalvani
    Tuesday, September 22, 2009 7:19 PM
  • Just as an FYI, while the solution using sys.dm_exec_procedure_stats will work on 2008 or later, the question was actually for SQL 2005.
    Wednesday, February 05, 2014 9:53 PM
  • Dear Sir,

    Thanks a lot for your valuable query.

    Cheers,

    Wednesday, June 18, 2014 6:41 PM