July 27, 2011

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

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

sql-server-dmv-cache2


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

3 Responses to "SQL Server: Top 10 Cached Queries"
  1. about said...
    August 11, 2011 at 8:39 PM

    This is such a good query. I am using it right away. Good work

    -- Kachin

  2. Madhivanan said...
    August 11, 2011 at 11:56 PM

    Kachin, Thanks for the feedback

  3. about said...
    August 12, 2011 at 12:08 AM

    :) anytime! I love your blog.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions