Announcing Live Query Execution Plans


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 – choose any one of the following:

  • 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.

How to Troubleshoot SQL Server Connection Timeouts

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.

Join Us at SQLSaturday Pittsburgh for a Pre-Con

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!


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!

Upcoming Webcast: Faster Virtual SQL Servers

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.

Watch Brent Tune Servers [Video] #MSIgnite

Last week at Microsoft Ignite 2015 in Chicago, I demonstrated how to tune a few SQL Server workloads with my favorite process:

  • Measure how fast the SQL Server is going
  • Check its bottlenecks
  • Apply some easy-to-tweak configuration settings and measure the difference

There’s no camera in this hour-long video, just a screen capture, so you’ll have to imagine my jazz hands.

You can grab the demo scripts here, and grab our related performance tuning resources here.

Databases Need a Kosher Certification

As I picked up a nutritious breakfast from my favorite bakery (love ya, Magnolia), I noticed a sign in the window saying they were certified Kosher. As I usually do, I thought about how that kind of thing applied to databases.


This database is totally free of heaps.

The Star-K symbol on food products or restaurants means that the ingredients and processes have been inspected by a rabbi or a kosher certification company. The way I understand it, given my extensive scanning of the Wikipedia entry on kosher certification agencies, a rabbinic field rep closely monitors the restaurant’s ingredient acquisition and food preparation. He makes sure everything matches up with kosher standards, and then puts his reputation on the line to vouch for it.

I’d love to have that for databases.

What if we had a “DBA Approved” stamp that covered things like data model design, index tuning processes, good query writing, and backups that matched the company’s RPO and RTO goals?

There could be a centralized list of people who were approved to give out that certification, and they’d get regular ongoing training to make sure they were qualified to put their stamp of approval on a database.

That’d be the kind of certification I could actually get behind.

The closest I’ve seen so far is kCura’s Best in Service certification for Relativity. It’s not just about DBAs passing a test – it assesses a customer’s hosting environment and actually post the scores live every week at so that end users know which environments are the safest bets to host their e-discovery data. I like this approach a lot, and I wish Microsoft would take a similar approach with SQL Server.

#MSIgnite SQL Server Unplugged Q&A Summary

This afternoon, we had one of my favorite sessions: the off-the-cuff Q&A with some of Microsoft’s best and brightest. I probably missed somebody, but for sure we had Conor Cunningham, Joe Yong, Jos de Bruijn, Kevin Farlee, Luis Carlos Varga Herring, Mike Weiner, Sunil Agarwal, and Tiffany Wissner.

Hubba hubba.

SQL Server Unplugged

SQL Server Unplugged

These sessions are fun to attend because sometimes you can get hints and details that weren’t originally scheduled to go public.

I didn’t transcribe all of the questions – often the answers are well-known amongst smart people like you, dear reader. You’re also attractive too, but I digress.

Q: How do you control engine versioning in Azure SQL Database?

Conor answered: SQL Azure is platform-as-a-service (PaaS). It gets you automatic patching. We build SQL Azure and SQL Server off the same mainline branch of code, and we ship first in SQL Azure. We can turn them on for limited sets of accounts first, then turn it on more broadly in public preview mode, and then eventually make it generally available. We have a whole fabric layer used to manage our versions and deploy them as we need it. You’re getting the next version of SQL Server before it ships to the public.

Followup Q: Can you use a database compatibility level to control the version you’re on?

Conor answered: In January, we launched the new round of v12 servers. Before v12, we didn’t expose compatibility level settings. Now, we’re starting to expose the 130 compatibility level, which will also be the compatibility level in SQL Server 2016. As we get more features aligned to the boxed product, it’ll make more sense.

Q: When I combine AlwaysOn AGs with replication, how do I manage jobs?

Luis answered: you have to manage those manually by copying the jobs to all servers.

Q: Is Always Encrypted going to work with Windows logins?

Conor answered: in the first version, no, but at some point, yes.

Q: What’s the future around DACPACs and deploying a whole app?

Conor answered: Windows Azure has a deployment and servicing model that they’ve been working on, and we’ve been talking about how we can make it work with DACPACs. We don’t have anything to announce today. I’ll be honest with you – there’s a lot of problems with doing this correctly, especially with complex deployments like AlwaysOn AGs.

Q: Besides a single SSDT, are there any other investments in developer tooling or ALM?

Conor answered: We haven’t been doing a ton of major investments there. Anything you’re interested in? (Data generation and automated builds.) There’s some stuff we’ve been working on with the Visual Studio team, but they’re driving it. We don’t have anything to announce in the SQL Server 2016 release time frame though.

Q: I have SQL Server sprawl. How are you helping customers consolidate and move to Azure? 

Conor answered with a huge smile: We’ve got the MAPS toolkit, but there’s a couple of things we’re working on that we are working hard on and we can’t share yet.

Luis added: We’re working on making it easier to look at your current database and your AlwaysOn AG configuration, and recommend a set of VMs with the right power in Azure.

Q: In SQL Server 2014, there are a lot of limitations on in-memory OLTP. Is that still true in SQL 2016?

Kevin answered: In 2014, we targeted specific scenarios where we knew we could be successful, and we nailed those. In this release, we’re working on getting it as broad as we can. SQL 2016 will have foreign key constraints between in-memory tables, but not regular tables.

Q: We’re looking at rolling out MDS, but we’re not happy with the current UI. Is Microsoft improving that?

Mike: I’ll have to defer that. (There are no BI experts in this particular session.)

Q: What’s enhanced about the Backup to Azure, and can we get an easier cleanup in maintenance plans?

