My Favorite SSMS Shortcut (After Copy/Paste)

Photo by Abhishek Desai on Unsplash

If there’s one keyboard shortcut I use more than any other (with the exception of copy and paste) it would be the ALT + highlight multi-line edit/block selection shortcut.

Let’s take a look at three ways the ALT + highlight shortcut lets you work faster in SQL Server Management Studio.

1. Add results of one query into the IN() statement

Sometimes when writing an ad hoc query  you might want to take the results of one query and put them into an IN() statement of another query.

Sure, you can write a subquery to put into your IN() statement…but that’s too much work for a one-time use disposable query.

What you can do instead is:

  1. Copy your values of interest
  2. Paste them into your IN() statement
  3. Hold down the ALT key while dragging the mouse down in front of all of your pasted values
  4. Type a comma (see video above for an easier demonstration).

This allows you to quickly turn pasted values into a list suitable for an IN() statement.

2. Paste values into a template of commands

Sometimes you might have to do something repetitive, like insert a bunch of values into a table.  You could script out multiple value insertion with a UNION ALL and and INSERT INTO statement, but once again that’s a lot of work for a one-time query.

Instead you can copy the values you want to insert by highlighting them while holding down the ALT key then positioning the cursor on the first location where you want to paste the values.  Then once you paste, SSMS will automatically multiline paste the values.

Once again, watch the video above to see a demo – a picture (video!) is worth a thousand words.

3. Quickly modify multiple rows

Finally, you might have ran into a situation where you already have a set of queries written, but they all need to be slightly modified in order to work.  For example, maybe the table name changed and now you need to append a prefix to each table name.

While holding down the ALT key, you can simply drag in front of the text you want to prepend to and then start typing.

Voila! You just edited multiple lines in one go.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Does The Join Order of My Tables Matter?

Photo by pan xiaozhen on Unsplash

I had a great question submitted to me (thank you Brandman!) that I thought would make for a good blog post:

…I’ve been wondering if it really matters from a performance standpoint where I start my queries. For example, if I join from A-B-C, would I be better off starting at table B and then going to A & C?

The short answer: Yes.  And no.

More of a watcher than a reader?  Watch this week’s episode on YouTube!

Table join order matters for performance!

Disclaimer: For this post, I’m only going to be talking about INNER joins.  OUTER (LEFT, RIGHT, FULL, etc…) joins are a whole ‘nother animal that I’ll save for time.

Let’s use the following query from WideWorldImporters for our examples:

Note: with an INNER join, I normally would prefer putting my ‘USA’ filter in the WHERE clause, but for the rest of these examples it’ll be easier to have it part of the ON.

The key thing to notice is that we are joining  three tables – Orders, OrderLines, and StockItems – and that OrderLines is what we use to join between the other two tables.

We basically have two options for table join orders then – we can join Orders with OrderLines first and then join in StockItems, or we can join OrderLines and StockItems first and then join in Orders.

In terms of performance, it’s almost certain that the latter scenario (joining OrderLines with StockItems first) will be faster because StockItems will help us be more selective.

Selective?  Well you might notice that our StockItems table is small with only 227 rows.  It’s made even smaller by filtering on ‘USA’ which reduces it to only 8 rows.

Since the StockItems table has no duplicate rows (it’s a simple lookup table for product information) it is a great table to join with as early as possible since it will reduce the total number of rows getting passed around for the remainder of the query.

If we tried doing the Orders to OrderLines join first, we actually wouldn’t filter out any rows in our first step, cause our subsequent join to StockItems to be more slower (because more rows would have to be processed).

Basically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve.

So if the order that our tables are joined in makes a big difference for performance reasons, SQL Server follows the join order we define right?

SQL Server doesn’t let you choose the join order

SQL is a declarative language: you write code that specifies *what* data to get, not *how* to get it.

Basically, the SQL Server query optimizer takes your SQL query and decides on its own how it thinks it should get the data.

It does this by using precalculated statistics on your table sizes and data contents in order to be able to pick a “good enough” plan quickly.

So even if we rearrange the order of the tables in our FROM statement like this:

Or if we add parentheses:

Or even if we rewrite the tables into subqueries:

SQL Server will interpret and optimize our three separate queries (plus the original one from the top of the page) into the same exact execution plan:

Basically, no matter how we try to redefine the order of our tables in the FROM statement, SQL Server will still do what it thinks it’s best.

But what if SQL Server doesn’t know best?

The majority of the time I see SQL Server doing something inefficient with an execution plan it’s usually due to something wrong with statistics for that table/index.

Statistics are also a whole ‘nother topic for a whole ‘nother day (or month) of blog posts, so to not get too side tracked with this post, I’ll point you to Kimberly Tripp’s introductory blog post on the subject: https://www.sqlskills.com/blogs/kimberly/the-accidental-dba-day-15-of-30-statistics-maintenance/

The key thing to take away is that if SQL Server is generating an execution plan where the order of table joins doesn’t make sense check your statistics first because they are the root cause of many performance problems!

Forcing a join order

