Sometimes it’s useful to know how to cause a problem.

Maybe you’ve never encountered the problem, and want to get hands-on experience. Maybe you’re testing a monitoring tool, and want to see if a condition flags an alert. Maybe you’re testing out a new client tool, and want to see how it displays it.

I recently was going through some demos in SQL Operations Studio, and I found that a spill on a sort operator wasn’t causing a warning to visibly show in the graphic execution plan.

I wanted to file an issue on this and let the Ops Studio team know that would be helpful - but my demo code was somewhat complex and required restoring a rather large database. So I set up a quick code sample to cause a spill that could be run in any database.

What’s the Easiest Way to Cause a Spill on a Sort Operator?

Populate a non-indexed table variable with a bunch of rows, then query the table variable and order the output. Ordering the output will require a sort, and since table variables don’t support column statistics, SQL Server won’t allocate enough memory for the sort.

Voila, Super Quick Sample Code…

DECLARE
    @foo TABLE
    (
        i integer
    );

WITH
    Ten
(
    N
) AS
(
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
    UNION ALL
    SELECT
        1
)
INSERT
    @foo
SELECT
    T1000000.N
FROM Ten AS T10
CROSS JOIN Ten AS T100
CROSS JOIN Ten AS T1000
CROSS JOIN Ten AS T10000
CROSS JOIN Ten AS T100000
CROSS JOIN Ten AS T1000000;

SELECT
    0
FROM @foo AS f
ORDER BY
    f.i DESC;
GO

Credit: This code is a simple adaptation of Paul White’s answer on this StackExchange question.

And Here’s What the Spill Looks Like in Action

Here’s a quick view of what the spills look like in SQL Server Management Studio, and then in Operations Studio.

In Operations Studio, you can currently only see one actual plan at a time (I’ve got an issue filed for that here), and there’s no little warning on the sort operator, although you can see the details of the spill in the tooltip (I commented on this issue to potentially reactivate it).