It keeps me up at night

Out of the box, sp_BlitzCache will give you enough stuff to work on in your server’s plan cache to last a month of leap days.

This is great for most people, but if you use a ton of dynamic SQL in stored procedures, it can make things difficult.

What’s the frequency, BlitzCache?

The trouble with dynamic SQL in stored procedures is that when we sort by normal metrics like CPU, reads, duration, etc., all of the underlying dynamic statements accumulate towards the calling procedure.

It can make really analyzing your plan cache contents difficult. You open the plan for a stored procedure that creates and executes dynamic SQL, and there can be hundreds or thousands of variable declarations and assignments, string concatenations, etc.

These all generally show with 0% cost, and then you’ll have a few lookup queries get socked with the entire plan cost, even though they have close to 0 cost. They’re just higher than the actual 0 cost operations that litter the plan.

The Tip

If you want to ignore stored procedures and only look at statements in your plan cache, you’ve had a way to do that since, like, forever. Use the @QueryFilter parguablemeter.

Here’s what @Help will tell you about it.

Cool! How does it change things?

A quick glance at my servers where I was running a workload on our Orders database looks like this.

Just procedures:

Just statements:

I don’t have any dynamic SQL procs in my plan cache, but if I did, using the @QueryFilter = 'statement' variameterable will ignore stored procedures, and focus on statements.

This should get you the actual statements that you built up with dynamic SQL and executed, not just the stored procedures that built them up.

Thanks for reading!