SQL Server: Top 10 Cached Queries

We have covered Dynamic Management views (DMV’s) many times earlier. In this article, we will see how to view the most cached statements on the server.

The view sys.dm_exec_cached_plans and function dm_exec_sql_text can be used for this purpose.

Let us execute the following query


select top 10
    db_name(sql_text.dbid) as db_name,
    cache_plan.objtype,
    sql_text.text ,
    cache_plan.usecounts
from
    sys.dm_exec_cached_plans as cache_plan
    cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
order by
    usecounts desc

Here are some important points to note about this query:

• The result is based on the descending order of the column ‘usecounts’ which is used to indicate the number of times the cached objects are used.
• The column ‘object type’ indicates whether the statement is of the type procedure,view, adhoc etc.
• The column text shows the exact statement which was cached.
• The column db_name is null for the cached types adhoc, prepared, etc

OUTPUT