Average Density & cardinality estimation

Question

In a large web application we had a query that was running slow in code, that seemed to be working blazing fast in SSMS. After checking the basics like the same SET options, we found that there was one difference: SSMS used declare @variable1 and the code used an inline parameter.

I never though that would make a difference, but the microsoft docs gave the solution and even a short explanations.

The following queries are different. The first query uses Average Density from the histogram for cardinality estimation, while the second query uses the histogram step for cardinality estimation:

SQL

declare @variable1 = 123
select * from table where c1 = @variable1

SQL

select * from table where c1 = 123

Great, problem solved. But I have no idea what the explanation actually means. I've read up on cardinality estimation and understand that it's used to select that best query plan. But how does that change when using a variable or not?

• Is using a variable always better?
• If not, is there a better way to find out what to use other then "use one until it fails and check the other"?

This is the first time I really noticed this because it changed from <10ms to 10 seconds, but I'm wondering if other queries are affected by this, but just a lot less.

Answer 1

Is using a variable always better?

No, it is not. Using a variable will prevent SQL from creating an optimal plan, as SQL does not know the contents while creating the plan. As you found, it will create a plan for the ‘average’ metrics.

If not, is there a better way to find out what to use
Knowing your data will be a good start. For example, if you know you have negative parameter sniffing issues, and know the average will be good enough, you may want to use it (temporary) until you have found a better solution for the specific problem.