So you already checked to see if your statistics are the problem and exhausted all possibilities on that front.  SQL Server isn’t optimizing for the optimal table join order, so what can you do?

Row goals

If SQL Server isn’t behaving and I need to force a table join order, my preferred way is to do it via a TOP() command.

I learned this technique from watching Adam Machanic’s fantastic presentation on the subject and I highly recommend you watch it.

Since in our example query SQL Server is already joining the tables in the most efficient order, let’s force an inefficient join by joining Orders with OrderLines first.

Basically, we write a subquery around the tables we want to join together first and make sure to include a TOP clause. 

Including TOP forces SQL to perform the join between Orders and OrderLines first – inefficient in this example, but a great success in being able to control what SQL Server does.

This is my favorite way of forcing a join order because we get to inject control over the join order of two specific tables in this case (Orders and OrderLines) but SQL Server will still use its own judgement in how any remaining tables should be joined.

While forcing a join order is generally a bad idea (what happens if the underlying data changes in the future and your forced join no longer is the best option), in certain scenarios where its required the TOP technique will cause the least amount of performance problems (since SQL still gets to decide what happens with the rest of the tables).

The same can’t be said if using hints…

Query and join hints

Query and join hints will successfully force the order of the table joins in your query, however they have significant draw backs.

Let’s look at the FORCE ORDER query hint.  Adding it to your query will successfully force the table joins to occur in the order that they are listed:

Looking at the execution plan we can see that Orders and OrderLines were joined together first as expected:

The biggest drawback with the FORCE ORDER hint is that all tables in your query are going to have their join order forced (not evident in this example…but imagine we were joining 4 or 5 tables in total).

This makes your query incredibly fragile; if the underlying data changes in the future, you could be forcing multiple inefficient join orders.  Your query that you tuned with FORCE ORDER could go from running in seconds to minutes or hours.

The same problem exists with using a join hints:

Using the LOOP hint successfully forces our join order again, but once again the join order of all of our tables becomes fixed:

A join hint is probably the most fragile hint that forces table join order because not only is it forcing the join order, but it’s also forcing the algorithm used to perform the join.

In general, I only use query hints to force table join order as a temporary fix.

Maybe production has a problem and I need to get things running again; a query or join hint may be the quickest way to fix the immediate issue.  However, long term using the hint is probably a bad idea, so after the immediate fires are put out I will go back and try to determine the root cause of the performance problem.

Summary

  • Table join order matters for reducing the number of rows that the rest of the query needs to process.
  • By default SQL Server gives you no control over the join order – it uses statistics and the query optimizer to pick what it thinks is a good join order.
  • Most of the time, the query optimizer does a great job at picking efficient join orders.  When it doesn’t, the first thing I do is check to see the health of my statistics and figure out if it’s picking a sub-optimal plan because of that.
  • If I am in a special scenario and I truly do need to force a join order, I’ll use the TOP clause to force a join order since it only forces the order of a single join.
  • In an emergency “production-servers-are-on-fire” scenario, I might use a query or join hint to immediately fix a performance issue and go back to implement a better solution once things calm down.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

How to Search Stored Procedures and Ad-Hoc Queries

Photo by Louis Blythe on Unsplash

Have you ever wanted to find something that was referenced in the body of a SQL query?

Maybe you need to know what queries you will have to modify for an upcoming table rename.  Or maybe you want to see how many queries on your server are running  SELECT *

Below are two templates you can use to search across the text of SQL queries on your server.

Prefer learning by watching?  Check out this week’s post on YouTube.

1. Searching Stored Procedures, Functions, and Views

If the queries you are interested in are part of a stored procedure, function, or view, then you have to look no further than the  sys.sql_modules view.

This view stores the query text of every module in your database, along with a number of other properties.

You can use something like the following as a template for searching through the query texts of these database objects:

For example, I recently built a query for searching stored procedures and functions that might contain SQL injection vulnerabilities.

Using the starting template above, I added some filtering in the WHERE clause to limit my search to queries that follow common coding patterns that are vulnerable to SQL injection:

2. Searching Ad-Hoc SQL Queries

Searching across ad-hoc queries is a little tougher.  Unless you are proactively logging the query texts with extended events or some other tool, there is no way to definitively search every ad-hoc query text.

However, SQL Server does create (or reuse) an execution plan for each query that executes.  Most of those plans are then added to the execution plan cache.

Execution plans are eventually removed from the cache for various reasons, but while they exist we can easily search their contents, including searching through that plan’s query text.

As a starting point, you can use the following code to retrieve SQL query texts that are currently stored in the plan cache:

Although the template above searches for the query texts in our execution plans, you can also use it to search for other query plan elements, such as elements that indicate if you have non-sargable query.

I used this technique recently to search for ad-hoc queries that might be vulnerable to SQL injection.  I modified the template above to search the input parameter values instead of the query texts, flagging any values that look like they might have some injection code in them:

So while using this technique won’t allow you to search across 100% of ad-hoc queries, it should be able to search the ones that run most frequently and appear in your plan cache.

Intrigued by how I’m searching query texts for SQL injection vulnerabilities? Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group to learn about these queries and how protect yourself from SQL injection.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!