Here is another useful Dynamic Management View (DMV) query to find out which cached query plans are executed most fequently.
SELECT TOP 50 qt.text, qs.execution_count, qs.total_elapsed_time/qs.execution_count AS ‘AvgElapsedTime’, qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 — This filters to a specific database
ORDER BY qs.execution_count DESC
The sys.dm_exec_query_stats view shows aggregate performance statistics for cached query plans. This means that if a stored procedure is edited and saved or if it is recompiled, it’s statistics will be cleared out and will start from scratch in the view. Also, like all DMV’s, the data is lost when SQL Server is restarted.
Bearing this in mind, what I like to do to get a more accurate snapshot is to run the query to get the initial results. Then, I build a script that explicitly recompiles the stored procedures that show up in the list from the intial query results, like this:
Until each of the top fifty Stored Procedures are in the script. Then I run the script to recompile all fifty SP’s, (which clears their performance statistics in the view). Now, after this somewhat tedious preparation, I can run the original query after a minute, five minutes, etc. to get more accurate counts of query execution counts.
One thing to keep in mind is that the execution times in the view are in microseconds, not milliseconds.