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?”

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

SELECT   TOP (20) CustomerID, Rows = COUNT_BIG(*)
FROM     Sales.OrderLines_or_whatever
GROUP BY CustomerID
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 always useful for demonstrating these types of problems.

For this test, I used Claude Code to help create the setup script and build the test scenario. WideWorldImporters, Microsoft’s sample database, was used as the starting point because it contains real order data. However, the data distribution is fairly uniform. Order lines are spread across customers, dates, and stock items. That makes sense for a sample database, but it does not create the conditions needed to demonstrate parameter sensitivity.

The skew in this demo is intentional and documented. The script creates Demo.OrderLinesSkewed using WideWorldImporters’ approximately 231,000 existing order lines, then adds another 500,000 rows tied to a single customer. The goal is to create a simple scenario where one customer has a large percentage of the data while most other customers have significantly fewer rows.

The customer values are not hardcoded. The script identifies the large customer and the smaller customers from the data and prints them out. This makes the demo more portable because WideWorldImporters installations may not all contain identical data.

I prefer demos that are transparent about how the test conditions were created. The important part is not the test data itself. The important part is understanding the SQL Server behavior we are trying to demonstrate.

There are three important details in the setup that make this demo work. Each one represents a common reason why performance demos like this can produce misleading results:

The index is narrow on purpose. A single non-clustered index on CustomerID, no INCLUDE columns:

CREATE NONCLUSTERED INDEX IX_OrderLinesSkewed_CustomerID
    ON Demo.OrderLinesSkewed (CustomerID);

CREATE NONCLUSTERED INDEX IX_OrderLinesSkewed_CustomerID
    ON Demo.OrderLinesSkewed (CustomerID);

TThe goal is to force SQL Server to make a real choice. It can either use the index and perform key lookups for each row, or decide that scanning the clustered index is the better option. Where SQL Server draws that line is the plan shape side of the problem.

If the index is covering, that decision goes away. You may still see a memory grant issue, but you will not see the plan change between executions. The result is a demo that only shows one side of the problem and misses how parameter sensitivity can affect plan selection.

The rows are intentionally wide. There is a char(200) filler column, and the stored procedure includes that column in the output. Memory grants are calculated using estimated rows multiplied by estimated row width. If the rows are too narrow, the memory grant behavior is not very interesting.

There is also no TOP and no ROW_NUMBER() in this demo. This is an important detail because many demos around this topic accidentally hide the memory grant problem.

For example, a query like SELECT TOP (50) ... ORDER BY UnitPrice DESC introduces a Top N Sort. The memory grant for a Top N Sort is based on the number of rows being returned, in this case 50, instead of the total number of rows flowing through the sort. Filtering a ROW_NUMBER() value against a constant can have a similar issue because the optimizer may rewrite it into a Top.

In both cases, the memory grant no longer scales with the actual workload. The demo may still run, but it is no longer showing the behavior we are trying to analyze.

If you build your own version of this test, check the execution plan and make sure the operator is a Sort and not a Top N Sort. The demo scripts capture the plan after each execution and flag Top N Sort because it changes the behavior being tested.

Here’s the procedure. It is deliberately boring:

CREATE OR ALTER PROCEDURE Demo.usp_CustomerLinesByPrice
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  ol.OrderLineID, ol.OrderID, ol.CustomerID, ol.StockItemID,
            ol.Description, ol.Quantity, ol.UnitPrice, ol.OrderDate,
            ol.Filler
    FROM    Demo.OrderLinesSkewed AS ol
    WHERE   ol.CustomerID = @CustomerID
    ORDER BY ol.UnitPrice DESC, ol.Description;
END

CREATE OR ALTER PROCEDURE Demo.usp_CustomerLinesByPrice
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  ol.OrderLineID, ol.OrderID, ol.CustomerID, ol.StockItemID,
            ol.Description, ol.Quantity, ol.UnitPrice, ol.OrderDate,
            ol.Filler
    FROM    Demo.OrderLinesSkewed AS ol
    WHERE   ol.CustomerID = @CustomerID
    ORDER BY ol.UnitPrice DESC, ol.Description;
END

The test is simple by design. One equality predicate against a skewed column. One sort operation where no index can fully support it. Those two things are enough to reproduce the behavior we want to analyze.

Failure mode one: sniff small, run big

Compile the procedure for the minnow. Then call it for the whale.

EXEC sys.sp_recompile N'Demo.usp_CustomerLinesByPrice';
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Minnow;  -- compiles here
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Whale;   -- suffers here

