How to cause a simple spill to tempdb

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 int);

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 T10
CROSS JOIN Ten T100
CROSS JOIN Ten T1000
CROSS JOIN Ten T10000
CROSS JOIN Ten T100000
CROSS JOIN Ten T1000000;

select 0
FROM @foo
ORDER BY i DESC

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).

A quick screencap of the issue turned into an animated gif

SQL Operations Studio: Keyboard Shortcuts, Actual Plans, & More

Last week I posted a quiz on SQL Operations Studio, a free, multi-platform tool from Microsoft.

This tool is under active development and the features are improving by the day — which makes it a great time to start trying out the tool and see what you like: because you can suggest changes!

Personally, I find that one of the things like I like to learn first with a tool is how to get around more easily using keyboard shortcuts. As soon as I set up keyboard shortcuts and color themes, I feel more at home in a client.

So, let’s make ourselves at home!


Here’s the answers to the quiz, and an overview of how folks did

Question 1: How can you change the keyboard shortcuts in SQL Ops Studio?

Correct answers:

  • Preferences -> Keyboard Shortcuts
  • The SSMS Keymap extension

Correct: 48 (30%) / Incorrect: 113 (70%)

Most of the folks who got that one incorrect didn’t know about the SSMS Keymap Extension. It’s definitely worth checking out, because makes things much easier for some shortcuts that are trickier to map, like enabling actual execution plans.

Opening up extensions in SQL Ops Studio
Opening up extensions in SQL Ops Studio

Question 2: To toggle a BLOCK comment, the built-in shortcut is…

Answer: Shift+Alt+A

  • Correct: 43 (27%)
  • Incorrect: 118 (73%)

I think a lot of folks who use SSMS regularly and don’t use VSCode may not know what I meant by the question, because SSMS doesn’t have this functionality (or if it does, I’ve never figured out the shortcut!)

Here is one version of togging a block comment in action around existing text.

Toggling a block comment in SQL Operations Studio
Toggling a block comment in SQL Operations Studio

It can also be very nice to insert a block comment and then type in the middle of it right after inserting the brackets!

If it’s possible to fall in love with a keyboard shortcut, I am in love with this one.

Question 3: To add a LINE comment, the built-in shortcut is…

Answer:  Ctrl+K Ctrl+C (windows) / ⌘K ⌘C (mac)
Mnemonic: Control Kansas City (nonsensical, but works for me! Thanks Merrill!)

  • Correct: 83 (52%)
  • Incorrect: 78 (48%)

Some things are the same as in Management Studio! At least for Windows folks.

For folks, like me, who use a mac, the default shortcut for this uses the Command Key. It is re-mappable if you don’t feel like retraining yourself when you switch between clients.

Question 4: To see a searchable list of your keyboard shortcuts…

Answer: Ctrl+K Ctrl+S (windows) / ⌘K ⌘S (mac)
Mnemonic: Control Keyboard Shortcuts

  • Correct: 48 (30%)
  • Incorrect: 113 (70%)

Especially when you’re first getting started, you’ll be asking a lot: is there a shortcut for x? You may want to map a shortcut where there isn’t one, or change a mapping. That makes this a useful screen to toggle quickly.

Question 5: Can you get ‘actual’ execution plans in SQL Ops Studio?

Answers:

  • Yes, via the command palette
  • Yes, via a custom keyboard shortcut

This was news to most people!

  • Correct: 18 (11%)
  • Incorrect: 143 (89%)

There’s currently more than one way to get an actual execution plan.

The awesome easy way is to install the SSMS Keymap Extension (pictured above). It maps the “Run Current Query with Actual Plans” command (which doesn’t have an option in the GUI as of today) to the CTRL+m shortcut.

If you don’t feel like using the extension, you can open the Command Palette (Ctrl+Shift+P (windows) / ⌘+Shift+P (mac)) and use the option “Run Current Query with Actual Plans”

Using the command palette to run a query with actual plans
Using the command palette to run a query with actual plans

Notes:

  • Highlight the query you want to run first, and then use the shortcut (if you’re using the extension) or the command palette command. It runs the query.
  • Getting estimated and actual plans for multiple queries is still a bit tricky (I filed this issue).

BONUS: Does SQL Operations Studio have a dark theme?

Answer: Yes, several

  • Correct: 78 (48%)
  • Incorrect: 83 (52%)

I know some of you folks out there love dark themes, so rejoice: you can take your pick. You can also customize them! In my last post, I wrote about customizing my highlight color and included links to lots of shortcut references.

Haven’t tried it yet? Give it a go!

While I still switch into SSMS for many tasks, I personally am finding that SQL Ops Studio is a super nice place to write code – so I’m using it as much as I can. Here’s that download link again.

Changing Your Highlight Color in SQL Operations Studio

I’ve been enjoying working with SQL Operations Studio lately – a new, free, cross-platform editor from Microsoft.  The tool is under active development and it’s a great time to try it out and suggest changes for how you’d like to see the product work. (Here’s a suggestion I made this week, for example.)

tldr; use workbench.colorCustomizations

To change the highlight color quickly to bright yellow, open your user settings file, add code like you see in one of the samples below, and save it.

Note that a bright yellow highlight color will not work well with themes that put some font in white – configuring editor.selectionForeground currently only works with high contrast themes (issue).

Because of that current limitation, I like a bright yellow highlight specifically with the “Light SQL Operations Studio” theme:

/* Yellow highlight, works well with Light SQL Operations Studio: */
    "workbench.colorCustomizations": {
        "editor.selectionBackground": "#fffb00",
        "editor.inactiveSelectionBackground": "#fffb007e", 
        "editor.selectionHighlightBackground": "#fffb007e"
    },

The change will take place as soon as you save the settings file.

For the “Dark SQL Operations Studio” theme, which I really like, I find that a purple highlight renders very well:

/* Purple highlight, works well with Dark SQL Operations Studio: */
"workbench.colorCustomizations": {
    "editor.selectionBackground": "#ae00ff86",
    "editor.inactiveSelectionBackground": "#ae00ff41", 
    "editor.selectionHighlightBackground": "#ae00ff41"
}

Highlighting code is important to me

One of my major uses of SQL Ops studio will be demonstrating code in webcasts and videos, so it’s important to me to be able to set a high-contrast highlight for lines of code.

SQL Ops Studio is based on VSCode, which is very flexible, so I suspected there was a way to do this already. Also, there is a lot of documentation out there on VSCode already, so I searched on “VSCode change highlight color” to help find my way to the solution.

But I figured that lots of folks starting out with SQL Ops Studio may not know that, and that this post might be a good introduction to how to change things like this – as well as how to find things by searching for “vscode”!

Changing the highlight and testing themes

Some handy shortcuts shown in this animation:

  • Opening preferences to edit: ctrl+, (windows) / cmd+, (mac)
  • Toggle block comment: shift+option+a
  • Saving file: ctrl+s (windows) / cmd+s (mac)
  • Viewing/changing color themes: ctrl+k ctrl+t (windows) / cmd+k cmd+t (mac)

Shortcut references: windows, mac

Here’s what it looks like to change the highlight to bright yellow, and to preview it in different themes:

Note that you don’t truly see how the highlight will be implemented until you fully select a theme (preview mode is slightly dimmed), and that the highlight color persists across different themes (which I think is awesome).

This screencap was taken from a mac, using Camtasia screencap to capture the keystrokes.