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;