sys.dm_exec_procedure_stats missing history?

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?

SQL Query

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;

From the documentation on sys.dm_exec_procedure_stats:


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:

• a unit test that executed a whole slew of procedures, filling up the cache

• an sp_configure change, which - for certain options - has a side effect of clearing the procedure cache

• a single query with a huge memory grant

• a manual DBCC FREEPROCCACHE;

• explicit recompiles or drop/re-create of procedures

• ...

StackExchange Database Administrators Q#117691, answer score: 8