EXEC sys.sp_recompile N'Demo.usp_CustomerLinesByPrice';
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Minnow;  -- compiles here
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Whale;   -- suffers here

The plan compiled for the minnow is a good plan for the minnow: seek the non-clustered index, look up the handful of matching rows in the clustered index, sort them in a memory grant barely above the minimum. For a few hundred rows that’s exactly right.

Then the whale arrives, and the same plan does it 500000 times.

Two separate things have now gone wrong, and from here on I’m going to insist on naming them separately.

The plan shape is wrong. Key lookups are fine in the hundreds and catastrophic in the hundreds of thousands. The logical read count tells the story: 1519167 reads to return 500000 rows. A clustered index scan would have read the table roughly once.

The memory grant is wrong, and this is the part people find surprising. The grant is not recalculated per execution. It is baked into the cached plan at compile time, computed from the estimated row count and the estimated row width. Runtime reality does not get a vote. So the sort gets a grant sized for the minnow — 1 MB — while the engine’s own after-the-fact assessment of what it should have had is 0.53 MB.

When a sort doesn’t have enough memory, it spills to tempdb. Not a warning, not a retry — it writes sort runs to disk and merges them, and your query goes from memory-speed to disk-speed while holding its locks the whole time. In the actual execution plan you’ll see a warning triangle on the Sort operator. In the Extended Events output you’ll see sort_warning fire.

The gap between GrantMB and IdealMB is the fingerprint. Learn to read it.

Failure mode two: sniff big, run small

Now the mirror image, which most write-ups skip, and which is the more interesting half.

EXEC sys.sp_recompile N'Demo.usp_CustomerLinesByPrice';
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Whale;   -- compiles here
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Minnow;  -- wastes memory here

EXEC sys.sp_recompile N'Demo.usp_CustomerLinesByPrice';
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Whale;   -- compiles here
EXEC Demo.usp_CustomerLinesByPrice @CustomerID = @Minnow;  -- wastes memory here

The plan compiled for the whale is a clustered index scan with a memory grant sized for half a million wide rows. Reused for the minnow, it returns a few hundred rows and finishes quickly.

Nothing spills. Nothing is slow. This query will never appear in your “top ten by duration” report. It is not broken in any way a duration-based monitor can see.

It is, however, greedy. It asked for 127.31 MB of workspace memory and touched 0.22 MB of it.

Here’s why you should care about memory a query didn’t use:

• A memory grant is reserved for the lifetime of the query, used or not. It is not lazily allocated and it is not shared.
• Workspace memory is a finite, instance-wide pool. There is only so much of it.
• When the pool is exhausted, incoming queries queue on RESOURCE_SEMAPHORE waits — they sit there, having compiled successfully, waiting for permission to start.

So one procedure with a badly sniffed grant, called from enough sessions concurrently, will stall queries that have nothing to do with it. The victim is never the culprit. That’s what makes this one hard to trace back, and it’s why duration is a bad detector for half of all parameter sniffing problems.

The detector that does work is a comparison, not a threshold:

SELECT  qs.execution_count,
        GrantMB = qs.last_grant_kb      / 1024.0,
        UsedMB  = qs.last_used_grant_kb / 1024.0,
        IdealMB = qs.last_ideal_grant_kb/ 1024.0,
        st.text
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE   qs.last_grant_kb > 1024
  AND   qs.last_grant_kb > qs.last_used_grant_kb * 2
ORDER BY qs.last_grant_kb DESC;

SELECT  qs.execution_count,
        GrantMB = qs.last_grant_kb      / 1024.0,
        UsedMB  = qs.last_used_grant_kb / 1024.0,
        IdealMB = qs.last_ideal_grant_kb/ 1024.0,
        st.text
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE   qs.last_grant_kb > 1024
  AND   qs.last_grant_kb > qs.last_used_grant_kb * 2
ORDER BY qs.last_grant_kb DESC;

Granted far above used means memory reserved and wasted. Ideal far above granted means the query spilled. Same three columns, two different diseases.

Two problems, not one

This is the pivot of the whole post, so here it is in one table:

Everything below refers back to this.

OPTION (RECOMPILE)

The blunt instrument, and the one that always works.

    ...
    ORDER BY ol.UnitPrice DESC, ol.Description
    OPTION (RECOMPILE);

    ...
    ORDER BY ol.UnitPrice DESC, ol.Description
    OPTION (RECOMPILE);

Both columns of that table go green. The plan is built against the actual parameter value on every single execution, so the shape is right and the grant is right, every time, by construction. You get a bonus, too: because the value is a literal constant at compile time, the optimizer can do things it can’t do for a cached plan — fold constants, eliminate whole branches of a query, simplify predicates it would otherwise have to keep general.

In the demo, scenario D calls the recompiling version with the minnow, then the whale, then the minnow again, and the grant tracks the actual row count in both directions: 1, 127.35, 127.34 MB.

Now the cost, stated honestly, because “just add RECOMPILE” is bad advice delivered confidently.

You pay a compilation on every execution. Compilation is CPU-expensive. On a procedure called four thousand times a minute, you have traded an intermittent memory problem for a permanent CPU problem, and the second one is harder to notice because it doesn’t spike — it just raises your floor.

You lose the plan cache as a diagnostic surface. After a recompiling statement runs, there’s nothing in sys.dm_exec_cached_plans to look at. Your monitoring gets thinner exactly where you were having trouble.

Statement-level, not procedure-level. OPTION (RECOMPILE) on one statement recompiles that statement. CREATE PROCEDURE ... WITH RECOMPILE recompiles every statement in the procedure on every call, including the nine that were fine. It is almost never what you want. If you inherited a procedure with WITH RECOMPILE in the header, that’s usually someone’s decade-old shotgun fix for a problem in one statement.

The rule of thumb worth internalizing: RECOMPILE is priced per execution. A reporting procedure called forty times an hour should probably just use it and stop thinking about this. A hot OLTP path called forty times a second should not.

And note something for later: after scenario D there is no cached plan at all. Hold that thought.

Memory grant feedback

Now the feature everyone wants to talk about, and the one whose limits are routinely oversold.

Memory grant feedback is a learning loop. After a query executes, the engine compares the memory it granted against the memory the query actually used. If the query spilled, the grant was too small — write a bigger number onto the cached plan for next time. If the grant was more than twice what was used, it was too big — write a smaller one. Over a handful of executions the grant converges on reality without anybody recompiling anything.

What you need for it:

That compatibility level column is where most people get stuck. A database restored from an older instance keeps its old compatibility level forever; WideWorldImporters ships at 130. You can be running SQL Server 2022 and getting none of this.

Scenario C in the demo sets the loop up to succeed: compile for the minnow, then call the whale six times in a row with nothing recompiling in between. That last part matters. Feedback is written onto the cached plan, so anything that evicts the plan throws away everything the engine learned. This is also why the adjustment always lands on the following execution — execution n discovers the grant was wrong, execution n+1 benefits.

The trajectory:

That State column is IsMemoryGrantFeedbackAdjusted from the cached plan’s XML, and it’s the cleanest way to watch the loop work: it moves from NoFirstExecution through YesAdjusting to YesStable.

Now the three caveats, which are the actual reason this section exists.

It fixes the grant. It never fixes the plan shape. Look at the PlanShape column across all six of those executions in the demo output. It does not change. It cannot change — memory grant feedback adjusts a number attached to an existing plan; it does not trigger a recompilation and it has no opinion about operators. Those 1519167 logical reads from failure mode one are still there on execution six. The query stops spilling and gets faster. It does not get good. If you go into this expecting feedback to solve parameter sniffing, this is where you’ll be disappointed, and it won’t be the feature’s fault.

It’s a learning loop, so somebody has to do the learning. The first caller always eats the bad grant. On SQL Server 2019 and earlier, so does the first caller after any cache eviction — a plan flush, memory pressure, a stats update, a failover. SQL Server 2022’s Query Store persistence exists precisely to stop throwing that lesson away, and it’s a good reason to have Query Store on.

It gives up if you make it thrash. A workload that genuinely alternates between tiny and enormous will push the grant up, then down, then up again. Rather than oscillate forever, the engine notices the instability and switches feedback off for that query. There’s an Extended Event for it — memory_grant_feedback_loop_disabled. Percentile grant feedback in SQL Server 2022 is the answer to this case: instead of chasing the last execution, it sizes the grant from a percentile of recent executions, which is far more stable across a genuinely bimodal workload.

Why RECOMPILE and memory grant feedback don’t combine

This falls straight out of the two sections above, and it’s the question that sent me down this path in the first place.

Memory grant feedback writes its correction onto a cached plan. OPTION (RECOMPILE) doesn’t leave a cached plan. There is nothing for the feedback to attach to.

You can watch this in the demo: after scenario D runs three times, query the cached plan view and you get nothing back. Compare with scenario C, where the plan is sitting right there accumulating adjustments.

This is not a conflict you need to resolve, and it isn’t a bug. RECOMPILE already produces an accurate grant on every execution by construction — there’s nothing left for a feedback loop to improve. But it does mean the two are alternatives, not layers. Don’t reach for RECOMPILE while imagining that feedback is also working quietly underneath, and don’t diagnose the absence of feedback on a recompiling statement as something being broken.

Parameter Sensitive Plan optimization

Which leaves the gap in that table from earlier: memory grant feedback never fixes plan shape, and RECOMPILE fixes plan shape but charges you per execution. Is there anything that fixes the shape without the compile?

On SQL Server 2022, yes. Parameter Sensitive Plan optimization caches multiple plan variants for a single statement and dispatches between them based on the cardinality the incoming parameter implies. The minnow gets the seek-and-lookup plan, the whale gets the scan, neither one triggers a compilation, and both come out of cache.

It’s on by default at compatibility level 160. Its limits are worth knowing: equality predicates only, at most three of them, and the column has to be skewed enough for the engine to consider it worth the trouble — PSPO is not applied to every parameterized query, only to ones where the optimizer sees a genuine sensitivity.

The most convincing thing I can say about PSPO is not an argument, it’s a confession about the demo: the setup script has to turn PSPO off. On a 2022 instance at compatibility level 160, scenarios A and B don’t fail. The engine handles them. I had to explicitly disable the feature to show you the classic behaviour at all:

ALTER DATABASE SCOPED CONFIGURATION
    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

ALTER DATABASE SCOPED CONFIGURATION
    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Scenario E turns it back on so you can see the contrast: same procedure, same two parameters, no recompilation between them, two different plan shapes.

Things that look like fixes

A tour of the mitigations you’ll find in older Stack Overflow answers, and what they actually cost.

OPTIMIZE FOR UNKNOWN. Compiles using the density vector — the average rows per distinct value — instead of the histogram. You’ve traded a plan that is excellent for most callers and terrible for a few, for a plan that is mediocre for everyone. Sometimes that really is the right trade, especially when the terrible case is bad enough to cause an outage. But it should be a decision, not a reflex.

OPTIMIZE FOR (@CustomerID = 12345). You’ve pinned the plan to a magic constant. It works until the data distribution moves, at which point it fails silently and nobody remembers that number is in there.

Assigning parameters to local variables. The folk-remedy version of OPTIMIZE FOR UNKNOWN — the optimizer can’t sniff a local variable, so you get the density estimate. Same trade-off, but now it’s invisible, and the next developer will “clean up” the pointless variable assignment and reintroduce the bug.

Updating statistics, or rebuilding the index. This evicts plans, so the symptom goes away, so it looks like a fix. It will look like a fix again next week, and the week after, forever. This is the single most common way a parameter sniffing problem survives for years: it is permanently one maintenance job away from being invisible.

Restarting SQL Server. Same mechanism, more downtime, and it usually happens at 3am while someone types “resolved” into a ticket.

Splitting the procedure into branches — an IF that routes big customers to one procedure and small ones to another, so each gets its own plan. This one genuinely works. It also hardcodes today’s understanding of your data into control flow, and it ages badly. Reach for it when PSPO isn’t available and RECOMPILE is too expensive, and leave a comment explaining why.

So what should you actually do?

1. Confirm the column is skewed. Group by the predicate column, compare the top and bottom. Within an order of magnitude? It isn’t parameter sniffing. Go look somewhere else.
2. Work out which problem you have. Compare last_grant_kb, last_used_grant_kb, and last_ideal_grant_kb against last_logical_reads. Wrong grant, wrong shape, or both.
3. Grant only, on 2019 or later, with steady traffic — check your compatibility level is 150+ and let memory grant feedback handle it. Turn on Query Store if you’re on 2022, so the lesson survives an eviction.
4. Shape wrong, on 2022 — check whether PSPO is on before you write any code. You may not have a problem.
5. Shape wrong, low call rate — OPTION (RECOMPILE) on the statement. Measure the compile cost afterwards rather than assuming it’s fine.
6. Shape wrong, high call rate, no PSPO available — branch the procedure, and write down why in a comment, because in three years the reason will not be obvious.

The thing I’d most like you to take away is the second step. Almost everything written about parameter sniffing collapses the two failure modes into one story, and once you’ve separated them the modern features stop looking mysterious. Memory grant feedback isn’t under-delivering — it’s doing exactly the one job it claims to do, and PSPO is the feature that does the other one.

Run it yourself

Download the sql files from my repo (see link below):

01-setup.sql     run once, builds the skewed table and the procedure
02-demo.sql      run the whole file, all five scenarios, records its own evidence
03-cleanup.sql   restores everything it changed

