Live query execution plans

Live query execution plans

This is new in the preview of SQL Server Management Studio 2016, available for download now, and available for playing-around at the TechNet Virtual Labs. It even works when you’re connected to SQL Server 2014 – but not 2012, because it relies on DMVs that were only shipped with 2014.

It’s so cute to watch it working on multiple indexes at once:

A couple of operators

A couple of operators

And the bigger your plans get, the more fun it is to watch. I’m only showing 600-pixel wide images here though to make your blog reading easier, but here it is working with multiple statements in a batch:


Works with multiple statements in a batch

It’s not perfect – if SQL Server believes only a few rows will come back, but its estimates are wrong, then the operator will keep showing 100%. Here’s an example:

Live plan with incorrect statistics

Live plan with incorrect statistics

The top right operator starts first, and SQL Server only expects 1 row for this clustered index scan. As it turns out, there’s more data – note that the line leaving the scan keeps moving, indicating we’re still getting data.

Interestingly, the clustered index seek immediately underneath it keeps changing its time numbers. It’s as if SQL Server Management Studio is saying, “Okay, we started this now and – wait, no, hang on, not yet, now. Okay now we’re – no, maybe not yet.”

The catch (because you knew there was a catch)

Great news – it even works when connected to a server running SQL Server 2014, as long as you’re using the CTP version of SQL Server Management Studio 2016!

Bad news – you can’t just view someone else’s live query plan. The live query stats plumbing has to be turned on before the query starts.

Here’s how to start that plumbing:

  • In SSMS vNext, before you run a query, click Query, Include Live Query Statistics
  • In any SSMS, before you run a query, run SET STATISTICS XML ON or SET STATISTICS PROFILE ON
  • Start an Extended Events session and enable the query_post_execution_showplan extended event. For example, use sp_BlitzTrace™ with @TraceExecutionPlansAndKillMyPerformance = 1. Danger: this enables live query stats on all sessions, and as you can guess by how Kendra named the parameter, there’s going to be a big performance hit for that on a busy server. (There’s a Connect request about that already.)

I know. You want the capability to get there from something like sp_WhoIsActive, but it’s not there. Look, it can’t be all candy and roses. They have to save something for the next version.

2 comments ↑ Back to top

If your application can sometimes connect to SQL Server – and sometimes not – it can be really tough to troubleshoot. If SQL Server doesn’t even hear the call, it can’t log any errors.

Here’s the questions I ask to get to the root cause:

When it happens, does it happen to all applications? For example, do you have monitoring tools pointed at the SQL Server, and are they able to consistently connect to SQL Server even when the problem is happening?

Does it happen to all application servers? If you have several app or web servers, are they all affected? (If you’ve only got one, now is an excellent time to set up another one for troubleshooting, and balance the load between them.)

Are all queries in the application affected, or just some queries? Sometimes I see long-running queries keep right on going, but only new connections are affected.

Sometimes, your SQL Server just goes on vacation.

Sometimes, your SQL Server just goes on vacation.

Are there any errors logged in the SQL Server or application servers? In one case, we saw that all of the application servers lost network connectivity at the same time, on a regular basis. Turns out there was a bad switch involved.

Is there a pattern to the days/times of the timeouts? Start writing them down or documenting when they happen. For example, in one case, we saw that the days/times were exactly correlated to the security team’s regularly scheduled port scans.

During the timeouts, is the app server able to ping the SQL Server? When all else failed with one troubleshooting triage, we put a free network monitoring tool on the app server to ping the SQL Server every 10 seconds. Sure enough, the next time the app had query timeouts, we were able to prove that even pings weren’t working – thereby ruling out a SQL problem.

Ask those questions, and sometimes you don’t even have to troubleshoot SQL Server at all – the answers tell the whole story.

3 comments ↑ Back to top
Do you have the key?

Do you have the key?

Do foreign keys hurt the performance of inserts? Okay, we all know that foreign keys do require some work, but the crazy people of the internet have wildly varying guesses as to just how much work is involved. Estimates varied from “barely any” to “as much as 50% slower”.

I figured that you were going to do the work, so I might as well do it.

