Resolving sp_Blitz Finding: Many Plans for One Query

Question

Why Multiple Plans for One Query Are Bad

So I was running sp_Blitz to get a handle on some systems. Got some code to get cleaned up as usual. Some heaps that should have been clustered indexes. etc.

This particular query was using literals, seemed to be resulting in a lot of plans. Got the indexing and stuff sorted out on the tables in this query, and even got the query parameterized for the latest code/database to go into production.

But still the query is showing as a parameterization issue. (DBA helpfully turned the plan cache query into an SSRS report so I can just run through them quickly on the PROD environment from my browser).

Where to go from here? Ignore it? (Seems like a lot of plan counts)

Use forced parameterization? (But the one that is explicitly parameterized is showing up).

Ugh - I see the developer didn't take my advice that the LEFT JOIN was being turned into an INNER JOIN... I'll have to get that fixed...

Without forced parameterization, will all these literal-style queries never share the same plan? Is this query too complicated for simple parameterization?

Answer 1

_{Community wiki answer:}

If the query is now parameterized, you should be able to ignore all the ones that still show constant literals - those are in the plan cache now, but they'll age out over time.

In addition to the outer join that's not really an outer join anymore, is SELECT * required, or is it possible there is a skinnier pair of indexes that could cover the actual output requirements (usually some subset of *)? You should also encourage your developers to always use the schema prefix e.g. schema.object.

Simple parameterization only applies for trivial plans.

Also consider optimize for ad hoc workloads. Kimberly Tripp has some good guidance on cleaning the cache. Think of forced parameterization as a last resort, because it will affect all your queries, with a high risk of performance regressions.