Two things before you do. Turn on Query Options → Results → Grid → Discard results after execution in SSMS, because the procedure returns half a million wide rows about ten times over and you want to be timing the server rather than the grid. And know that 01-setup.sql raises your database compatibility level and turns PSPO off — both are recorded before they’re changed and restored by the cleanup script, but point it at a scratch instance, not production.

The demo captures every measurement into Demo.DemoResults as it goes, so you don’t have to sit and read execution plans between executions. The summary at the bottom flags spills and wasted grants for you.

Dowload the demo scripts from my github repo.

The post Demo for Parameter Sniffing and Memory Grant Feedback first appeared on SQL, Code, Coffee, Etc..

#temp tables can be useful in so many ways. I hadn’t really given them much thought beyond using them when a situation called for it, until I saw the invitation for T-SQL Tuesday #201 Invitation: Temp Tables, Friend or Foe? This month’s T-SQL Tuesday is hosted by Jeff Taylor (b).

For this post, I needed to do some research because there is more to #temp tables than simply creating one and using it. As I started digging into the topic, I realized there are a lot of discussions around #temp tables and @table variables, especially around when one might be a better choice over the other.

One of the common arguments is that #temp tables go to disk while @table variables stay in memory. Another argument is that this is a myth. That got me curious about what the actual differences are and when they really matter. Discussions like this almost always starts with where the data is stored (disk vs memory).

Both #temp tables and @table variables use tempdb. Both allocate pages that are managed by the buffer pool. Depending on the size of the data, memory availability, and other factors, those pages may or may not ever be written to disk. If that’s the case, then maybe “memory versus disk” isn’t really the question we should be asking.

Let’s take a look at a couple of scenarios.

Test SQL: SQL Server 2022 CU25 (16.0.4255.1) container (Orbstack on Macbook pro)
CPU: 8 (schedulers)
MAXDOP: 0
Cost Threshold for Parallelism: Default 5 (for parallelism test scenario)

The difference is statistics

One of the biggest differences between #temp tables and @table variables is statistics. #temp tables can have statistics created and maintained by SQL Server, which gives the optimizer more information when generating an execution plan. @table variables do not have column statistics, although newer versions of SQL Server have improved their cardinality estimates through deferred compilation.

#temp tables can have statistics. Those statistics include histograms that help the optimizer understand how the data is distributed and make better estimates.

A table variable does not get column statistics. Even with newer versions of SQL Server and improvements like deferred compilation, the optimizer still does not have the same level of information that it has with a #temp table.

This is where many of the differences start to show up. Things like indexes, constraints, recompiles, and parallelism are often related to the fact that a #temp table is a temporary table with metadata that the optimizer can use, while a table variable has different behavior.

The rule I am starting to use from this research is simple:

Use a #temp table when the optimizer needs more information about the data. Use a table variable when the amount of data is small, the usage is simple, or you specifically need the behavior that a table variable provides.

Let’s take a look at a simple test.

I wanted to keep this as fair as possible. Both objects have the same structure, the same data, and the same nonclustered index. All the test scripts can be found in the Github repo (see link at the bottom of this post).

CREATE TABLE #temptable 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

DECLARE @tablevariable TABLE 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

CREATE TABLE #temptable 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

DECLARE @tablevariable TABLE 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

The data is intentionally skewed. grp = 1 has 9,000 rows, while grp = 2 has only 10 rows.

When I checked the estimated row counts, the difference was obvious.

The #temp table had statistics, so the optimizer had information about the data distribution. The estimates were close to the actual row counts.

The table variable was different. Even though it had the same index, the optimizer did not have the same information available. The index gave it something to seek on, but it did not tell the optimizer how the data was distributed.

That distinction matters because estimates influence the rest of the execution plan. Row estimates affect join choices, memory grants, and other optimizer decisions. If SQL Server estimates 100 rows but the query actually returns 9,000 rows, the optimizer may choose a plan that isn’t optimal for the actual workload. That can also lead to memory spills to tempdb if the memory grant is too small.

One thing I noticed while testing this is that the estimate for a table variable is not always the same. For example, removing the index can change the estimate. The important part is that the optimizer still has limited information about the data inside a table variable.

So what about SQL Server 2019 and table variable deferred compilation?

Deferred compilation helps because SQL Server can see the table variable row count before compiling the statement. This improves cardinality estimates in many scenarios.

However, it does not create statistics. Knowing that a table variable has 10,000 rows is different from knowing how those 10,000 rows are distributed. A row count can help, but it does not replace a histogram.

Table variables migh have a problem with parallelism

Note: It is quite complicated to test paralellism on my test environment. Some tests results returned “inconclusive”. Your mileage varies (depending on your config basically).

I also looked at how #temp tables and @table variables behave when it comes to parallelism.

A common statement is: “@table variables are always serial.” That may not be completely accurate. But again, I have not tested this extensively. FYI.

The restriction is related to modifying the table variable, not reading from it. For example, a regular SELECT or a query that reads from a table variable can still use parallelism. The limitation shows up when SQL Server is inserting into or modifying the table variable. I need to test this with bigger workload when I get the chance.

Again, this is not conclusive.

Download the test scripts to see how this looks in your environment. Link to my Github repo is at the bottom of this post.

The impact really depends on how much data you are working with.

If you are inserting a small number of rows into a table variable and reading from it later, the lack of parallelism during the insert probably does not matter. The difference between a serial and parallel insert for a small dataset is not something you will likely notice.

Where this becomes important is when you start loading a large amount of data. A table variable that is used as a staging area for thousands or millions of rows can limit the insert operation to a serial plan when a #temp table could take advantage of parallelism.

For small amounts of data, use whichever option makes the code easier to understand. For larger data loads, a #temp table is usually worth considering.

The test scenario used in this post covers only a couple of the differences between #temp tables and @table variables. If you want to experiment with the scenarios yourself, you can grab the test scripts from my GitHub repository and run them in your own environment.

https://github.com/MarlonRibunal/sqlserver-demos/tree/main/tempdb

The post When Do You Reach For A #temp Table? first appeared on SQL, Code, Coffee, Etc..

And how do you even implement the principle of least privilege on the instance and database level?

Well, we’re lucky that the U.S. Department of Defense has provided a Security Technical Implementation Guide (STIG) for SQL Server, along with many other technologies. It gives you a well-established security baseline that you can use to evaluate your SQL Server environment, whether you’re working with an on-prem deployment, a virtual machine, or even a lab environment. Even if you’re not in a government-regulated organization, the STIG is still a practical reference for identifying security gaps and strengthening your SQL Server configuration.

You need two things: the SQL Server 2022 STIG and the STIG viewer.

Download the STIG file

Download the guide here. Search for SQL Server and select Microsoft SQL Server 2022 STIG. Click the Download button to, well, download the zipped STIG file.

You don’t have to extract the zip file. We’ll load the whole zip file on the viewer in the next step.

Install the STIG viewer

Download the STIG viewer here: https://www.cyber.mil/stigs/srg-stig-tools/. Download the msi installer called STIG Viewer 3.7.0-Win64 msi. Extract the installer package. Unfortunately, macOS is not supported. This is a Windows-only app.

Click Yes to allow the app to make changes to your device.

There is no wizard steps to follow. Upon clicking Yes, the STIG Viewer should be installed right away.

Load the STIG file

To load the STIG zip file (the first zip file we downloaded above) in the viewer, click the Open button in the STIG Viewer section (top panel). That will load both the STIG for SQL Server Instance and Database in the viewer.

You may want to add STIG to the Library so you don’t have to reload the STIG documentation each time. You can simply add the whole zip file.

If the STIG docs will not automatically appear after they are added in the library, close the app and open it again.

You can always go to the Dashboard by clicking the Home buttom at the upper right-hand navigation menu. Click the STIG Viewer button to open the STIG docs.

You can now then view the rules contained in the STIG docs. Select the Microsoft SQL Server 2022 Intance to view the rules for the instance (and Database for the database-level checks).

We will create our custom Checklist in our follow up post. We’ll also customize it so you can have a version of this STIG for your organization.

The post SQL Server Security Hardening Guide Using the DoD STIG Checklist first appeared on SQL, Code, Coffee, Etc..

So, probably instead of establishing a rigid step-by-step process, putting together a repeatable methodology can lead you to a resolution. This will eliminate blind guesses and at least bring you to an educated guess if the issue is too complicated.

A common workflow for troubleshooting performance issues on SQL Server may involve the following. But again, these are not necessarily phases or stages that you have to follow in a specific order. Real-world troubleshooting rarely happens in a perfect sequence. You may jump between these areas, revisit previous findings, or investigate multiple things simultaneously depending on the complexity of the issue.

1. Define the scope
2. Determine what changed
3. Measure server health
4. Identify bottlenecks
5. Investigate the root cause
6. Validate the fix / Monitor after implementation
7. Document the incident

Scope it first

Every time I get a call about slow performance, the first thing I want to do is qualify the question itself. What does slow mean? Is it a widget that used to run for a few seconds and is now running for five minutes? By the way, this is one of the most common “slow” incidents I’ve seen in my last job at an ISV.

