Cahced Plans - Using System Tables

SQL Server stores query plan in cache for each query run on the server in sys.dm_exec_cached_plans system table. We can get information of query text, query plan , memory used, and count of query execution.



Following query will give us info for the query text, object type , refcounts, usercounts for cached queries.


SELECT Db_name(t.dbid) db_name,

t.TEXT,

cacheobjtype,

objtype,

p.refcounts,

p.usecounts

FROM sys.dm_exec_cached_plans p

CROSS APPLY sys.Dm_exec_sql_text(p.plan_handle) t

WHERE dbid = Db_id()

ORDER BY cacheobjtype



  • We can find which query is used most by usecounts column.

  • We can find which plan is referred most

  • We can find query type by objtype column

It has following values


Value Definition

=========================================
Proc - Stored procedure

Prepared - Prepared statement

Adhoc - Ad hoc query

ReplProc - Replication-filter-procedure

Trigger - Trigger

View - View

Default - Default

UsrTab - User table

SysTab - System table

Check - CHECK constraint

Rule - Rule

If its adhoc then we should consider to make it prepared, because adhoc is taking valuable space of ram which we can reduce if we can make it prepared.

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts