The other day my brother sends me this blog link and asks what I think about it:

https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database

I answer back to him with a recent story from another one of my customers.

The issue here is that people write posts on analyzing memory structures without ever addressing the potential side effects of them. In this case, scanning the database cache looking for errors, missing indexes, scans, etc…

Just like with anything, there is no free lunch. As much upside as there is to looking through the cache, here is the downside: depending on the size of the cache being analyzed– the DBA risks being the biggest problem on the entire system.

Here is an example of what a DBA did at one of my customers. They scraped a script off some website and just started running it in the middle of the business day looking for parallelism in plan caches:

-- Collect parallel plan information
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO master.dbo.PlanCacheForMaxDop
SELECT
 query_plan AS CompleteQueryPlan,
 n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
 n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
 n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
 n.query('.') AS ParallelSubTreeXML,
 ecp.usecounts,
 ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

What he didn’t understand was that each time he ran it, about 600GB of reads was being pushed through the CPU (as seen via Profiler):

 
Month-Day   Hour        Bytes Per Query
----------- ----------- ----------------------------- 
1-6         16          589,001,506,816.00 
1-8         19          622,114,177,024.00 

While in itself, there’s nothing wrong with looking at the caches, just be aware of the classic Heisenberg Principle: You can’t monitor something without disturbing it.

In this case, his monitoring was actually worse than the queries he was looking for.