Scoping and discovery are probably the most critical parts of any troubleshooting task. This is the part where you can grasp the scope and nature of the issue at hand. Here, you will probably be able to establish your footing on how to approach the issue. Of course, a batch job running in SSIS or SQL Server Agent will require a different approach than, say, a process triggered by a user in the application’s frontend.

And, again, as I’ve said in my other post, I think it’s important to know when this issue started occurring. That alone might reveal a few important details about the very nature of the issue. Knowing whether you’re dealing with a single bad query or a server-wide issue will prevent you from investigating the wrong layer of the stack. For all you know, it’s not a performance issue at all, but a slowdown caused by the user’s ISP while working from home. I’ve seen this many times before.

My typical goal at the onset is to reproduce or replicate the issue in a test environment. That can give you the most bang for your buck because it allows you to investigate the issue and test potential solutions in a controlled environment.

The one thing we’re trying to prevent in the scoping is getting down the proverbial rabbit hole or chasing that red herring. Yes, I still love using those idioms in real life.

What changed?

Actually, this should be part of scoping, but I think it merits a whole section of its own. Knowing what changed could hold critical information as to the reason behind the issue. Maybe somebody just tweaked some lever on the application side, and that kind of caused a domino effect of some sort.

The main goal here is to find all the possible root causes and not point fingers at whoever might have been involved. There are a ton of reasons why a SQL Server might suddenly run slow. Configuration changes, new procedures, introduction of new indexes, unanticipated workload, network I/O, and even user workflows can cause slowdowns given the right circumstances where SQL Server can really slow down.

I’d start with the most recent change. And, of course, the typical answer to “What changed?” is almost always “nothing.” So, it’s up to you to determine how you surface what actually changed, if anything.

Know your baseline

If nothing has changed at all, then the next step is to determine any other activities that could be affecting the particular issue that you are investigating. It’s possible that the slowdown is caused by another task that is not necessarily related to the slowdown issue but is somehow affecting, for example, server resources.

I’ve seen cases where a third party started to send an amount of data that was not typical for an intra-day workload, which caused a domino effect on normal business operations. Not knowing what a “typical intra-day workload” looks like can complicate your investigation because you wouldn’t know if things are a factor in the issue or not.

For example, high CPU isn’t automatically a problem if the server normally runs at 90% CPU during month-end processing. While a query that normally consumes an infinitesimal amount of CPU and is suddenly taking 30% of the total CPU is almost certainly an issue. Always compare current metrics against a known baseline before concluding that something is abnormal.

Paul can tell you where it hurts

If you haven’t identified a possible root cause or figured out a narrative that may possibly connect the dots at this point, then it’s time to dig deeper. Paul Randal’s wait stats query can surface some clues about the ongoing issue. If you are troubleshooting a live issue, you probably want to combine that with Paul’s waiting task query. Glenn Berry’s Diagnostic queries are a must-have tool for any SQL Server DBA. If you are like me, when I encounter slow issues that are happening live, my instinct is to run Adam Machanic’s sp_whoisactive procedure. For many others running Brent O’s sp_BlitzFirst procedure is their go-to.

Wait stats and waiting tasks can be a superpower when it comes to troubleshooting a performance issue. Here is an example of how I used these two in the past to troubleshoot a live performance issue.

The wait type typically tells you where to go next. For example, blocking and lock waits send you to the head blocker (sp_WhoIsActive does a great job at identifying this). PAGEIOLATCH sends you toward storage I/O issues or queries performing large reads, possibly due to missing indexes. RESOURCE_SEMAPHORE points you toward memory grant pressure, while SOS_SCHEDULER_YIELD points toward CPU pressure. CXCONSUMER can be a benign wait type, but you should still investigate whether it is contributing to a bottleneck in a highly concurrent system.

Wait stats can help you identify the type of bottleneck SQL Server is experiencing, whether it’s related to I/O, CPU pressure, memory pressure, locking, or other resource contention.

Resource utilization

Ok, just like with wait stats, if you need to dig deeper, you should look into system resources as well. There is only so much you can achieve by optimizing indexes and queries. At some point, you need to ask whether the server has enough resources to handle the workload it is being asked to process. A perfectly tuned query can still struggle if the underlying infrastructure is already under pressure.

Here’s what I had to say about Disk IO on a LinkedIn post:

The key here is understanding whether you are dealing with a query problem, a workload problem, or a capacity problem. You can only go so far with index tuning and query optimization if the real issue is resource pressure underneath.

At some point, you have to understand what SQL Server is waiting on and whether the current infrastructure can support the workload. This is where troubleshooting becomes less about applying fixes and more about understanding the behavior of the system as a whole.

