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

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan