Brent McCracken

18 January 2017

• SQL Prompt
• SQL Code Snippets

Faster SQL Server troubleshooting using SQL Prompt snippets

A good DBA will automate repetitive tasks, but sometimes you need to troubleshoot a problem ‘live’, with people standing over you, and the pressure on. This is something that can’t be automated. SQL Prompt snippets have helped me out in this sort of situation numerous times.

Built-in SSMS query shortcuts

SSMS provides a few shortcuts via keyboard function keys, which we can use to call a stored procedure, for example, or execute a query on a manually-chosen target object.

To find the shortcut keys, open up the option menu in SSMS (Tools | Options) and then in the left-hand navigation pane, go to Environment | Keyboard | Query Shortcuts.

Figure 1

As you can see, I’ve used all the available shortcut key slots to call a number of useful built-in or custom stored procedures, as well as management commands.

This is great, but sometimes I need to pull up and then customize a common troubleshooting query. For example, if I’m troubleshooting a slow query I’ll often need to query the plan cache. I don’t want to have to spend time retrieving that query, or worse trying to type it in from scratch, especially if my boss is staring over my shoulder.

This is where SQL Prompt snippets come in very handy.

Using SQL Prompt snippets

A SQL Prompt snippet is essentially a pre-defined block of code, with a meaningful name, such as af (for the ALTER FUNCTION snippet). Having defined the code for the snippet, we can insert that code into our SSMS query window just by typing in the name of the snippet.

You can find Snippet Manager… under the SQL Prompt menu in SSMS. Open it up and you’ll see immediately that SQL Prompt provides a host of built-in snippets, which we can add to our queries using the indicated snippet name.

Figure 2

We can edit these snippets as required, as well as create and edit an unlimited number of our own snippets. By default, SQL Prompt saves all snippets to a local folder, as shown in Figure 2, but we could instead have this set to a network location where your whole team can access the snippets. This also means we don’t risk losing the snippets if we, for example, upgrade the PC.

If you’re new to SQL Prompt, start by trying out a few of these built-in snippets. You’ll see that some of them use placeholders, which allow us to quickly customize a script for a specific purpose. For example, take a look at the cdb (CREATE DATABASE) snippet. Alistair Daw provides a good, general overview of how to use and mange snippets and the placeholders within them. Steve Jones also shows how to save, customize, and share frequently used SQL snippets in his short video tip.

A custom snippet to query the plan cache

Often, I will need to query the plan cache when there is an issue with one of the queries. Rather than typing in the whole query every time, I created a snippet called ‘cache’.

Creating a snippet is easy. First, write out the code for the snippet in SSMS. Listing 1 shows the code for the cache snippet (formatted using SQL Prompt of course), in this example looking for particular text within a stored procedure.

Listing 1

I include OPTION (RECOMPILE) in my query so that the optimizer doesn’t place the query plan for this query in the plan cache.

Next, open up SQL Prompt Snippet Manager, and on the Snippets screen, select New. In the Edit Snippet screen, paste in your code and give the snippet a memorable name and a description. Notice that in the snippet, I’ve created a placeholder called search_text, which I did simply by encapsulating the name of the placeholder within two dollar signs, $search_text$, as shown in Figure 3.

Figure 3

Now, whenever I type ‘cache’ (without the quotes) and hit the Enter key, SQL Prompt inserts my snippet exactly as I created it, in the identical format and layout, even if it is in the middle of a line of code. It will also highlight the placeholder and place the cursor at the start, ready for you to type in the search criteria.

Figure 4

Summary

Snippets are one of my favorite time-saving features of SQL Prompt and I use them all the time. I find myself typing in the shortcut name for a snippet, even when I’m using a different computer, which doesn’t have SQL Prompt installed.

SQL Prompt is free to try for 28 days, so try it for yourself and, for more time-saving tips using SQL Prompt, check out the Super SQL Tips video library.