Luis answered: We deployed it in SQL 2014, and we heard your feedback. We’re now supporting cheaper blob storage and striping (beyond the 1TB limit, up to 32TB). Backup will be parallelized across those stripes, so it’ll be even faster. That will be out in the next CTP. For automated cleanup, consider the managed backup feature rather than maintenance plans.

Q: How will licensing work in SQL Server 2016?

Tiffany answered: We haven’t made those decisions at this point in time. We just went through some significant changes in licensing, and most of our customers are still absorbing that change.

Conor added: You’re asking, is it possible to put all features in all editions? We don’t have anything to announce today. If you look at SQL Azure, it has most of the same features in all editions, and we vary pricing by resources, and that’s something to think about. But we don’t have anything to announce today.

The end – at least of my summary. I had to leave a little early to meet Erika, Jeremiah, and Kendra for dinner. (Gotta love it when conferences happen in my home town.)

Steal This Slide Deck: What I Learned About SQL Server at #MSIgnite 2015

If you attended Microsoft Ignite in Chicago this week, you’re probably barely keeping your eyes open at this point. There’s been so many after-hours get-togethers, and it’s been tough to get any spare time between sessions. (Jeez, these things are spread out all over McCormick.)

One of the things I love about Ignite is that the videos and presentations are available during the conference itself. That makes it easy to catch up on the sessions I wasn’t able to attend, and then I can go talk to the presenters with my questions the next day.

I realized I should turn my session notes into a slide deck, and give it away. I’ll update it a couple more times this week as more sessions finish.

Attendees – you can go back to the office, and run through this deck as a lunch-and-learn with your coworkers who couldn’t go. This way, your boss will be more likely to send you back next year because you were such a good note-taker.

User group leaders – you can use this as a quick intro for your chapter, before your next meeting, to get ’em up to speed fast on SQL Server 2016.

This deck is licensed in the public domain, meaning you can do anything you want with it. Enjoy!

How to Fake Load Tests with SQLQueryStress

Load testing – real, serious load testing – is hard.

In a perfect world, you want to exactly simulate the kinds of queries that the end users will be throwing at your SQL Server. However, in the words of a timeless philosopher, ain’t nobody got time for that.

Instead, let’s use Adam Machanic’s neato free tool SQLQueryStress to fake it. This is an oldie but goldie app that will run any one query thousands of times (or more) from dozens of sessions (or more), all from the comfort of your desktop:

SQLQueryStress in action

SQLQueryStress in action

After you install it on your desktop (or a VM in the data center, whatever, just not the SQL Server you’re trying to load test), click the Database button to set up your connection string. In this instance, I’m pointing it at one of my Availability Groups, using Windows authentication. As soon as I set the server and auth methods, the database list gets populated so I can set my default database:

Setting up your connection string

Setting up your connection string

Then it’s time to pick the query to run.

But you want to test more than one query at a time, right? You want to test a variety of different queries running all at once.

Rather than calling a single query, call a “shell” stored procedure that runs other queries. Here’s how it works:

  1. Declare an integer, and set it to a random number
  2. Based on the mod of that number, run a stored procedure
    (for example, if it’s divisible by 3, run sp_C,
    else if it’s divisible by 2, run sp_B,
    else run sp_A.)

Since SQLQueryStress will be calling this stored proc dozens of times at once, you’ll end up with a variety of different queries running simultaneously.

Let’s get a little more complex. Here’s what mine looks like for one of my query tuning demos:

SELECT @Id = CAST(RAND() * 10000000 AS INT)
IF @Id % 7 = 0
    EXEC dbo.Refresh_ReportByVoteType
ELSE IF @Id % 6 = 0
    EXEC dbo.Refresh_ReportByBadge
ELSE IF @Id % 5 = 0
    EXEC dbo.GetBadgesDetails @Id
ELSE IF @Id % 4 = 0
    EXEC dbo.GetCommentsDetails @Id
ELSE IF @Id % 3 = 0
    EXEC dbo.GetPostsDetails @Id
ELSE IF @Id % 2 = 0 AND @@SPID % 2 = 0
    EXEC dbo.GetUsersDetails @Id
    EXEC dbo.GetVotesDetails @Id

WITH RECOMPILE – I use this because I don’t want the GetShell stored procedure to show up in my execution plan stats. The work involved with building this execution plan isn’t significant, and it won’t be the largest part of my workload. (Oh, I wish it were.) All of the stored procs it calls will still show up in the plan cache, though.

@Id parameter – note that some of the stored procs take an @Id. For example, the stored proc GetBadgesDetails takes @Id, and uses that to look up a particular badge number’s details. This is handy because each of my stored procedures don’t have random number generators – they’re designed to mimic more real-world stored procs that have input values. If you wanted to get really fancy and test procs with lots of parameters, you’ll need to generate those in GetShell. You don’t want to hard-code the same values because then that relevant table data will end up getting cached in memory.

@@SPID – some of my workload queries simulate blocking. Due to the wonders of random number generation and very fast queries, if a blocking chain starts on any two sessions, then eventually the rest of the sessions will also call the stored proc that’s susceptible to blocking. After a few seconds, the only symptom my server will have is blocking – and that’s no fun. Instead, by using @@SPID %2 before calling GetVotesDetails (which gets blocked in my scenario), I make sure that no more than half of my sessions will get blocked at once.

The end result is beautiful – well, at least if you want something that looks like a production server getting hammered with all kinds of different queries:



I love using this quick-load-generation technique in our performance tuning classes. It’s a great way to show a server that looks like home, and gets students to figure out which queries are causing problems – and which ones are just harmless background noise.