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.