Configuring SQL Server for Optimization - Microsoft SQL Server 2012 Performance Tuning - page 401

sys.dm_exec_sql_text(plan_handle) AS ST
WHERE
CP.usecounts > 0
GO

After clearing the cache in step 2, here is the result of the query we ran in step 3:

How to do it...

Follow the steps given here to perform this recipe:

1. Execute the following query.

SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID=43659
GO

2. Check whether anything come in plan cache for query run above or not. After clearing

the cache, it was the first time we executed the preceding query:
SELECT
CP.usecounts AS CountOfQueryExecution
,CP.cacheobjtype AS CacheObjectType
,CP.objtype AS ObjectType
,ST.text AS QueryText
FROM
sys.dm_exec_cached_plans AS CP
CROSS APPLY
sys.dm_exec_sql_text(plan_handle) AS ST
WHERE
CP.usecounts > 0
AND CP.cacheobjtype='Compiled Plan'
AND ST.text LIKE 'SELECT * FROM Sales.SalesOrderDetail WHERE
SalesOrderID=43659%'
GO

You can find a long running query in the Activity Monitor tool.
Use the Ctrl + Alt + A keyboard shortcut or the standard toolbar
from SSMS to open Activity Monitor.