Bottleneck at the query level

We’re at the point where we are ruling out server-wide issues and closing in on specific queries that may be contributing to the problem. This is where tools like Query Store become extremely valuable because they allow you to look back at query behavior over time instead of relying only on what is happening at the exact moment of the incident.

One thing that I have been trying to learn lately is Query Store. At the time of writing, I am taking the SQLskills course on Query Store, IEQS. The more I learn about it, the more I realize how useful it can be when troubleshooting query performance issues, especially when dealing with plan regressions or queries that suddenly start behaving differently.

Of course, actual execution plans can give you a lot of information about where the bottleneck is occurring at the query level. Is it a cardinality estimation issue, memory grant pressure, spills, missing indexes, outdated statistics, or even parameter sniffing? The information you can glean from execution plans or Query Store can help you understand why a query is behaving the way it is and guide you toward the right tuning approach.

Is it blocking

If it’s a blocking chain, the first thing you want to do is find the head blocker and understand what it is doing before you start killing sessions. A blocked session is usually just a victim waiting for something else to finish. The real question is: what is holding the lock, and why is it taking so long?

This is where experience and judgment come into play. Just because you found a blocking session does not mean the immediate answer is to kill it. You need to understand what the session is doing, how long it has been running, and what impact terminating it will have. One interesting case I encountered in the past is a Service Broker queue that got stuck in limbo.

Communicate to stakeholders

During an incident, people understand that troubleshooting takes time. What they don’t want is uncertainty and silence while the issue is impacting the business. Keeping stakeholders informed shows that you are engaged, even when the root cause has not been identified yet.

In my experience, you don’t always have to provide a breakthrough every time you communicate. Sometimes a simple update like “we are seeing high I/O waits, we are checking storage latency, and we are looking at the queries involved” is enough to keep everyone aligned. The technical investigation may still be in progress, but at least everyone understands where things stand.

Validate the fix

Once the fix has been implemented, the work is not done yet. This is where you go back and validate if the change actually fixed the problem. Did the waits decrease? Did CPU return to its normal behavior? Did response times improve? Did the blocking clear? And probably the most important question: did the users notice the improvement?

One thing I always try to avoid is assuming that the issue is fixed just because the immediate symptom disappeared. A SQL Server environment is a complex system, and sometimes fixing one problem can expose another one. Keep monitoring after the change and make sure that things continue to behave as expected.

I’ve seen cases where a change looked successful during the initial troubleshooting session, only to find out later that the original problem came back or another issue surfaced. This is why validation and monitoring after the change are just as important as finding the fix in the first place.

Document the incident

Document what happened, what you checked, and what fixed the issue. Include the root cause, symptoms, timeline, diagnostics, fix, and any lessons learned. The next time a similar incident happens, this documentation can save you from starting the investigation from scratch.

Effective SQL Server troubleshooting is less about having the perfect fix ready and more about knowing how to narrow down the possibilities. You start with what you know, gather evidence, validate your assumptions, and slowly eliminate what doesn’t fit.

In my experience, the DBAs who troubleshoot well are not the ones who have memorized every possible solution. They are the ones who know how to ask the right questions, understand what the system is telling them, and make changes based on evidence. Having a repeatable methodology will take you much further than a collection of random tuning techniques.

Well, take it with a grain of salt. It depends. It always is.

The post How I Troubleshoot a Slow SQL Server Live first appeared on SQL, Code, Coffee, Etc..

Provision an Azure SQL Database

Go to you Azure Portal and search for Azure SQL Database

On the upper left-hand of the UI, click on Create and select SQL database (Free offer).

Configuring an Azure SQL Database is pretty much intutive. For the Server option, use an existing SQL Database Server or create a new one.

Click Review + create to finish the setup.

Connect from VS Code on a MacBook

Go to your Resource Group and find your Azure SQL Database. Or, you can simply search for Azure SQL Database in the search bar again and that will take you to your databases.

Copy the Server name.

Now, open your VS Code (install the mssql extension if you haven’t already). Why VS Code? That’s because Mirosoft will never port SSMS to macOS. That’s why.

Create new connection. Look for the plug icon with the ‘+’ sign next to it.

For the Input type, Browse Azure wouldn’t work for me even if I already took care of the networking setting. Let me know in the comment if you made it work. Using Parameters worked for me.

Paste the Server name. Don’t forget to tick the Trust server certificate. Use SQL login and input the sa user and password that you set when you provisioned your SQL Server.

That should be it. Your Azure SQL Database is now ready to use.

The post How to Provision an Azure SQL Database first appeared on SQL, Code, Coffee, Etc..