/*============================================================================
  File:     CachingExamples.sql

  Summary:  This script shows how to clear cache, see what's in cache and find
		out more about DMVs. Use DMObjects.SQL to create the needed
		functions to access metadata about DMVs.

  Date:     November 2005

  Version:	SQL Server 2005
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

CHECKPOINT				--writes dirty pages to disk
DBCC FREEPROCCACHE		--clears entire plan cache
DBCC DROPCLEANBUFFERS	--clears all clean data cache pages

DECLARE @DBID	smallint
SELECT @DBID = db_id('pubs')
DBCC FLUSHPROCINDB(@DBID)	--clears all clean plan cache for specified database

-- Auto parameterized and cached...
SELECT * 
FROM titles 
WHERE price = $19.99

-- SQL Server 2000/2005
SELECT * FROM master.dbo.syscacheobjects
--    WHERE cacheobjtype = 'Compiled Plan'

-- To use the DMVs, you must be in 90 compatibility mode
-- sp_dbcmptlevel pubs, 90;

-- SQL Server 2005
SELECT cp.*, st.text 
FROM sys.dm_exec_cached_plans AS cp 
	CROSS APPLY sys.dm_exec_sql_text(plan_handle) st 
WHERE st.text like '%titles%'

-- Or even better, you can see number of executions:
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* 
FROM sys.dm_exec_query_stats AS qs 
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
	CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
ORDER BY qs.EXECUTION_COUNT DESC

-- So, will subsequent executions use it?
SELECT * 
FROM titles 
WHERE price = $19.99

-- But what if there's a different value?
SELECT * 
FROM titles 
WHERE price = $7.99 -- money

SELECT * 
FROM titles 
WHERE price = 7.99 -- still money?

SELECT * 
FROM titles 
WHERE price = 19.99 -- still the same?

-- Client application
-- Caching by specification, variable is explicitly typed
EXEC sp_executesql
	N'select * from titles where price = @price',
	N'@price money',
	19.99

EXEC sp_executesql
	N'select * from titles where price > @price',
	N'@price money',
	1099.99
