Sql-server – sys.dm_exec_procedure_stats missing history

dmvsql serversql-server-2008-r2stored-procedures

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;

Best Answer

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

Related Solutions

Sql-server – sp_ExecuteSQL, performance and table variables

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T 
  ( 
     x INT 
  ) 

INSERT INTO #T 
            (x) 
SELECT TOP 10 number 
FROM   master..spt_values 

SELECT * 
FROM   #T 
UNION 
SELECT * 
FROM   #T 

DROP TABLE #T
'

SQL Server Execution Plan – TOP Operator Without TOP or ORDER BY

NOT EXISTS can introduce a TOP 1 (as can EXCEPT in some cases). This is because it only cares about the fact that at least one row exists. This is called short circuiting, and you want this - it is much more likely to speed up your query than slow it down. And I'd be really surprised if the TOP didn't show up in the plan for different date ranges, unless there was a substantial difference in row counts and a different plan was compiled. Maybe you could post the two .sqlplan files somewhere so that we could associate each plan with the query and help determine the reasons for any differences. With a screen shot and the query that allegedly goes with it, there's not a ton to go on.

I'm not convinced that the TOP in your case is making this query run for 5 minutes. Rather than just look at the plan, I'd actually investigate what the query is doing for 5 minutes, while it is running, by looking at sys.dm_exec_requests - is it blocking? What is the wait type? Since you're using a ##global temp table (why?), this could be due to blocking, but it's unclear from just a screen shot of a plan. Why doesn't your ##temp table have a clustered index? What other indexes does it have? The TOP would probably be more efficient if it could sort on something sensible other than the output of a full table scan. You could consider changing the subquery to be TOP (1) ... ORDER BY but adding a clustered index (or an index that covers and satisfies all the where clauses in the subquery) should accomplish something quite similar.

For more info on NOT EXISTS and alternatives, see this blog post.

Best Answer

Related Solutions

Sql-server – sp_ExecuteSQL, performance and table variables

SQL Server Execution Plan – TOP Operator Without TOP or ORDER BY

Related Question