How did the test work?

I created two tables – parent and child. The parent table has an int primary key and a fixed width filler column. I varied the size of the filler column across multiple test runs, just to make sure I wasn’t biasing the test by using an artificially large parent table. The testing happened with a parent table that had 2 rows per page, 4 rows per page, and 8 rows per page.

You can find the test code in fk.sql.

Testing SQL Server insert speed with foreign keys

The test code inserts 1,000,000 rows in batches of 5,000 rows. Although this isn’t strictly realistic, it’s better than timing a single batch of 1,000,000 inserts.

What happens during the test?

It turns out that no matter what the page size it takes approximately 40ms to insert 5,000 rows with the foreign key in place. When we remove the foreign key, inserting 5,000 rows takes ~26ms.

Although the difference looks big at 5,000 row batch sizes, each insert is taking, on average, 0.0072ms with the foreign key and 0.0052ms without the foreign key. These differences are hardly worth noting. And, let’s face it, 2 microseconds is a pretty minor price to pay for data integrity.

How much do foreign keys affect single row inserts?

I ran the test one last time with a batch size of 1 row, just to see how much difference there was in insert performance.

FK present? duration
yes 253,896 ms
no 241,195 ms

When it comes down to single row performance, the difference is neglible. We’re spending all of our time waiting for other parts of the system.

How real is this test?

It’s not terribly real, but it’s real enough to say “Yes, foreign keys have overhead” and follow that up with “No, you shouldn’t care, just add the keys.” The tests were done in VMware and the hardware in question is mid-2012 Retina MacBook Pro.

In the real world, we’ve got a lot more than inserts happening, but it’s worth quantifying the cost of a foreign key lookup and realizing that it’s worth having foreign keys.

Kendra says: Wanna know if your foreign keys are ‘trusted’? Check out our script here.

25 comments ↑ Back to top
How did I ever get this to work????

How did I ever get this to work????

Some people hate repetition. They want to avoid manual tasks at all costs.

I used to be part of the cult of automation. I thought that everything should be made efficient. Why should I do something twice, if I can figure out how to do it once and then just run a script or push a button?

I don’t feel that way anymore about a lot of things.

One thing I do often is write scripts to reproduce problems or demonstrate how something works. Very often, there’s pretty similar setup steps in these scripts. I’ve written the following code hundreds of times:

/* Recreate the test database with prejudice */
USE master;
IF DB_ID ('IAMNotAProductionDatabase') IS NOT NULL
     DROP DATABASE IAMNotAProductionDatabase;

CREATE DATABASE IAMNotAProductionDatabase;

It’s often useful to back up a demo database and restore it to avoid a long setup process. So I also frequently retype commands like this:

BACKUP DATABASE IAMNotAProductionDatabase TO DISK=N'S:\Backup\IAMNotAProductionDatabase.bak' WITH COMPRESSION, INIT;

/* I like to do this in a separate step because I am neurotic like that */

I could use snippets in SQL Server Management Studio to reduce the typing. I could use a third party tool which auto-completes statements.

But I don’t. I re-type it every time. I was slow at first, but I’m much faster at it now.

These are commands I care about. Sure, they’re simple, they’re basic, they’re nothing fancy. But they’re really core to my profession and my job. I like being able to type these commands quickly and not wonder for too long about the syntax. I like not having to worry if I remembered to set up snippets in whatever test environment I’m using, or if it has third party tools installed.

I’m terrible at memorizing things, and this helps.

I’m not against automation. For production monitoring and responding to incidents, setting up tools and processes that help you work faster and avoid having one person as a single point of failure is awesome. Just don’t apply that principal to everything in your work.

There are some tasks that are fine to repeat, if you want to get to know them by heart.

Brent says: to learn more, check out our list of tasks you probably shouldn’t automate, and read the comments too – lively discussion in there.

Jeremiah says: I’m a huge fan of automation when you understand what you’re automating, why you’re automating it, and what can go wrong when you’re not watching. Otherwise, you need to be really careful.

Erik says: Just about everything I’ve automated has stemmed from a .sql file full of trial, error, and documentation. Know your processes, and make sure someone is made aware if they start failing.

14 comments ↑ Back to top

Brent & Jeremiah are coming to Pittsburgh on October 2-3, 2015. We’re doing a one-day pre-con, and then sticking around to present at SQLSaturday Pittsburgh too.

Developer’s Guide to SQL Server Performance – Live!

Make SQL Server Apps Go Faster - Live at PASS Summit 2013

Developer’s Guide to SQL Server Performance – Live at PASS Summit 2013

You’re stuck with a database server that’s not going fast enough. You’ve got a hunch that the biggest bottleneck is inside the database server somewhere, but where? In this one-day class, you’ll learn how to use powerful scripts to identify which queries are killing your server, what parts of the database server are holding you back, how to tackle indexing improvements, and how to identify query anti-patterns.

In one day of training with Brent Ozar and Jeremiah Peschka, you’ll learn:

  • How wait stats tell you where to focus your tuning
  • How the plan cache shows you which queries are the worst
  • How to make fast improvements by picking the right indexes
  • How to identify and fix the most common query anti-patterns

This is a pre-con before SQLSaturday Pittsburgh. If you haven’t attended a pre-con before, there’s a few things to be aware of. The session will be held at:

Pittsburgh Technical Institute
111 McKee Road
Oakdale, PA 15071

Lunch is included with your admission, but it will not include hands-on lab exercises or copies of the slides. This is how we keep SQLSaturday pre-con costs low – these SQLSaturday pre-cons are the most cost-effective way to get a day of training from us.

Use coupon code EarlyBirdPittsburgh to save $50 for registrations this month (May) – see you there!

0 comments ↑ Back to top

There are a few little gems built into SQL Server Management Studio that are easy to forget, but they sure can come in handy.

I sometimes generate a little formatted report of recent growths for a database, and how long they took. It’s really easy, and it’s built right in. Here’s how:

  • Right click on a database name in Object Explorer
  • Select Reports
  • Then Standard Reports
  • Then Disk Usage. Voila, the Disk Usage report appears!
  • To see recent autogrowth or autoshrink events, click the little plus sign under the pie charts.

Here’s a 30 second video of that in action:

Where Does SQL Server Track Autogrowth Events?

This report reads from the default trace files. Those files can roll over pretty frequently in an active environment, so this only gives you recent events. You can get the same information with a query, or look for slow auto-growths using our free sp_Blitz® script.

When sp_Blitz® gives me a warning that something’s up, I like to get screenshots from this report to go the extra mile. It’s very nicely formatted and shows any troublesome latency pretty clearly.

9 comments ↑ Back to top

After the SQL 2014 SP1 mess, I thought, “How are people with a real job supposed to keep up with updates?”

Go try to find the most recent SQL Server service packs and cumulative updates on It’s miserable – they’re scattered all over the place. Eventually, a lot of us started relying on, which is cool, but I have no idea who maintains it, and there’s no way to subscribe to updates there whenever a new patch comes out.

And then just try to figure out when support for a version ends – that’s even worse.

So I built

Does what it says on the tin, and you can subscribe via RSS or email as well. Hope that helps!

45 comments ↑ Back to top

Developers – how much operations work are you doing?

DBAs – how much development do you do?

The Separation of Duties

For most of us, we stick to our assigned job role. Developers write code and then throw it over the wall for the ops team to put in place. If there’s a problem, there is inevitably finger pointing. Eventually a developer gets over to an operations person’s cube and the problem gets solved.

It’s rare that we see any cross disciplinary skill sharing.

In Origin of the Species, Darwin notes that the finches in the Galapagos have become very specialized, depending on the island where they were found. The finches were so specialized that Darwin originally miscategorized a subspecies of finch as a wren. He goes so far as to say:

Seeing this gradation and diversity of structure in one small, intimately related group of birds, one might really fancy that from an original paucity of birds in this archipelago, one species had been taken and modified for different ends

What Do Birds Have To Do With Work?

Darwin’s finches all had very different beak sizes and shapes – each finch’s beak had adapted to a different food source. Even though they’re all finches, they worked in very different environments.

