Which queries are cached with MSSQL

Microsoft SQL Server stored the most recently executed queries in cache. Which one? and how big are they?

Data used

First we will execute a couple of queries to fill the cache, then look at the cache.

USE sql911;
go

SELECT  YEAR(sales_date) AS 'Year', MONTH(sales_date) AS 'Month', SUM(sales_qty) AS "Units sold"
FROM dbo.sales
GROUP BY YEAR(sales_date), MONTH(sales_date)
ORDER BY YEAR(sales_date), MONTH(sales_date);
go

SELECT resource_address AS 'Resource Bottleneck',
       COUNT (*) AS '# of bottlenecks'
FROM sys.dm_os_waiting_tasks
WHERE resource_address <> 0
GROUP BY resource_address
ORDER BY '# of bottlenecks' DESC;
go

I am not showing the results, it's only to fill the query cache.

What's in the cache

The queries are stored in:

  1. sys.dm_exec_cached_plan
  2. sys.dm_exec_query_stats
SELECT sqlt.TEXT AS 'Cached query',
      qstat.execution_count AS '# of times',
      cplan.size_in_bytes AS 'Size',
      qstat.last_worker_time, qstat.max_worker_time, qstat.last_execution_time
FROM sys.dm_exec_cached_plans cplan
INNER join sys.dm_exec_query_stats qstat ON cplan.plan_handle = qstat.plan_handle
cross apply sys.dm_exec_sql_text(qstat.sql_handle) sqlt
ORDER BY qstat.last_execution_time DESC;
go
Cached query                                                                                                                                                                                                                                                     # of times           Size        last_worker_time     max_worker_time      last_execution_time
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ----------- -------------------- -------------------- -----------------------

select resource_address as 'Resource Bottleneck',
       count (*) as '# of bottlenecks'
from sys.dm_os_waiting_tasks
where resource_address <> 0
group by resource_address
order by '# of bottlenecks' desc;
                                           1                    40960       533                  533                  2007-07-21 20:55:08.640

SELECT  YEAR(sales_date) AS 'Year', MONTH(sales_date) AS 'Month', SUM(sales_qty) AS "Units sold"
FROM dbo.sales
GROUP BY YEAR(sales_date), MONTH(sales_date)
ORDER BY YEAR(sales_date), MONTH(sales_date);
                                                1                    49152       476                  476                  2007-07-21 20:55:08.623

(2 row(s) affected)
  1. Notice that the current query looking at the cache is NOT displayed.
  2. Only the first 256 characters of the cache query are displayed.

Clear the cache

DBCC FREEPROCCACHE;
go

SELECT sqlt.TEXT AS 'Cached query',
      qstat.execution_count AS '# of times',
      cplan.size_in_bytes AS 'Size',
      qstat.last_worker_time, qstat.max_worker_time, qstat.last_execution_time
FROM sys.dm_exec_cached_plans cplan
INNER join sys.dm_exec_query_stats qstat ON cplan.plan_handle = qstat.plan_handle
cross apply sys.dm_exec_sql_text(qstat.sql_handle) sqlt
ORDER BY qstat.last_execution_time DESC;
go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Cached query                                                                                                                                                                                                                                                     # of times           Size        last_worker_time     max_worker_time      last_execution_time
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ----------- -------------------- -------------------- -----------------------

(0 row(s) affected)

The cache has been cleared