Demo for Parameter Sniffing and Memory Grant Feedback - SQL, Code, Coffee, Etc.

Demo for Parameter Sniffing and Memory Grant Feedback - SQL, Code, Coffee, Etc.

Disclaimer : The following is built with Claude Code. Just need to justify my $100/mo subscription cost. This was meant for a private demo so I can learn more about parameter sniffing and memory grant, but I decided to open it to public. And you may say "Marlon, this isn’t you, this is too good." Again, I will say it again, originally I was to keep this a private learning doc for me to understand Parameter sniffing. I asked Claude Code to build me a test for param sniffing and memory grant feedback. The following is the result of that after few prompts. You may use this for your own demo or POC. Run at your own risk. The Github Repo info is at the bottom of this post.

The stored procedure runs in milliseconds. It has been running in milliseconds for the last two years. Then one morning, it suddenly takes four minutes to complete. No code deployment. No configuration change. Nothing obvious changed. Restart the SQL Server instance, and it goes back to being fast — at least until later in the day.

You already know what the first response in the incident channel will be: "It’s parameter sniffing."

And technically, that answer may be correct. But it does not tell you enough to fix the problem.

Parameter sniffing is not a single issue. There are different ways this can bite you, but two of the most common ones are easy to confuse.

The first is a bad plan choice. SQL Server compiles a plan based on one set of parameter values, but that same plan performs poorly when reused for a different set of values. For example, SQL Server may choose an index seek with hundreds of thousands of key lookups when a scan would have been the better option.

The second is a bad memory grant. SQL Server estimates it only needs enough memory for a small number of rows, but the actual query returns hundreds of thousands of rows. The result can be spills to tempdb, poor performance, and unnecessary memory pressure.

These two problems can look similar from the outside, but they require different troubleshooting approaches.

This is especially important with newer SQL Server features like Memory Grant Feedback. It can help correct inaccurate memory grants, but it does not change a fundamentally bad plan choice. If you do not identify which problem you actually have, you can apply a fix that was never designed to solve the issue.

The goal of this post is to separate these two behaviors, show how they are different, and walk through a demo you can reproduce yourself.

Sniffing is a feature

Before getting into the problem, it is important to set the right context. A lot of discussions around parameter sniffing make it sound like a SQL Server defect that Microsoft should have fixed. That is not really the case.

When SQL Server compiles a parameterized query, it uses the parameter values that caused the compilation to estimate the number of rows and build a plan. It looks at the statistics, checks the histogram, estimates the expected cardinality, and creates a plan based on that information. That plan is then stored in cache and reused for future executions, even if those executions use very different parameter values.

The first part of this process is actually what makes SQL Server perform well. Without parameter sniffing, SQL Server would have to create plans based on generic estimates instead of the actual values being searched. You would end up with a plan that is average for everyone instead of a plan that is optimized for the majority of cases.

The problem is not parameter sniffing itself. The problem is reusing a plan when the data distribution does not match the values that plan was originally optimized for.

This is where data skew comes into play. If values in a column are evenly distributed, most parameter values will produce similar row counts, and the cached plan will usually work well. Parameter sniffing only becomes a problem when some values return a small number of rows while others return a significantly different number of rows.

So the first question should not be, "How do I disable parameter sniffing?"

The better question is, "How is the data distributed, and how much skew exists in this column?"

SQLSELECT TOP (20) CustomerID, Rows = COUNT_BIG(*)<br>FROM Sales.OrderLines_or_whatever<br>GROUP BY CustomerID<br>ORDER BY Rows DESC;<br>SELECT TOP (20) CustomerID, Rows = COUNT_BIG(*)<br>FROM Sales.OrderLines_or_whatever<br>GROUP BY CustomerID<br>ORDER BY Rows DESC;

If the difference between the highest and lowest values is only within an order of magnitude, then data skew is probably not your problem. There is likely something else causing the bad plan choice.

Building a demo; thanks, claude

I wanted to demonstrate both failure modes using actual query behavior, which means I needed data with a noticeable skew. The challenge is that standard sample databases are not...