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.