https://BrentOzar.com/go/parameterization

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 parametrized for the latest code/database to go into production.

But still the query is showing as a parametrization 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 force parametrization? (But the one that is explicitly parametrized 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...

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?

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 parametrized for the latest code/database to go into production.

But still the query is showing as a parametrization 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 force parametrization? (But the one that is explicitly parametrized 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...

https://BrentOzar.com/go/parameterization

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 parametrized for the latest code/database to go into production.

But still the query is showing as a parametrization 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 force parametrization? (But the one that is explicitly parametrized 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...