What about you? How specialized are you?

I consider myself a developer – I’ve spent most of my career writing applications. Some of those applications focus largely on SQL Server. But I can also configure HA/DR solutions, set up hardware, and plan storage deployments.

One of the problems with overspecialization is that it becomes difficult to survive if your environment changes.

Avoid Overspecialization

I’m a big fan of mixing job roles. Developers should provide operational support for their features. Operations staff should take part in developing tools or even features for the application. Having a well-rounded set of skills makes it easier to survive when your environment changes.

12 comments ↑ Back to top

You want your SQL Server VM to go as fast as possible, but you don’t have a lot of time or budget money, and you’re not allowed to reinstall it or upgrade. Good news – we’ve got a webcast for you.

In part 1, my demo-focused session will explain the VM-friendly performance features of SQL Server 2012, 2014, and even the next version, 2016.

Webcast sponsored by Veeam

Webcast sponsored by Veeam

You’ll learn how to measure a running application’s performance, and then simply by turning a few knobs and switches, you’ll see how performance improves. Microsoft Certified Master Brent Ozar will explain which features are right for your workloads, plus give you resources you can use when you get back to the office to go faster and learn more.

In part 2, Veeam will explain how to get fast transaction-level recovery of SQL databases, including agentless transaction log backup and replay, so you can restore your SQL databases to a precise point in time and achieve low recovery time and point objectives (RTPO™). Learn more at Veeam Explorers™.

This session is for sysadmins and DBAs running production workloads in VMware, Hyper-V, and Xen, whose end users are unhappy with performance.

Register now.

6 comments ↑ Back to top

Developers have struggled with a problem for a long time: how do I load up a new table, then quickly switch it in and replace it, to make it visible to users?

There’s a few different approaches to reloading data and switching it in, and unfortunately most of them have big problems involving locking. One method is this:

  • Create a schema for staging
  • When loading tables, create them in the staging schema
  • When load is complete, drop the table from your production schema
  • Then use ALTER SCHEMA TRANSFER to move the staging table into the production schema

This method sounds good, because items being worked on for staging are easy to identify in their own schema. There’s clearly going to be issues with concurrency between dropping the table and transferring in the new table, but the gamble is that will be fast and can be minimized.

ALTER SCHEMA TRANSFER locks the whole schema

The concurrency problem is worse than you might think. When you run ALTER SCHEMA TRANSFER, it locks up everything in that schema, so nobody can use any table– the lock is not limited to the table you’re transferring.

Not sure? Let’s take a look. First, we create database:


The create a schema named LockMe in the database. Create a table named InnocentTable in the LockMe schema, and insert some rows.

USE LockTest;


CREATE TABLE LockMe.InnocentTable (
    i int identity not null
GO 10

Now create a new table in the dbo schema and add a few rows. We’re going to transfer this into the LockMe schema.

CREATE TABLE dbo.NewFriend (
    i int identity not null
GO 20

Alright, now it’s time to do the transfer! Here we go. To make this easy to see, we’re going to leave a transaction open:


In another session, we just try to query LockMe.InnocentTable. We didn’t run any commands against it, but this query gets blocked and can’t make any progress:

FROM LockMe.InnocentTable

Looking at this in a third session with sp_WhoIsActive, we can see the problem:

Let go of that schema!

Let go of that schema!

Some Bad News: There Is No Awesome Way to Swap In a Table

The locking you see above is a very high level lock. The query can’t even get a schema stability lock on LockMe.InnocentTable.  There’s no magic hint that will get you around it: adding a NOLOCK hint won’t work (it still needs a schema stability lock).

Optimistic locking won’t get you around it either — queries in SNAPSHOT and READ COMMITTED SNAPSHOT need to get a schema stability lock, too.

If You Must Swap In a Table, ALTER SCHEMA is the Greater Evil

Unless you really want to block absolutely everyone who might use the schema (and live with the consequences), this command will cause you pain.

Need to get to the root cause of blocking? Read more on locking here on our blog for free, or check out our SQL Critical Care®.

12 comments ↑ Back to top