Posted on

Take the SQLChallenge: Tuning a Stored Procedure

I’ve just published a new SQLChallenge course for subscribers, and I think it’s one of the best ones yet.

Your mission is to:

  1. Identify which statement is slowing down our stored procedure the most
  2. Tune the code to speed it up. You can change the query that is slow as well as anything else in the procedure that will help you make that statement faster.

In the solution videos, I’ll step through multiple strategies to figure out which statement in the procedure is slowing it down the most – because in real life, you need to have a whole bag of tricks in different situations. 

Solution scripts and videos include demos of:

  • Trace flag 7412, how it impacts sp_WhoIsActive, and live query plans
  • Finding the slow query in Query Store – with TSQL as well as the graphic reports
  • Finding the slow query in the plan cache
  • Methods of rewriting the procedure to tune the query
  • Additional code patterns that make tuning procedures simpler.

The challenge script in the first lesson creates a database on your test instance – no need to restore anything — so it’s fast to get going.

Get started here.

Watch all the videos, or just what you need

The course has an hour and ten minutes of videos, each one of which is 15 minutes or less. 

Want to take SQLChallenges?

Pick the right product for you and get started:

Posted on

Employee Agreements & Contracts: Anti-Patterns (31 minute video)

When you take a new job in software engineering or in IT, within the paperwork there often lurks an employee agreement: a contract between you and your employer. In this half-hour live episode, I talk about why these contracts exist, and multiple anti-patterns you should avoid.

This is the first of a two part series. In the next episode, I’ll discuss best practices for understanding and negotiating the contract.

Want to listen on the go?

Subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

Video (31 minutes)

Slides from the talk

Posted on

Table Value Constructors in TSQL

Last week’s quiz was on Table Value Constructors in TSQL.

Essentially, table value constructors let you create a dataset on the fly. These can occasionally be useful in writing queries, but I think playing with them has another benefit: they provide a simple, lightweight framework to let you develop your ability to think in sets.

I wrote this quiz building up to question #4, which I think is a very fun and interesting pattern.

Let’s take a look at how folks did on the quiz, and see what table valued constructors can do.

Quiz Answers and Explanations

Question 1: A single Table Valued Constructor! How many rows will this query return?

SELECT *
FROM (VALUES 
   ('Spider Plant', 'Chlorophytum comosum', 'airplane plant'),
   ('Aloe Vera', 'Aloe vulgari', 'Burn Plant')
)as v(name1, name2, name3);
  • Answer: 2
  • Correct: 252 (86%) / Incorrect: 40 (14%)

The folks who got this incorrect mostly went for 3 — if you haven’t used these before, it’s unclear how those commas work!

Table value constructors allow you to specify multiple rows, each surrounded by (round parens). Look for those round parens to identify the rows.

Screenshot of the results from question 1

Question 2:  I’ve cross applied my TVC with another TVC. How many rows will this query return?

SELECT * 
FROM ( VALUES 
   ('Heart Leaf Philodendron'),
   ('Philodendron cordatum')
) as v(name1)
CROSS APPLY ( VALUES 
   ('green'),
   ('leafy')
) as v2(name1);
  • Answer: 4
  • Correct: 239 (82%) / Incorrect: 53 (18%)

We have two table value constructors, and each of them has one column and two rows.

If you look at the execution plan for this query, it takes the datasets and joins them with an INNER JOIN with no join predicate – in this case it’s as if we did a cross join, and got the cartesian product. Each dataset has two rows, so 2 x 2 = 4.

Screenshot of the results of question 2

Question 3:  Something changed in that APPLY. How many rows will this query return?

SELECT * 
FROM (VALUES 
   ('Succulents'), 
   ('Cacti') 
) as v(thing) 
CROSS APPLY (VALUES 
   (v.thing + ' are friends')
) as v2(thing);
  • Answer: 2
  • Correct: 220 (75%) / Incorrect: 72 (25%)

Whoo, the second table value constructor (v2) is doing something interesting this time: it’s referring to the dataset produced by the first table value constructor (v1), and concatenating on a literal value to the ‘thing’ column.

The results here have two rows: the second TV creates an additional column.

Note: you could get the exact same results using a SELECT to define v2 instead of a VALUES clause.

Screenshot of the results of question 3

Question 4:  Hmmmm… the CROSS APPLY got even more interesting. How many rows will this return?

SELECT v.plant, v2.faves 
FROM (VALUES 
   ('Ferns', 'being spritzed', 'shade', 'humidity'), 
   ('Succulents', 'well drained soil', 'to dry out a bit', 'brighter light')
) as v(plant, fav1, fav2, fav3)
CROSS APPLY ( VALUES 
   (fav1),
   (fav2),
   (fav3)
) as v2(faves);
  • Answer: 6
  • Correct: 202 (69%) / Incorrect: 90 (31%)

I find this to be a very interesting pattern: we are using a table value constructor to do an unpivot operation!

The first table value constructor (v), is a two row table with four columns: plant, fav1, fav2, and fav3.

The second table value constructor (v2), does something that seems almost impossible:

  • It refers to the fav1, fav2, and fav3 columns from (v)
  • It places all THREE of these columns into a single column named (faves)

This is allowed, and it does work.

The number of rows we get back is the number of values in fav1, fav2, and fav3 – there are two rows and three columns, so 2 x 3 = 6 rows.

Screenshot of the results from question 4

 

Posted on

Dear SQL DBA: Remembering Robert Davis, DBA Hero

What makes a person a DBA Hero? In this episode, I talk about how Robert Davis made a huge impact on the SQL Server community, and the traits that Robert displayed that I hope to emulate.

If you’d rather listen on the go, subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

Links from the video:

Posted on

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.

Posted on

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.

 

Posted on

CROSS APPLY in T-SQL: Why to APPLY

Why use APPLY?

Michelle asked a great question on the quiz page:

In your own words, why would one want to use a cross apply operator rather than a join operator? I’m old school, and I’m just not getting why a cross apply would be so much better to use than a join.

Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY:

  1. APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it’s still the #1 use.
  2. Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.
  3. And I also like it for queries that have a calculation that needs to be done and which is referenced in multiple columns in the query, or perhaps also in a predicate and the select. You can perform the computation once in the apply and then reference it multiple times. That way if you have to change the formula later on, you only have to change it in once place, plus I find it’s easier to read in some cases.

But it can do even more…

It can help you (un) pivot data. Check out Kenneth Fisher’s example here.

Brad Schulz’s post here has code samples for those, plus more things like showing how it can be useful for shredding XML: http://bradsruminations.blogspot.com/2011/04/t-sql-tuesday-017-it-slices-it-dices-it.html

Brad gets a bit jokey at the end of the post, but there’s a lot of valid uses along the way.

Want to take the quiz on APPLY?

Take the quiz here, then head back here to see how you compare to other folks who took the quiz– reported below.


Quiz Results/Answers/Explanations: CROSS APPLY

The questions in this quiz are based on two tables, created and populated with the following commands:

CREATE TABLE dbo.t1 (t1c1 int IDENTITY PRIMARY KEY); 
CREATE TABLE dbo.t2 (t2c1 int IDENTITY PRIMARY KEY, t1c1 int NOT NULL DEFAULT 'foo'); 
GO

INSERT dbo.t1 DEFAULT VALUES
GO 5
INSERT dbo.t2 (t1c1) SELECT TOP (2) t1c1 FROM dbo.t1;
GO

This produces two tables which look like this:

Question 1: If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

SELECT COUNT(*) AS ct 
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
     FROM dbo.t2 
     WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: The results of COUNT(*) based on an inner join between the two tables

  • Correct: 106 (42%)
  • Incorrect: 146 (58%)

I know, it’s weird, there’s a 1/0 in there which seems like it would produce a divide by zero error! But that bit of code never gets evaluated in this case, and the query runs successfully.

Sometimes a query using APPLY can be “transformed” into a join operation, and this is one of those cases. You can read more about this in Paul White’s article here.

Most of the time, if you want a join, you should use a join. However, it’s definitely useful to know that APPLY can be used this way for when you are reading and editing code written by others.

Question 2: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: yes

  • Correct: 140 (56%)
  • Incorrect: 112 (44%)

In this case, the second query will be rewritten to use an inner join, just like the first query. One method you can use to verify this is to look at the execution plan for both queries (and you can obviously run them and compare the results).

Both queries return a count of 2.

Here are the estimated plans for both queries:

Question 3: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
OUTER APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: no

  • Correct: 198 (79%)
  • Incorrect: 54 (21%)

The first query returns the count of 2. The second query returns a count of 5.

This is because the first query is implemented as an inner join, and the second query is implemented as an outer join.

Question 4: Will these queries return the same result?

SELECT t1c1 * 2 AS d
FROM dbo.t2;

SELECT x.d
FROM dbo.t2
CROSS APPLY (SELECT t1c1 * 2 AS d) AS x;

Answer: yes

  • Correct: 161 (64%)
  • Incorrect: 91 (36%)

This is an example of using a CROSS APPLY to compute a calculation.

It’s pretty trivial in this case, but I find this can be very useful in the case of complex queries where you refer to the result of a calculation more than once — doing this can allow you to only do the computation in once place and then refer to it many times. Not only can this make the query more readable sometimes, it can limit the errors if you need to change the calculations (because you’re changing it in fewer places.)