SQL: Types of Cached Queries
Author: jason
Date: 2014-04-10 12:59:59
Category: Technical

Here is a great script from Kimberly Tripp / Paul Randal over at SQL Skills. It shows what types of queries are being run on your SQL Server.






Code

SELECT objtype AS [CacheType]
, COUNT_BIG(*) AS [Total Plans]
, SUM(CAST(size_in_bytes as DECIMAL(18,2)))/1024/1024 AS [Total MBs]
, AVG(usecounts) AS [Avg Use Count]
, SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
, SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END)
AS DECIMAL(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC


The Type of object value can be one of the following:
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

http://technet.microsoft.com/en-us/library/ms187404.aspx
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
http://www.sqlskills.com/blogs/kimberly/statement-execution-and-why-you-should-use-stored-procedures/
http://technet.microsoft.com/en-us/library/ms175528(v=sql.105).aspx




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009