DBCC FREEPROCCACHE

http://sqlserverperformance.wordpress.com/2009/12/28/fun-with-dbcc-freeproccache/

-- Example 1 (Sledgehammer)
-- Remove all elements from the plan cache for the entire instance 
DBCC FREEPROCCACHE;

-- Flush the cache and suppress the regular completion message
-- "DBCC execution completed. If DBCC printed error messages, contact your system administrator." 
DBCC FREEPROCCACHE WITH NO_INFOMSGS;


-- Example 2 (Ballpeen hammer)
-- Remove all elements from the plan cache for one database  
-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);


-- Example 3 (Scalpel)
-- Remove one plan from the cache
-- Get the plan handle for a cached plan
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C1

SQL server: Deciphering Wait resource

http://www.practicalsqldba.com/2012/04/sql-server-deciphering-wait-resource.html


SELECT 
 o.name AS TableName, 
i.name AS IndexName,
SCHEMA_NAME(o.schema_id) AS SchemaName
FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID 
JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID  AND p.index_id = i.index_id 
WHERE p.hobt_id = 72057594040811520


SELECT DB_NAME(68)

Select the database based on the output above statement. Then execute the below statement

DBCC traceon (3604)

GO
DBCC page (68, 1, 492478) --Database_id,file_id,page_id