Posted on

DevOps Terms – and Why They Matter to Database Specialists

In this 20 minute session, I define scrum, continuous deployment, test driven development, DevOps, and related concepts. I close with a quick discussion of why Database Administrators and Developers should care about DevOps.

Prefer 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#

If you enjoy Dear SQL DBA, reviews  on iTunes help me out a ton.

Slides from the presentation

Posted on

Employee Agreements & Contracts: Best Practices (23 minute video)

So you’ve got an employee agreement in front of you: now what? In this  episode, I talk about practical steps you should take to make sure that you understand the terms of your contract, and how to potentially negotiate the terms.

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

Slides from the talk

Posted on

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
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: