Search content within the blog

Friday, July 29, 2011

Sql Server : Top 10 cached queries.

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

sql-server-dmv-cache
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

No comments:

Post a Comment