My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, however it only goes back as far as 10:17am September 1st.
My boss assures me that (although he likes to tinker with the server in his spare time) the server has been up since May 5th.
I'm pretty new to this, so explain it like I'm a particularly simple house pet, but is there are reason why my results table only goes back 1 month, instead of the full 6? I would assume that uptime is reset if the server is restarted, but I'm not sure why I wouldn't have any history that far back.
Does viewing the procedure by using
right-click -> modify count as an execution?
SELECT qt.text AS 'SP Name', qt.dbid as 'SP DB', qs.execution_count AS 'Execution Count', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_physical_reads AS 'PhysicalReads', qs.last_execution_time 'Last Executed' FROM sys.dm_exec_procedure_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt where qs.database_id = 5 ORDER BY qs.last_execution_time asc
My question relates more to the time frame of the results. I'm finding the information I need in the month of results I have but I'm worried there are 6 months of results that aren't being found found.
This returns May 5th:
select create_date from sys.databases where name = N'tempdb';
The following returns "2015-05-05 08:27:42.080":
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
Finally, this one returns "2013-09-15 19:34:34.667":
SELECT top 1 [rs].[destination_database_name], [rs].[restore_date] FROM msdb..restorehistory rs where destination_database_name = '%ID%' ORDER BY [rs].[restore_date] DESC;
Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.
Therefore, it is likely that some time on September 1st, the server had an event that caused the procedure cache to be flushed, or at the very least, caused the plans for the procedures you care about to be pushed out of cache. We've already ruled out the most obvious culprit - a failover or service restart - but it could have been anything, really:
sp_configurechange, which - for certain options - has a side effect of clearing the procedure cache