YouTube
GitHub

#TSQL2sday: I Just Wish SQL Server Could Restore a Single Object from Backup.

Brent Ozar
Backup and Recovery
29 Comments


For this month’s T-SQL Tuesday, Kevin Chant asked us to dream up our fantasy SQL Server feature. I’m sure people are gonna ask for flying cars and gene splicing, and that’s awesome. I’m excited to hear the kinds of things they dream up.

Me? I’ve got a really simple ask.

(When you’re talking to Microsoft people, you have to use “ask” as a noun.)

Today, if you have problems with a single file in your multi-file database, you can do an online file restore like this:

That one file is restored from backup, and you can update it by applying subsequent transaction logs, and then eventually bring it online. You can restore individual (or multiple) pages, too. But the thing is – I rarely need to restore a file, or specific pages.

So my ask is:

I just need a single object, like a table someone dropped, or a stored procedure from a mucked-up deployment that somebody didn’t really save in source control the way they were supposed to. I know, there are gonna be foreign key issues, and I’m fine with the keys being marked as untrusted at that point.

Like it too? Vote for Gonzalo Bissio’s feature request.

What’s It Like to Have a Live Class Season Pass?

Brent Ozar
Company News
4 Comments

I don’t usually post product reviews here on the blog, but Vadim Kulikov (aka BlondeDBA in Slack) recently finished all of the Mastering classes, and he took the time to write a really detailed review. Here it is, unedited:

I heard some folks refer to Brent’s training as “Brent Ozar’s Full Experience”. I really like this description, as it accurately describes the training. For some of us who have been in the DBA industry for some time, training is a mandatory part of the job if you want your skills to remain relevant. However, I noticed that most other trainings that I have taken have one common issue, they help to introduce the subject but you don’t retain much due to the lack of hands-on labs and /or long lecturing often reciting from books-online that provides little practical value. In my opinion, Microsoft is not always the best source of information about SQL Server.

One main thing that sets Brent’s training apart from other is the tremendous practical value. The training is very hands-on. The tools that his team developed are the actual tools Brent uses himself in his consulting business. The class introduces the tools and teaches how to properly use them. You learn how to identify bottleneck(s) and how to develop an approach to come up with the most effective solution for the real-world performance tuning scenarios. I purchased the annual pass and went through all the classes. Below is the list of my favorite things about Brent Ozar’s online training:

1. The class provides hands-on material and demonstrates a comprehensive approach to troubleshooting performance problems and identifying root cause. You will learn how to efficiently (with set time limit) improve the situation or resolve discovered bottlenecks completely.

2. In addition to being technically skilled, Brent is a fantastic instructor. Either its speaking with “Clippy” voice or “taking on a personality” of the SQL Server Engine Optimizer, sharing war stories from his consulting gigs and adding constant humor (at times self-deprecating ?) keeps your attention and remarkably doesn’t get old. I have gone through close to a hundred of hours of Brent’s online training (both free and paid) and often run into the same jokes over and over again and still find them hilarious, which adds levity (Brent does get into complex performance tuning scenarios that can be intimidating) and also helps to remember the material. Brent is very creative with his presentation, often uses analogy with car performance or hospital triage situations which I found to be helpful to understand the subject and retain it for a long time.

3. The classes are well structured and tend to build on previously presented material. So, its recommended to take the fundamental classes first to feel more comfortable with more advanced topics. I would also recommend to go through all the specified pre-requisite training to keep up with the class. While lectures have perfect tempo (not too fast not too slow) but the sheer amount of information presented might be overwhelming for the 1st time. I take notes during the lecture and save the Slack chat as Brent addresses other participant ‘s questions in real time.

4. The ability to watch the recorded class before and after (with purchased annual pass) is by far the best option which sets this training apart from every other I have seen. I replaced my “Audible” with “Brent Ozar” subscription, so I can play the courses on my way to work.

5. The Labs are well-thought-out and could be quite challenging. They reinforce the presented material and also demonstrate the effective use of the First Responder Kit tools. You get to use beefed up VMs with large amount of cores and RAM to bring the lab environment very close to the actual production systems you encounter in the field. I love the option to download the different sizes of StackOverflow database to fit your local environment constraints, so you can practice with the labs at your own pace later on your own server.

Every little aspect of this training appears to be carefully considered and planned. Brent started to record with a green screen behind him, so you don’t have to manage multiple screens when viewing the recorded class later. Little detail but makes a big difference and contributes to a pleasant experience.

6. I purchased the annual pass, so could take all the classes I want multiple times. I love that the class starts so early (6am PST), as it give me an opportunity to do at least the first part of my training undisturbed. Most of us have full time DBA jobs and training is often considered a luxury, that you are supposed to arrange on your own time, so “it doesn’t interfere with your DBA responsibilities” ? Re-taking classes helps to capture the parts you missed the 1st time due to the pressures of your job.

7. Another valuable aspect of Brent Ozar training is that you get to ask questions, at times outside the context of the actual class. The feedback you get – provided you can concisely articulate your issue – is very helpful especially if you can’t afford to hire Brent as a consultant.

8. There are many Senior level DBAs who enter the training and contribute to the class by asking interesting questions or share their own experience deploying a particular solution. You can benefit from someone else’s success or failure. It also helps to see how your skills measure up to other DBAs.

9. Brent is constantly improving his content factoring-in students’ feedback. So, you benefit by taking the same class multiple times. I would, however, wish that the classes’ schedule could be a little bit more aligned with the progression of difficulty of a particular class, so you start with basic like “Fundamentals of Index Tuning ”, then progress to “Mastering Index Tuning” for example, and then followed by the most challenging “Mastering Server Tuning”

I highly recommend the training. You will be amazed how much you didn’t know. It’s kind of embarrassing …?

Wow! Thanks, Vadim – I’m honored that you took the time to write this out. Wanna find out what it’s like for yourself? Check out the Live Class Season Pass, and you can also save a lot of money by building your own lab server. Enjoy!

DBA Training Plan 19: The L-Word, Licensing

Brent Ozar
DBA Training Plan, Licensing

You’ve gotten through a lot of this training. You’ve tried fixing the problem with indexes, query tuning, and judicious application of various configuration switches. You’ve been watching webcasts and using free scripts, but…

Things just aren’t getting better.

Before you start browsing your cloud vendor’s sizing price list or your favorite server vendor’s build-and-price site, we need to talk about the L-word: licensing. It’s a really complex subject, but we’re going to try to boil it down as simple as possible.

You pay by the CPU core. Even if you’re deploying an older version of SQL Server, you still have to buy and pay maintenance on the current version of SQL Server. That means you’re using core-based licensing whether you like it or not. In the good old days, we’d buy the biggest CPUs possible and clap our hands when it sat around at 0% CPU, but those days are over. These days we want as few cores as possible, but that go as fast as possible.

SQL Server Standard Edition is about $2k USD per core, and it’s limited to 24 cores and 128GB of memory. Even if you deploy just 8 cores (like 2 quad-core processors), that’s $16k of licensing. 64GB of memory is really cheap – you’d be crazy to deploy SQL Server on a physical box with anything less than 64GB of memory. Even on virtual machines, keep things in perspective – 128GB RAM is way, way cheaper than a core of licensing when you’re licensing VMs individually with Standard Edition.

SQL Server Enterprise Edition is about $7k USD per core, but much like Brent Ozar Unlimited, it has no limits. (Okay, actually, both us and Enterprise Edition have some limits.) Enterprise Edition adds a lot of the cool features you really want, like online index creation & rebuilds, readable secondaries in AlwaysOn Availability Groups, and Transparent Data Encryption. However, at $7k per core, you need to be really, really careful about how many cores you buy – most of the time, folks buy way too many cores and don’t spend enough on memory or storage throughput.

Virtualization – if you have less than 4-5 VMs, you’re usually better off buying Standard Edition at the guest level. Once you have more than 4-5 VMs, buy two (or more) virtualization hosts dedicated just to databases. License Enterprise Edition at the host level, and you can run as many guests as you want on those hosts. However, you need to buy Software Assurance (it’s like maintenance) so that you can use License Migration and move your VMs around from host to host. Useful in case one of your hosts fails.

Containers are just like VMs, which makes the whole container deployment thing a really awkward discussion. Microsoft is all, “Just use a bunch of containers!” but you either have to track their licensing individually with Standard Edition, or take every container host where your containers run, and license ’em all with Enterprise Edition. Come true-up and auditing time, you’ll have a lot of interesting questions around, “So, how many SQL Server containers have you been running, and on which hosts?” The easiest answer is to have a dedicated Kubernetes cluster where your SQL Server containers run, and license those hosts with the $7K/core SQL Server Enterprise Edition. When I say the “easiest answer,” I’m referring to your job and mine. The poor finance people, on the other hand, those folks are screwed when they have to write that check. Because of that, I have a tough time selling people on containers for production servers – unless they’re getting free site licenses from Microsoft in exchange for publicizing containers. Those folks seem to be all over it. Go figure.

SQL Server Developer Edition has the same capabilities as Enterprise Edition, but it’s not to be used for production. It’s free, so the rules for virtualization are a little different here. You don’t want to intermingle your Developer Edition VMs on hosts that you’re licensing with SQL Server Enterprise Edition because you’d just be wasting licensing fees. Put these Developer Edition VMs in with the rest of your servers.

Everything’s negotiable if you’re big. If you’re a service provider or a large enterprise, you may have different types of licensing agreements like SPLA or EA. Have their people take your people out to golf (I hear that’s how this stuff works), and people get drunk, and contracts get signed.

SQL Server Licensing GuideIf the licensing costs scare you, that’s where cloud alternatives to SQL Server come in. All the cloud vendors will rent you licensing by the hour, included with your VM pricing. It’s not to say that’s cheaper – over the long term, if you have predictable needs, it’s not – but it can let your accounting team classify the expense differently, and sometimes that’s a good thing.

For the gritty details, check out Microsoft’s SQL Server Licensing Guide PDF. It’s not short, but it’s definitive, and it’s pretty easy to read. If you’ve got any interpretation questions, you have to ask your Microsoft contacts, not folks in the community, and not salespeople. Nobody else can give you answers that will stand up when the auditor comes knocking on your door.

Comments are disabled on this post. That’s because in the past, I’ve learned that anytime I post about licensing, I get dozens of licensing questions in the comments. For licensing questions, contact your Microsoft sales representative. I’m not doing their work for free, ha ha ho ho.

DBA Training Plan 18: Managing Concurrency

Brent Ozar
DBA Training Plan, Locking, Blocking, and Isolation Levels
2 Comments

Locking isn’t a problem – queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won’t show up as a problem. (That’s one of the many reasons queries work so well in development, and then hit performance issues in production.)

Blocking, now that’s where the problem shows up. Blocking means that someone is holding a lock, and they’re blocking someone else from getting the data they need. Even worse is “deadlock” – that term strikes fear in the hearts of database administrators. Deadlock issues strike without warning, kill transactions dead, and slip away without a trace. Because they’re so transient, they’re hard to troubleshoot.

Good news, though – this stuff really isn’t hard to track down once you use a methodology. Here’s mine: first, identify if blocking is slowing things down.

  1. Run sp_BlitzFirst @SinceStartup = 1 to see if LCK% waits are a significant problem for you, or if your server is bored. Note the number of lock waits – if you’ve only had a few dozen over the course of a day, then it might have just been a few queries held up when someone was trying to create or rebuild an index. If you see hundreds (or heaven forbid, millions or billions) of these waits per day, especially with long (seconds or more) average wait times, then we’ve probably got a problem.
  2. Run sp_WhoIsActive and see if LCK% waits are showing up for queries running right now. This will help you identify which queries are involved, and what databases they’re running in.
  3. Finally, run sp_BlitzIndex @GetAllDatabases = 1 and look for “Aggressive Indexes” warnings pointing to the tables that are involved in the blocking. These indexes/tables aren’t necessarily the problem – they’re just involved with the blocking, just like you are. We wouldn’t fire you just because you’re involved with the blocking. (Well, maybe we would.) Interpreting these Aggressive Indexes warnings can be a little tricky (just like interpreting those Human Resources warning emails you keep getting.)
Mitch Bottell is preparing to take an exclusive lock on some BBQ.

Mitch Bottell is preparing to take an exclusive lock on some BBQ at SQL Saturday Sacramento.

Once you’ve identified how big the problem is, which queries are involved, and what tables & indexes they’re trying to lock onto, then we can start fixing the root cause of the problem. There’s three ways to do it, from easiest to most work required:

  1. Tune your indexes – get rid of the indexes you don’t need, and add the right indexes to help queries jump in and out quickly without holding huge locks to do table scans.
  2. Tune your queries – get rid of implicit conversions, functions in the WHERE clause, and other SARGability foes that cause your tables to scan an entire index rather than doing a seek. The less data we can read & lock, the more predictable and small our locks become.
  3. Change your isolation level – let queries cooperate with RCSI or SNAPSHOT isolation instead of waiting in line for locks. That last one is the easiest in theory, but just make sure you read that entire post (and the linked ones in it) to understand what you’re getting into, since it can backfire.

DBA Training Plan 17: Should You Partition Your Tables?

Brent Ozar
DBA Training Plan, Partitioning
7 Comments

In the last episode, I talked about knowing when it’s time to scale out: identifying when our data is getting to be so large that we have to split it across multiple servers, and I explained why that is so challenging. But what about table partitioning – SQL Server’s ability to break up a single table into smaller ones on the same server?

Great volumes have been written about table partitioning. It’s a complex feature and you can read for days to just understand how you might apply it. But will it improve performance for you? Table partitioning produces great benefits for some applications, but causes giant headaches for others.

What Table Partitioning Does

Table partitioning allows tables or indexes to be stored in multiple physical sections: a partitioned index is like one large index made up of multiple little indexes. Each chunk, or partition, has the same columns – just a different range of rows. In theory, SQL Server handles this transparently for you: a partitioned heap, clustered index, or non-clustered index can be referenced as a single structure although it’s stored in independent physical partitions. In other words, in theory you don’t need to change any code in the calling applications. (Is this true in reality? More on this later.)

Let’s say we want to partition the Stack Overflow Posts table – that’s where the questions and answers are stored. Every question & answer is considered a Post. I’m going to use the 50GB 2013 version of the Stack Overflow database because the Posts table is starting to grow: it’s 17,142,169 rows taking up 37GB space on disk – that’s most of the database, so breaking it up will be a performance boost, right?

Let’s say we want to partition them by CreationDate, and we want to store each year’s data in a separate file. We’ll need to start by creating filegroups for each year:

You’ll notice in this example that I’m using the same drive (M) for all of the files – in partitioning, though, it’s common to put the different years of data in different drives, and even stripe the data across multiple files. Let’s keep it simple for this demo though.

Next, I need to create a partition function and a partition scheme to tell SQL Server which data belongs on which drive/file:

Partitioning pros will note that I’m violating all kinds of best practices. Partitioning pros should also note that I’m trying to teach the basics of partitioning in a really short amount of space. Partitioning pros should go write their own blog posts.

Finally, we move the table over onto the new storage structure:

To pull that off, I had to drop the primary key.

This table – like most tables – has a clustered primary key, and SQL Server doesn’t let me just move the primary key over to the new storage structure. Instead, I have to:

Time to partition my wine collection

Time to partition my wine collection

  • Remove any foreign key relationships that reference this table
  • Drop the primary key
  • Create a new clustered index on the partition scheme (which splits the data across multiple files)
  • Create a primary key again
  • Recreate the foreign key relationships

For more details about why I had to do all that, see this excellent Stack answer by Remus Rusanu, and make sure to read the linked Microsoft article, Special Guidelines for Partitioned Indexes.

See, partitioned indexes come with a whole lot of gotchas:

<sigh>

Partitioned tables work, but…they’re a lot of hard work.

So should you partition your tables?

If you’re dealing with a 1TB+ data warehouse that can do sliding window loads, possibly.

If you’re dealing with a smaller one, or you can’t do sliding window loads, partitioned views are usually a better fit.

If you’re doing transactional workloads, and you can’t guarantee that the partitioning key will be in most of your WHERE clauses, then no, partitioning isn’t a good fit. Plain ol’ index and query tuning will be a better match.

DBA Training Plan 16: Is It Time to Scale Out?

Brent Ozar
Architecture, DBA Training Plan
12 Comments

In our last episode, we found out what your server was waiting on. In the closing remarks, I mentioned that you should find out which queries are causing the problem, and focus your mitigation efforts there. You might have circled back to the episode where we covered sp_BlitzCache, used a @SortOrder parameter that lines up with your top wait type, and…

Now you’re frustrated.

Because you’re looking at all these bad queries and thinking, “Isn’t there an easier way? Can’t I just throw hardware at this?”

Scaling out reads with replicas:
splitting queries between servers

When you want to copy the exact same production data to a second server to offload reporting queries, some of the costs are immediately obvious:

  • Hardware and storage – even if you’re running it in a virtual machine, you need to account for the costs of say, 4 cores and 32GB RAM. Not only will you need storage for the databases, but you’ll also need to decide whether this server gets backed up, and copied to a disaster recovery data center.
  • Software licensing – Standard Edition is ~$2k per core, and Enterprise Edition is ~$7k per core. Toss in Windows (especially now that it’s licensed per-core), your management/backup/antivirus tools, and your monitoring software.
  • Project planning – you’ll need to design how to get the data from production to the reporting server, like with Always On Availability Groups, log shipping, or transactional replication.
  • App modifications – the app running reporting queries will need a new connection string. Even with Always On Availability Groups, reads aren’t automatically offloaded to readable replicas – you have to use the connection string parameter ApplicationIntent = ReadOnly to tell SQL Server that you promise not to try to write anything. If you have a single app that does both reads and writes, and you only want to offload some of the queries, you’ll need to go through the code to switch those queries over to the new connection string.

But some of the costs come as cruel surprises:

  • Adding a troubleshooting process – sooner or later, the data replication process will break. Depending on the method (AGs, log shipping, replication) and failure type, it’ll fail in different ways – maybe all of the data is old, maybe just some of it is, or maybe the reports aren’t accessible at all. You’ll want to list out the failure methods and explain what symptoms will look like. This helps business users recognize when their reports are wrong, and react appropriately. If you don’t do this step, then after the first failure, people are just always going to expect that there’s a bug in the report data.
  • Prepare for failure – for each of those failure methods, decide how you’re going to react. For example, if AG replication breaks and reports are out of date, will you point reports at the primary until the problem is resolved, or will users just have to deal with unavailable reports while you troubleshoot or resync the replicas? If you don’t do this step, then you’re going to be winging it every time, and you’ll look unprepared while reports are wrong or down.
  • Set realistic expectations for RPO and RTO – based on your process and preparation, make sure the business users understand how long their reports will be down when things break.
  • Measure the overhead of replication – AGs and transactional replication can add performance slowdowns beyond what the reports used to cost. For example, if you were only running a few reports an hour, and only hitting a subset of the data, then suddenly replicating every individual delete/update/insert operation can have a huge overhead.
  • Add monitoring – you need to start monitoring how far behind the reporting server is, and how performance is doing on both. Performance troubleshooting becomes a lot harder, too – for example, when you’re doing index tuning, you have to combine data across both the primary and the reporting servers in order to find the right mix of indexes across the board.

And at the end of all this, all you’ve got is a way to split out the read-only queries. Those might not even be your biggest problem – you might need to split up writes between servers, and that gets REALLY hard.

Scaling out writes with sharding:
splitting data between servers

Sharding is the technique of splitting your data between multiple shards. You have the same exact database structure on each server, but you split up customers into multiple shards – for example, maybe the US customers are on one shard, and the European customers are on another shard.

Sharding gets trickier when you have to keep data in sync between shards. For example, you might run a web site, and you want to keep all of the product and pricing data exactly the same across shards. You may also need to combine data back from all of the shards into one, like for reporting purposes.

Other techniques for splitting load across servers involve replication with multiple masters – multiple servers that can accept writes at any given time. If you’re interested in that topic, check out Many Masters, One Truth. (Spoiler: it’s a lot harder than it sounds.)

But the real bottom line with sharding, and the reason implementation is so hard, is that your application has to know which shard to query. If you’re connecting all of the apps to a single SQL Server and routing requests from there, you’re right back where you started: bottlenecked on a single SQL Server.

This whole post was a trick.

None of the scale methods are really easy. Whether you’re convincing the boss to buy more memory, taking an outage to upgrade SQL Server, or rewriting the app’s back end to accommodate sharding, you’re going to be putting in serious work.

That’s why I put so much emphasis on tuning indexes and queries. It’s hard to make your server 100x faster – but it’s really easy to make your database design and queries suddenly 100x more efficient. Fixing queries and tables and indexes is indeed hard work – but it’s totally worth it. The better you get at these techniques, the farther you can go in your career.

DBA Training Plan 15: What’s Your SQL Server Waiting On?

Brent Ozar
DBA Training Plan, Wait Stats
6 Comments

You’re here, dear reader, because you weren’t “classically trained” as a database administrator. You didn’t graduate from the University of Sciencing Computerses with a Master’s of Transactional Processing.

You probably got your start as a developer or sysadmin, and gradually fumbled your way here. You’re used to monitoring stuff from the OUTSIDE using things like perfmon counters or CPU graphs.

SQL Server has a way, way, way better tool: wait stats. Whenever your queries need to wait on something – storage, locks, memory, whatever – SQL Server tracks what it’s waiting on, and for how long. It’s like you’re seeing INSIDE the server, with all kinds of possible debug points being tracked.

All you have to do is ask SQL Server, “What have you been waiting on?” by running this:

And presto, you get back something that looks like this:

sys.dm_os_wait_stats

And, uh, it’s…confusing. It’s hard to understand, filled with misleading harmless stuff, and it includes all wait time since startup – which includes overnight jobs, backups, checking for corruption, etc. So I wrote something better.

The better way: sp_BlitzFirst

Go get sp_BlitzFirst from our free First Responder Kit, and run it like this:

Here’s what it does:

  1. Takes a snapshot of a bunch of system data (including sys.dm_os_wait_stats)
  2. Waits 5 seconds
  3. Takes another snapshot
  4. Compares the difference between those 2 snapshots to tell you what’s happening on the server (without starting anything expensive and slow like a trace or XE session)

Here’s what the output looks like – and focus in on the area where I’m pointing at with the spiffy red arrow:

sp_BlitzFirst wait stats

The “WAIT STATS” section lists what your server was waiting on during that 5 seconds:

  • Wait_type: cryptic name from Microsoft
  • wait_category: more human-friendly description
  • Wait Time: how long we spent waiting on that thing. Generally, the top couple of things should get most of your focus, and this is what I’m sorting the results by.
  • Number of Waits, Avg ms Per Wait: helps you understand if this is something that’s not happening often (but sucks when it happens), or if it’s a death-by-a-thousand-cuts scenario
  • URL: a link to SQLskills’ wait types library so you can learn more about each particular wait type

Now, the trick you just learned only shows you performance right now. Your next question is going to be, “How can I track this stuff over time?” You can set up an Agent job to run sp_BlitzFirst every 15 minutes and log this data to table so you can trend it over time:

You can learn more about doing that, and how to use Power BI to read the data, in my post on the free Power BI Dashboard for DBAs.

The modern method of SQL Server performance tuning

I can’t emphasize this enough: screw CPU %, page life expectancy, disk queue length, or any of those other metrics your grandpa told you to watch. If you’re only looking at a few metrics at a time, you’re looking at SQL Server from the outside.

It’s time to get inside the server:

  1. Ask SQL Server, “What have you been waiting on?”
  2. Find the top queries & issues causing that wait
  3. Focus your tuning efforts on those to give you the biggest bang for the buck

Obviously, I can’t teach you about every possible wait type bottleneck on your server in this DBA Training Plan series, but I can get you started on the right steps on the journey. Here are your next steps for learning:

  • Run sp_BlitzFirst @SinceStartup = 1, and look at the top wait types on your servers. Read the SQLskills wait library data for your top waits, and search the blog here too.
  • Free Troubleshooting SQL Server book – once you know your top wait types, pop this book open and turn to the relevant chapter for techniques on how to mitigate it. (It’s also got chapters on typical production outage issues, too.)
  • Fundamentals of Server Tuning class – where I teach you how to use sp_BlitzFirst to measure your server, and we talk through the top wait types on your server together.
  • Mastering Server Tuning class – 3-day hands-on class where you’re given tough workload challenges and you have to fix ’em fast.

DBA Training Plan 14: POP QUIZ!

Brent Ozar
DBA Training Plan
10 Comments

AH-HA! You didn’t know there would be one of these, did you, hotshot? You’ve been reading along in the series, just smiling and nodding, pretending you’re keeping up, but not really doing the homework. BUSTED! Put your books away and get your pencils out.

Backup & Recovery Questions:

  1. How many production SQL Servers do you have?
  2. What’s the RPO and RTO of your most important production server?
  3. Did your backups take the normal amount of time last night?
  4. When was the last time DBCC CHECKDB successfully finished in production?

Security Questions:

  1. How many different people are sysadmins in production?
  2. Do they each know that they’re sysadmins, and take care to avoid accidents?
  3. How many of your databases hold personally identifiable data like credit card numbers, social security numbers, and passwords?
  4. If someone gets hold of one of those database backups, can your company’s data go public?
  5. Have you informed your managers of that risk, or will they blame you?

Monitoring Questions:

  1. When a database server runs out of drive space, who gets emailed?
  2. Do at least two different people get the email in case one is on vacation or unavailable?
  3. What actions will you take to fix the situation?
  4. Are those actions documented so that everyone who gets the email can take action quickly?

Index Questions:

  1. Does every table in production have a clustered index?
  2. For any exceptions (heaps), do you have a plan to fix them?
  3. Which table in production has the most indexes, and why?
  4. Which frequently queried tables in production have the least indexes, and why?
  5. How are you managing index fragmentation?

The Right Answers

There’s no one right answer for any of these questions, but some answers are more wrong than others. Database administration is a journey, and not everyone in the company is going to appreciate the work you’re putting in. You can spend weeks or months trying to improve your answers on these. No matter how big your company is and how many database administrators you have, you’re probably never going to be truly happy with your answers here.

It's a trick question.

Final question: which one of these is Blanc de Blanc?

You’re not aiming for perfect.

You’re aiming for good enough that your managers accept the base of your Database Hierarchy of Needs pyramid, and that you feel confident in tackling the higher levels like performance and future-proofing.

Next week’s email is going to start digging into performance, and we’re going to ignore the lower levels of the pyramid – but that doesn’t mean that part of your journey is over. Print out this email, cut out the question list, and scribble in a few thoughts. Pin it up on your wall, and a few months from now, when you’re feeling overconfident that your environment is awesome, check that list again. Refresh your memory with the links on the right side of this email.

When an outsider comes in, like a support engineer or a consultant, they’re going to start at the base of your pyramid first. Before they start to help, they have to make sure your data is backed up and checked for corruption. They can’t go making changes without having a safety net.

And you shouldn’t either.

Now might be a good time revisit some of the posts in the DBA Training Plan series.

Updated First Responder Kit and Consultant Toolkit for August 2019

Brent Ozar
First Responder Kit, First Responder Kit Updates
1 Comment

sp_DatabaseRestore can now restore to Azure blobs and change the database owner after a restore, and everything else gets bug fixes.

To get the new version:

Consultant Toolkit Changes

No functionality changes this month, just updating the First Responder Kit with the below changes.

sp_Blitz Changes

  • Fix: the power mode check now includes the label for Windows 10’s new Ultimate Performance Power Mode, which surely goes up to eleven. (#2044, thanks B. Tyler White for the code.)
  • Improvement: added syntax highlighting in the Github repo. (This affects all scripts, not just sp_Blitz.) (#2090, thanks Konstantin Taranov.)

sp_BlitzCache Changes

  • Improvement: if 75% of your plan cache is new as of today, raise the priority on the plan cache time summaries to 1 (instead of 254.) (#2052)
  • Fix: removing air_quote_actual plans for now. This new feature in SQL Server 2019 & Azure SQL DB simply isn’t working consistently even in 2019 CTP 3.2, so disabling for now. (#2022, thanks Jonathon Wyza for the bug report and Shane Holder for the debugging.)
  • Fix: skip AG secondary databases that don’t allow readable connections. (#2072, thanks Adrian Buckman for the code contribution.)
  • Fix: running sp_BlitzCache from multiple sessions no longer duplicates the missing index count on plans. (#2070)

sp_BlitzFirst Changes

  • Fix: skip AG secondary databases that don’t allow readable connections. (#2072, thanks Adrian Buckman for the code contribution.)
  • Fix: was throwing an int overflow on boxes with over 2TB RAM. (#2060, thanks Dan Andrei Stefan for the bug report.)
  • Fix: index reorgs were being reported as DBCC operations due to a bug in sys.dm_exec_requests. (#2062, thanks Erik Darling for the bug report.)

sp_BlitzIndex Changes

  • Fix: when saving sp_BlitzIndex output to table, if you had an index with a definition over 4,000 characters long, it was throwing the legendary data-would-be-truncated error. Could happen if you had a lot of includes with very long column names, or with columnstore indexes. (#2076, thanks Scotti85 for the bug report.)
  • Fix: if the @IgnoreDatabases parameter was broken across multiple lines (like if you copy/pasted from a spreadsheet), they weren’t all getting ignored. (#2053, thanks Erik Darling for the bug report.)

sp_DatabaseRestore Changes

  • Improvement: new @DatabaseOwner parameter lets you set the database owner after the restore finishes. (#2081, thanks gdoddsy for the code contribution.)
  • Improvement: ability to restore to an Azure blob target. (#2067, thanks John McCall for the code contribution.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

DBA Training Plan 13: Why Do Cached Plans Go Bad?

Brent Ozar
DBA Training Plan, Execution Plans
2 Comments

It’s a little bit of a trick question: what could make a good query plan turn bad?

In the last episode, we used sp_BlitzCache to spot the most resource-intensive, longest-running query plans in your server’s cache. Go run those queries again, right now, and I bet you’re going to see plans in the cache that use parameters. Maybe they’re stored procedures, or maybe they’re parameterized SQL.

To explain it, I’m going to need to run a few demos. I’m going to use the StackOverflow database – particularly, the Users table that I demo in How to Think Like the Engine. I’m going to start with an index on the Reputation field:

That index is ONLY on the Reputation field – so it can be used for this below query, but it’s not a fully covering index:

That query finds all of the data for users whose Reputation score = 2. There’s not a lot of folks in the Stack database that match – the default Reputation score is 1, and people either stay there, or they start working their way up the charts.

Here’s what the query plan looks like:

Index seek with key lookup

Index seek with key lookup

SQL Server does an index seek on our IX_Reputation index to find the 5,305 rows that match, then does a key lookup to get the SELECT * part (because the index doesn’t cover all those fields.) Look at the execution plan, hover your mouse over the index seek, and you’ll see that SQL Server expected 5,305 rows – and 5,305 actually came back. Awesome.

Now let’s try that query looking for Reputation = 1:

The Plan with the Scan

The Plan with the Scan

Note that even though SQL Server auto-parameterized the query (that’s the @1 part at the top), SQL Server chose a different execution plan. This time, the actual plan shows that SQL Server expected 3mm rows to come back – so here, it makes more sense to do a clustered index scan rather than first make a list of the users that match, then do 3mm key lookups to get the SELECT * part. SQL Server is using our index’s statistics to guess how many rows will come back.

The same query can produce 2 different plans with 2 different parameters.

(More complex queries can even produce more different plans than that.)

Let’s put it in a stored procedure and see what happens.

This stored procedure is pretty simple:

Run it with @Reputation = 1, and you get the clustered index scan:

Perfect plan for big data

Perfect plan for big data

Then run it with @Reputation = 2, and you get…wait a minute…

Scan, but not as bad as you think

Scan, but not as bad as you think

You get the execution plan from the first pass. That’s because SQL Server caches stored procedure execution plans – it builds a plan for the first set of parameters that happen to get passed in when the plan needs to be built, then caches that same plan to reuse over and over. The plan will stay in cache until you reboot Windows, restart the SQL Server service, rebuild indexes, update statistics, run DBCC FREEPROCCACHE, etc.

Here, that’s not such a big deal. I know, you see clustered index scan, and you think performance is bad – but it’s not really that big of a deal:

  • @Reputation = 1 with index scan – does about 80k logical reads, takes about 30 seconds (but mostly because SSMS has to render 3mm rows)
  • @Reputation = 2 with index scan – does about 80k logical reads, takes about a second (because there’s only 5305 rows)

If you look at the actual plan for @Reputation 2 here, and hover your mouse over the Clustered Index Scan operator, you’ll notice that SQL Server doesn’t just save the plan – it also saves the estimates. We’re expecting 3.3mm rows to come back here – even though only 5,305 do. Who cares, though? Overestimating is awesome, right?

But then something goes wrong.

Somebody:

  • Restarts Windows
  • Restarts the SQL Server service
  • Frees the procedure cache
  • Puts the server under memory pressure (thereby pushing this plan out of cache)
  • Doesn’t run the query for a while
  • Rebuilds indexes on the Users table
  • Updates statistics on the Users table

And somehow the execution sequence is reversed. First, we run it for @Reputation = 2:

The seek shall inherit the mirth

The seek shall inherit the mirth

We get an execution plan beautifully designed for tiny amounts of data. Hover your mouse over the index seek, and you’ll see that SQL Server accurately expects that only 5,305 rows will be returned. With the index seek, we only do 16,268 logical reads – even less than before! Great! Now that plan is in the cache.

You can hear the train coming. Let’s run it for @Reputation = 1:

We reuse the plan for tiny data

We reuse the plan for tiny data

SQL Server uses the cached execution plan, but it’s ugly, which means:

  • We do an index seek, plus 3.3mm key lookups
  • We do a staggering 10,046,742 logical reads (up from 80k) due to those repeated key lookups
  • We only estimate 5,305 rows will come back, which means if we had added joins or sorts in this query, they would have spilled to disk
  • We can’t see the terrible awfulness in the plan cache, which only shows estimates, not actuals

This is parameter sniffing:
good plans turning bad.

SQL Server builds one execution plan, and caches it as long as possible, reusing it for executions no matter what parameters you pass in.

If for some reason, the plan disappears from memory, the very next set of parameters determine the new execution plan.

Your next question is, “So how do I get the good plan back?” And here are the next steps on your learning journey:

Research Paper Week: Constant Time Recovery in Azure SQL DB

Brent Ozar
Azure SQL DB, Backup and Recovery
10 Comments

Let’s finish up Research Paper Week with something we’re all going to need to read over the next year or two. I know, it says Azure SQL DB, but you boxed-product folks will be interested in this one too: Constant Time Recovery in Azure SQL DB by Panagiotis Antonopoulos, Peter Byrne, Wayne Chen, Cristian Diaconu, Raghavendra Thallam Kodandaramaih, Hanuma Kodavalla, Prashanth Purnananda, Adrian-Leonard Radu, Chaitanya Sreenivas Ravella, and Girish Mittur Venkataramanappa (2019).

This one covers a new feature for both Azure SQL DB and SQL Server that:

  • Stores the version store in the user database file(s) rather than TempDB, which
  • Facilitates near-instant transaction rollbacks, even for huge transactions
  • Lets you clear the transaction log much faster, even when transactions are open

Those of you who are familiar with the magic of Read Committed Snapshot Isolation (RCSI) and its use of the version store in TempDB are probably slapping your heads right about now going, “Ah, it’s obvious!” You’re going to be able to hit the paper running – no need to keep reading.

For the rest of you, I’ll try to condense the relevant parts of Kendra’s intro to RCSI here in a few sentences. When you enable RCSI or SI, SQL Server automatically starts storing versions of rows in TempDB. If you want to hold a lock on a row, SQL Server stores the original unmodified row over in TempDB. This means that if someone wants to read (not write) the row that you currently have locked, the SQL Server can simply hand them the unmodified copy out of TempDB and skirt around your lock. This means writers don’t block readers, which is what makes RCSI so massively popular for fixing blocking issues.

The downsides of RCSI include more workloads happening in TempDB, which causes two problems: slower TempDB access, and larger TempDB files. For years, folks have asked for the ability to use a different TempDB per user database to work around problems like this, but Microsoft went in a totally different direction and decided to let you store the version store in the user database instead.

This is way better than separate TempDBs (for this purpose) because having the original rows inside the user database opens up new possibilities for faster transaction rollback and quicker transaction log truncation, as the authors go into in the paper.

If you’re considering enabling Constant Time Recovery in SQL Server 2019 (when it ships), you should read the paper to understand the risks and drawbacks of a feature like this. This is the kind of feature that will put the cutting in cutting edge: I would fully expect to see a lot of rollback, concurrency, and backup/restore bugs fixed in the early Cumulative Updates for 2019. I’m not saying it’s a bad feature – it certainly looks cool – but there are lots of risks when you make a change this big.

If you enjoy this paper, you’ll probably also enjoy Socrates: The New SQL Server in the Cloud. It explains how Azure SQL DB Hyperscale works, and boy, does it have intriguing new ways of handling data and log files.

I hope you enjoyed Research Paper Week – I certainly did – and I want to stop for a moment to thank all those academics and research professionals out there who’ve done such a fantastic job putting very complex thoughts down into easier-to-understand words for the rest of us. These papers involve hundreds or thousands of hours worth of research, action, writing, and testing, and they’re all available to you for free. How awesome is that?

Research Paper Week: In-Memory Multi-Version Concurrency Control

Brent Ozar
SQL Server
3 Comments

If you’ve been doing performance tuning for several years, or graduated from my Mastering Server Tuning class, you’ve come across Read Committed Snapshot Isolation, aka RCSI, aka multi-version concurrency control, aka MVCC, aka optimistic concurrency. It’s not the way SQL Server ships by default, although it is the default for Azure SQL DB, and it’s part of the magic in how you can query an Availability Group readable secondary even when it’s applying updates to the tables behind the scenes.

Today’s paper turns it up several notches: PDF: An Empirical Evaluation of In-Memory Multi-Version Concurrency Control by Yingjun Wu, Joy Arulraj, Jiexi Lin, Ran Xian, and Andrew Pavlo (2017).

In-Memory Multi-Version Concurrency Control

This white paper compares and contrasts the methods used by SQL Server (Hekaton In-Memory OLTP), Oracle, Postgres, MySQL, SAP HANA, and others. It doesn’t really focus on SQL Server rowstore RCSI, though – the only SQL Server thing it focuses on is Hekaton.

It’s only 12 pages, but buckle up: these are very information-dense pages. By page 2, they’re already breaking out each competitor by its protocol, version storage, garbage collection method, and index management (logical vs physical pointers.)

By page 3, you’re facing stuff like this:

Page 3

Do you need this white paper’s contents in order to be a good production DBA, development DBA, or database developer? Absolutely not. This white paper is more for the curious amongst us, those folks who want to know internals work and why Microsoft made the design decisions that they did. It’s the kind of white paper I’ve only started digesting, and will probably take me several more readings before I’d even feel comfortable discussing its contents.

But the cool thing about having a blog like this is that a lot of y’all are smarter than me, so I present this white paper for your amusement. Enjoy.

People who liked this paper (uh, me) also enjoyed this one from Microsoft Research: Improving Optimistic Concurrency Control Through Batching and Operation Reordering by Bailu Ding, Lucja Kot, and Johannes Gehrke (2019). That’s right: taking groups of transactions that are happening around the same time, and reordering them to avoid problems with lock conflicts!

Research Paper Week: Automatic Indexing in Azure SQL DB

Brent Ozar
Architecture, Azure SQL DB, Indexing
5 Comments

Before I give you the link to the next research paper in this week’s series, I wanna give you a few questions to think about:

  • If you had to build something to automatically add indexes to SQL Server databases, where would you start?
  • What data would you use to make your decisions?
  • How would you test your decisions without implementing them in production?
  • How would you apply your decisions?
  • What’s the worst that could happen if your decisions were wrong?
  • How would you find out if your decisions were wrong?
  • What might your users do that would break your hard work?

Now, think about the same points – but for automatically dropping indexes. What data might you base your decision on? What’s the worst that could happen? How would you find out if the worst had happened, and could you correct the situation?

Seriously, print out this blog post or copy/paste those questions into an email, send it to yourself, and just think about it when you’re standing in a line or riding the train. Don’t cheat by reading the white paper first – spend some serious spare time thinking about how you’d answer those questions.

This paper covers those answers.

After you’ve spent a day or two thinking about the answers, read this: (PDF) Automatically Indexing Millions of Databases in Microsoft Azure SQL Database by Sudipto Das, Miroslav Grbic, Igor Ilic, Isidora Jovandic, Andrija Jovanovic, Vivek R. Narasayya, Miodrag Radulovic, Maja Stikic, Gaoxiang Xu, and Surajit Chaudhuri.

Automatically Indexing in Azure SQL DB

This easy-to-read 12-page paper is wonderfully candid about how Azure’s auto-indexing does what it does, the challenges it faces, how it’s succeeded, and times when it’s failed.

I don’t want to give away any spoilers here, but trust me when I say that if you do a lot of index tuning – especially graduates of my Mastering Index Tuning class – you’re going to recognize the challenges in this work, and you’re going to identify a lot with how Clippy runs into problems. Indexing is seriously complex, and there’s no magic answer. Azure SQL DB faces the same problems you do, and it’s making a really cool set of first steps to improve the situation – but with tools you might not expect.

I’ll talk spoilers in the comments as folks write in – feel free to leave spoilers in the comments, and avoid the comments before you’ve taken a day or two to think about your answers, and then read the white paper. This thought exercise really is worth the time. (I say this because I’ve thought a lot about these problems over the years!)

Research Paper Week: Query Execution in Column-Oriented Database Systems

Brent Ozar
Columnstore Indexes
1 Comment

This week, I’m sharing some of my favorite papers that I’ve read. Sometimes they’re about future technologies that haven’t shipped yet – and may never ship! Sometimes, like this one, they’re not from Microsoft at all, but from someone else in the industry who’s explaining a problem that we face in SQL Server.

SQL Server 2012 introduced non-clustered columnstore indexes, but they didn’t catch on much because they forced the entire table to become read-only. Thankfully, Microsoft kept investing in columnstore indexes, and by SQL Server 2016-2017, they became pretty doggone good defaults for data warehouses.

If you’re a performance tuning internals geek who’s just starting your columnstore learning journey today, start with this: PDF: Query Execution in Column-Oriented Database Systems by Daniel J. Abadi.

Query Execution in Column-Oriented Database Systems

It’s a ~130-page monster from 2008, before SQL Server’s implementation shipped (but probably around the same time Microsoft was designing it.) You don’t really need Microsoft specifics in here – this white paper is just a thought-provoking foundation that covers:

  • The differences between rowstore and columnstore indexes
  • How query execution is different with columnstore indexes
  • How performance can be better if you can preserve batch mode longer through the query plan
  • When data should be turned from columns into rows, like for joins
  • How Abadi implemented columnstore himself to understand the implementation and tradeoffs

If you understand these concepts, you can transfer this knowledge pretty well over to SQL Server’s columnstore indexes and execution plans.

It’s not a light read, but it doesn’t require familiarity with columnstore indexes at all. Abadi takes you from zero to hero in here, but you’re probably not going to knock it out in one sitting. I certainly didn’t – it’s been on my iPad for a couple of years, and I’ve repeatedly revisited it, learning new things each time.

Folks who liked this white paper will also like these:

Research Paper Week: Plan Stitch: Harnessing the Best of Many Plans

Brent Ozar
Execution Plans
5 Comments

Those of you who follow me on Instagram, Twitter, or Facebook know I’m taking most of August off for vacation. Erika and I spent the last couple of weeks in Telluride, had a brief stop in Sacramento for SQL Saturday, and this week we’re touring wineries in Napa and Sonoma.

As part of recharging, I caught up on a bunch of research papers that I’d set aside to read, and I realized I should share my favorites with you. For each paper, I’m going to try to boil down why I found it interesting, and why you might find it interesting too.

Let’s start with a paper that finds a new solution to a really classic problem.

As long as I’ve been working playing with databases, execution plans have had one giant pain point: parameter sniffing. I constantly run into situations where the slightest change of a parameter can completely rework an entire execution plan into something wildly different.

In the Microsoft Research paper Plan Stitch: Harnessing the Best of Many Plans by Bailu Ding, Sudipto Das, Wentao Wu, Surajit Chaudhuri, and Vivek Narasayya (2018), the authors propose something really cool: watching an execution plan change over time, splitting it into parts, and then combining parts to form a SuperPlan™. (They don’t call it a SuperPlan™ – it just seems appropriate, right?)

They even went so far as to actually implement Plan Stitch in SQL Server. I hope you’re sitting down:

We implement Plan Stitch in C# as a component on top of Microsoft SQL Server without requiring any changes to the engine. SQL Server stores previously-executed plans and their execution statistics, including plan structures, properties of operators (e.g., sort columns), and operator-level execution costs [37, 41]. Plan Stitch passively monitors query executions and triggers the search for alternative plans when execution statistics for multiple plans for the same query are available. Our implementation follows the logical architecture in Figure 2, where we force the stitched plan using the plan hinting API supported in SQL Server [36, 46].

Footnote 37 refers to Query Store, and footnote 36 refers to plan guides.

This is completely wonderful and I am mesmerized. When “Automatic Tuning” came out, I was infuriated at the ridiculous use of that term to describe what that feature actually did (simple plan regression.) This, THIS, ladies and gentlemen, THIS is automatic tuning. This is what that phrase should be applied to. So the only question becomes – when this feature eventually ships, what is marketing going to call it? Hekatuning?

If you liked that white paper, you’re going to love these:

And for the record, yes, I consider writing blog posts like this to still be vacation, hahaha. I banged out all 5 of this week’s posts while sitting poolside with drinks. (That last word is definitely plural.)

View this post on Instagram

Afternoon reading by the pool

A post shared by Brent Ozar (@brento) on

DBA Training Plan 12: What Query Plans Are In Cache?

Brent Ozar
DBA Training Plan, Execution Plans, sp_BlitzCache
3 Comments

In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I’ve also talked about the dynamic management views (DMVs) that let you query what’s in SQL Server’s memory. Let’s put those two things together and find out what queries have been running on the server lately.

Start by running this on any SQL Server – development is fine:

Some of the columns in the result set include:

  • sql_handle – used to join to other DMVs to get the text of the query.
  • plan_handle – used to join to get the execution plan.
  • creation_time – when the query plan went into cache.
  • last_execution_time – the last time the query was run.
  • execution_count – the number of times Picard has said “Make it so.”
  • total_worker_time – total amount of CPU time burned while the query executed – and note that there’s also a last_worker_time, min_worker_time, and max_worker_time. Most of the columns in this DMV have that same pattern.

So if you want to find which queries have read the most data, you could do:

Pretty nifty, eh? Except now you wanna see what the queries are, and their execution plans. You could try to write your own DMV query from scratch, joining all that stuff together, but…this is the year 2019. (Or at least it is when I write this.) Ain’t nobody got time for that – there’s a much, much easier way to analyze your plan cache.

Enter sp_BlitzCache.

A few episodes back, you downloaded the totally free First Responder Kit. One of the scripts in there is sp_BlitzCache.sql – open that one and run it in the master database to create the sp_Blitz stored procedure. Then run it:

This shows you the top 10 queries that have run the longest time in total. I happened to pick Duration here, but sp_BlitzCache has a lot of useful sort orders:

  • @SortOrder = ‘cpu’ helps you find queries burning the most CPU time, often due to implicit conversions, sorts, or functions running row-by-row.
  • @SortOrder = ‘reads’ finds queries that are reading the most data, typically great candidates for index tuning.
  • @SortOrder = ‘memory grant’ finds queries that need large amounts of memory to run (or at least, SQL Server thinks they need a lot – but it can be dramatically wrong.)

In the top result set, scroll across a little to the right, and look at these columns:

  • Executions – how many times it ran.
  • Total Duration (ms) – the total runtime across all the executions.
  • Avg Duration (ms) – total duration divided by the number of executions.

As they say in the land of wine and cheese, voilà. Keep scrolling around left and right, and you’ll find all kinds of other interesting metrics – many of which you recognize from sys.dm_exec_query_stats, and some that took a whole heck of a lot of joining and calculating in order to display to you, dear reader.

It’s tempting to click on the Query Plan column and dive right in – but hold up. Before you go looking at operators and errors, take a few moments to read the Warnings column. These are a combination of warnings built into the query plan (like implicit conversions) and warnings we figured out on our own (like spills over 500MB or joins to a user-defined-function.) The latter set of warnings is especially important because they’re not displayed with a yellow bang on the plan: they’re hidden issues that you need to be on the lookout for as you go spelunking through the plan and the query.

As you read through the warnings, you’ll doubtlessly come across one that doesn’t make intuitive sense to you. That’s where the second result set comes in: it’s like a decoder ring of the warnings you’re seeing.

sp_BlitzCache query warnings

Want to tune the queries?
This is where specialization comes in.

Back in episode 3, when we talked about treating our servers like cattle, I said that sooner or later, you were going to have to pick a specialty. The journey of learning to performance tune queries – for example, removing barriers that cause forced serialization – is typically a different journey than efficiently automating the rollout of Always On clusters in Azure VMs via PowerShell scripting.

Now’s a good time to pause and read my post on the job duties of production DBAs vs development DBAs.

If you want to focus on production DBA work, then your goal for this week is to hand off the sp_BlitzCache output to your developers. This is good, juicy information for them to understand which queries are using the most resources. Many of my clients actually publish the sp_BlitzCache output on an internal web page so the developers can go check it at any time – it turns into a bit of a competition, making fun of whoever’s queries are up on the top of the suckerboard. (I call sp_BlitzCache the suckerboard, not the leaderboard, because it’s the query plans that suck the worst.)

If you want to focus on development DBA work, performance tuning the queries and indexes, then here are your learning resources for this episode.

If you want to do both production and development DBA work, you just need to be aware that you’ve only got so many hours in the week. You can do a kinda-sorta-halfway job at both, but you’re not going to do a phenomenal job of both – at least, not as long as you’ve got real day job duties too, like responding to help desk tickets and sitting in meetings. I’m a great example of that myself – I’ve long since given up production DBA work, and I focus on the performance side instead. They’re both fun, high-paying jobs, don’t get me wrong – I just picked the latter, that’s all. (And there are plenty of other rewarding careers around data, too.)

DBA Training Plan 11: The Basics of Executing a Query

Brent Ozar
DBA Training Plan, Execution Plans
13 Comments

Up til now in the DBA Training Plan, we’ve been taking inventory of our servers, making sure the data’s well-protected, and understanding the basics of how the data’s stored in indexes inside those data files. Now, let’s start looking at performance, and let’s start by zooming really far out to think about how SQL Server runs a query.

When you pass in a query:

  1. SQL Server comes up with an execution plan for it
  2. SQL Server executes that plan

The first one happens in what seems like an instant, but in that instant, a whole lot of really complex decisions are made that will determine your entire experience.

It’s a lot like building a custom house.

When you decide to build a custom home from scratch:

  1. An architect designs a blueprint plan for it
  2. The construction company turns that blueprint plan into reality

The architect asks you a lot of questions about your style, how you like to live, and probably most importantly, how many people you expect to be living in this house. Capacity planning and honesty with your architect are both important here: if you tell them you plan to live by yourself and don’t entertain much, then you’re going to get a very different blueprint and cost than if you say you’ve got 8 kids and throw a lot of parties.

Once the architect hands off plans to the construction company, those plans are pretty well set in stone. You might be able to make small, superficial changes, but you’re not changing it from, say, 1 bedroom to 5 bedrooms. If you were single and had your architect design a 1-bedroom home, but then during construction, you decide to get married to someone who has 6 kids from a prior marriage, it’s not like the builder can move a couple of walls around and make everyone happy. You’re going to have to stop that project, then go back to the architect for a new plan.

There’s a strong division between the two phases: the architect’s work, and the construction company’s work. I might even say there’s a wall between them, but then this metaphor gets really complicated.

SQL Server works the same way.

(And by that, I mean all your construction projects run late and over budget. (Just kidding. (Not really.)))

Earlier, I used a really simple two-step process saying “SQL Server” comes up with an execution plan, and then executing it. I was wildly oversimplifying that – but now let’s dig a little deeper:

  1. The architect hears your list of requirements. Unlike our house-based analogy, where the architect can ask followup questions, he just has to sit here and listen to your demands, and hope that you describe your needs in a clear, accurate manner. T-SQL is a declarative language: you’re declaring the shape of the result set you want, but not necessarily describing how you want the exact result set to be built. (You can do that, though.)
  2. The architect thinks about whether he’s heard those requirements before. If someone’s come into his office and asked for something extremely similar, he may pull a blueprint out of his cache and hand that to you so the construction company can immediately start working.
  3. If he hasn’t, he’ll design a new blueprint execution plan. To get a rough idea of just how hard this work can be, scan Microsoft’s Query Processing Architecture Guide – and then realize that it’s an incredibly brief summary! The architect does all this work, and then saves the query’s blueprint execution plan in cache (memory) so that he can reuse it if you happen to run the query again.
  4. The construction company takes the fresh or cached plan and starts work. This is the part you usually focus on, and you usually say things like, “Dang, it’s taking way too long to run this query.” Thing is, it’s not usually the construction company’s fault: they were just given a plan that wasn’t appropriate for the amount of data involved in the query. Maybe it was designed for a tiny amount of data but way too much came back, or maybe it was designed for a huge amount of data and did a ton of unnecessary prep work for a tiny amount of data.

When you’re reviewing the cost & runtime of a project, start by looking at the blueprint to see if it’s appropriate. This is where things usually went wrong.

Start by reviewing the blueprint for a query.

In SQL Server Management Studio, click Query, Include Actual Execution Plan, and run this query:

That query gives you a list of databases on the server – but I don’t really care about the results. I’m more interested in the execution plan. Click on the Execution Plan tab of SSMS, and you get a graphical plan showing the work that SQL Server did. We’ll dig more into interpreting execution plans soon, but for now, right-click on the SELECT icon at the top left, and click Properties:

Execution Plan

A new Properties pane will appear on the right side – look at the top of that, and there’s a ton of juicy details:

Plan Properties

  • Compile CPU = the number of milliseconds of CPU time spent building the query plan
  • Compile Memory = well, I don’t think I’ve ever really used this for much
  • Compile Time = the number of milliseconds overall spent building the plan (can be much higher than CPU if we have to wait for things to come back from storage, like the first time we compile a plan in a database after it’s been restored or started up)
  • NonParallelPlanReason = if the query plan couldn’t go parallel and use multiple cores, you’ll get a hint here
  • Reason for Early Termination of Statement Optimization = the architect may decide to go home and start drinking early

These metrics focus on the architect: the one building the query plan. When you think about it, he accomplishes a spectacular amount of work in a short period of time. It’s amazing that we get a working query plan at all that quickly!

This has interesting implications for you.

Plans are built without awareness of execution frequency. SQL Server doesn’t know whether you’re going to run this query just once, or a million times. It just builds a plan once and caches it.

Plan structure isn’t revisited even when it goes wrong. SQL Server doesn’t go back and say, “Whoa, performance on that one was terrible – I thought I was only going to bring back 1 row, but 1,000,000 rows came back, and I should do things differently.” Starting with SQL Server 2017, Microsoft does revisit some parts of the plan, like how much memory gets granted, but the plan shape stays the same. It’s up to you to figure out when the plan shape is incorrect, and coach SQL Server into designing a better plan.

You don’t get great knobs to control architect time. You can’t tell SQL Server, “Listen, I need you to spend a lot more time designing plans on this server because performance here is really important.” There’s a trace flag you can enable to force SQL Server to spend more time building EVERY query plan, but that’s a bad idea since it also includes things like IntelliSense queries and monitoring applications.

In theory, SQL Server will rewrite your query. You’ll come across blog posts that suggest it doesn’t matter how you write your query, but read those posts carefully: they come with caveats. The classic example is when SQL Server runs out of time during plan generation and decides to ship a plan – even though it knows there might be better plans available if it spent more time doing optimization.

In practice, human-readable queries perform better. If a human being can look at the query and get a pretty good idea of what’s going on quickly, then so can SQL Server. If a human being looks at the query and develops a look of confused terror, then SQL Server is probably not going to build a great execution plan either.

Plans stick around longer than you might expect. In a modern server with 64GB RAM or more, plans can stay in cache for days, weeks, and even months. If it was a good execution plan, then that’s a good thing. If it was a bad execution plan, well, that’s why people start doing index rebuilds in order to “fix” the problem. More on that in the next episode.

With this background in mind, next up, let’s review which query plans are in your server’s cache.

DBA Training Plan 10: Managing Index Fragmentation

Brent Ozar
DBA Training Plan, Index Maintenance
14 Comments

In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!)

You might have thought, “Is there an easier way?”

“Is there a way I can just run a job, and have something automatically clean my indexes up?”

DefragAnd that’s how folks start focusing on fragmentation rather than the design of the indexes themselves.

Those of us who got our start on Windows back in the days when it ran on spinning rusty magnetic frisbees called “hard drives” used to run apps to defragment the data. These apps would shuffle bytes around, getting them all lined up into contiguous rows. The idea was that if your data was all in a row, then your spinning rusty magnetic frisbees would be able to read the data faster. See, the way they worked internally was that they had a head that physically moved around the drive, reading one place at a time, just like a record player.

What’s that?

What’s a record player?

<sigh>

Music used to be stored on vinyl discs. No, not the compact ones – those were plastic. This is before that. No, even before tapes. These vinyl discs had little grooves in them that would cause a needle to vibrate, and …

NO I’M NOT MAKING THAT UP, IT WAS REALLY A THING.

Lemme just stop there. The whole “defragment your drives” advice is really, really old, that’s what I’m saying. It stems from a day and age where technology was completely different than it is today. Your SQL Server’s data files probably don’t live on spinning rusty magnetic frisbees anymore, and even when they do, they’re scattered amongst lots of other data files – even from different servers all sharing the same drives. All these different data files are being accessed at the same time. The whole reason we used to defragment our data is just dramatically less relevant today.

Today, a lot of your data is stored in RAM. You know what that first R stands for? Of course you don’t, kid, just like you don’t remember that cars used to have record players. <sigh> RAM doesn’t stand for Sequential Access Memory, and you don’t need all your data pages lined up in a row in RAM. A table’s pages aren’t all lined up in a row in RAM, nor does that matter. (Does fragmentation matter if you hit disk? Yes, but then you’re screwed anyway.)

To make matters worse, a lot of folks try to “fix” fragmentation by setting fill factor lower than 100%, purposely leaving empty space on each 8KB data page so that they can add more data later. When they do that, they:

  • Make the database larger (which also makes their backups, restores, corruption checking, index rebuilds, etc all take longer)
  • Make their memory smaller (because each 8KB data page will have a percentage of empty space, and that space is cached in RAM as well)
  • Make table scans take longer (because more logical reads are involved)
  • Give a unicorn heartburn

So what’s a DBA supposed to do?

Leave the server-level fill factor at the default 100%. If you can prove that the default is causing you a problem on a specific index, I’m okay with lowering it on specific indexes, but I’m going to make you prove you made things better instead of worse. If you want to do that, read Jonathan Kehayias’ post on how to track page splits with Extended Events, and make sure to read the comments too.

Run Ola Hallengren’s index maintenance scripts weekly. We talked about these free tools when we were covering backups and restores, and they’ve got more tricks up their sleeve: index reorganization, rebuilds, and statistics updates. I’m not a fan of their defaults, though: start with these less-aggressive defaults instead. Avoid running ’em more than once a week, though, unless you can prove that rebuilding your indexes & updating your stats is fixing things (rather than making ’em worse.)

And if your mentor told you that you have to run daily index rebuilds in order to fix execution plans, I’ve got good news and bad news. The bad news is that your mentor was wrong – but the good news is that I’ll teach you the real problem over the next couple of episodes.

Wanna learn more? Here are a few more resources for this episode’s topic:

  • Why Defragmenting Your Indexes Isn’t Helping – a 1-hour video where I explain the two kinds of fragmentation, show why one kind does matter, and show in more detail why setting fill factor is such a bad idea
  • What’s So Bad About Shrinking Databases? This common maintenance task actually causes fragmentation, which is especially terrible if you’re doing both this and defragmenting your indexes.
  • Columnstore Online Index Rebuilds – Columnstore indexes need more attention to maintenance. Niko Neugebaeur is the go-to for in-depth columnstore tips, and in this post, he analyzes the online rebuild process and its overhead. (You’re definitely gonna want online index rebuilds when you’re dealing with big data warehouses.)

Now that we’ve got the right indexes in place, the next episode will tackle how queries are executed.

DBA Training Plan 9: My 3 Index Guidelines

Brent Ozar
DBA Training Plan, Indexing
1 Comment

In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn’t need to keep around anymore.

Now that you’ve taken out the trash, let’s have a closer look at what’s left. This time, run sp_BlitzIndex without a Mode parameter:

You get back a prioritized list of index design issues like:

  • Aggressive indexes – where a lot of locking is taking place
  • Hoarder – meaning someone’s been creating indexes like they’re free
  • Indexaphobia – where SQL Server is begging for more indexes
  • Self-loathing heaps with forwarded fetches – causing lots of extra reads for no good reason

When you’re dealing with an existing hand-me-down database that’s been in your company for years, your tables are probably facing a mix of extremes. Some tables don’t have any indexes at all, and we’re scanning the table every time we’re looking for just one row. Some tables have dozens of indexes because folks just created every recommended index willy-nilly.

This can be a little overwhelming at first, but before you start digging into the index design and learning what anti-patterns you’re facing, let’s stop for a second to ask:

Who’s supposed to manage the indexes here?

Whether you’ve got a third party application or one developed internally by a team of developers, there’s always a gray area around who’s supposed to be designing the right indexes. Going down the left side of this grid is a list of job duties involved with designing, maintaining, and hosting a database application:

The top group of tasks – designing tables, writing queries – that part is pretty straightforward. Whoever builds the application does this work. After all, if you suddenly removed a column from their table, you’d be in trouble because the application would stop working.

The bottom group of tasks – provisioning the VM, installing SQL Server, keeping it patched – that’s also pretty straightforward. It’s your job, hahaha – you’re in the DBA Training Plan, and that’s your work. It wouldn’t be fair to expect the app developers to do this job.

The middle group is where it gets sketchy.

Either of you could do it: either the app developers, or the DBA team. It just comes down to the support policy for each application. In some apps, like Microsoft Sharepoint, the customers are forbidden from screwing around with the indexes. If you have a performance problem, you basically have to throw hardware at it. In other apps, like Microsoft Dynamics, customers are encouraged to do their own index designs to support the way they use the application. To learn more about this choice, read my post on who’s allowed to add indexes to ISV apps.

Your job as a DBA is to:

  1. Share the sp_BlitzIndex results with the developers
  2. Say, “It looks like there are a lot of improvements we could make together. Who should drive this effort, me or you?”
  3. After getting the answer, communicate it clearly to the end users, too – as in, “We’ve found a lot of index improvements we could make, and the developers will be driving that effort.” (Or you will! Either way.)

Sometimes, it’ll be somebody else’s problem – but sooner or later, the job duties are gonna fall on your lap. Let’s get started with a few simple guidelines to help get your tables performing well.

Brent’s 3 Index Guidelines

You were assigned my free How to Think Like the Engine video class in a prior homework episode, so I’m going to assume you already know the differences between clustered indexes, nonclustered indexes, and heaps. With that in mind, here are a few index guidelines to start with as you’re reviewing the output of sp_BlitzIndex:

  1. Start every table with a clustered index. There will be times when you don’t need one, but when in doubt, start with one. Only remove it when you can prove that it’s a performance problem.
  2. Aim for around 5 indexes or less per table. The more indexes you add, the more copies of the table you have. Granted, you’re picking which columns are in each copy – but they’re still by no means lightweight. I’ve seen databases where every insert required hundreds of writes to disk because there were hundreds of indexes per table.
  3. Aim for around 5 columns or less per index. The more columns you add, the larger the index becomes. All of those columns have to be updated synchronously whenever the data changes, so in a perfect world, you avoid indexing “hot” fields that change all the time. (Yes, this includes “included” columns – they take up space in the indexes too, and they have to be updated every time they change. They ain’t free.)

These are starting guidelines,
not commandments carved in stone.

Brent Ozar presentingI get it – because I stand up on podiums and present, you think I’m Moses standing up on the mount, holding The Three Index Commandments. I can see why you’re confused – I look a lot like a Biblical character alright.

These are just starting points, meant to help you take the first few steps on your journey. You’re going to find plenty of scenarios along the way when you need to take a different approach. However, until you understand why those guidelines are in place, you want to abide by ’em.

Your next steps are to learn why I wrote those guidelines. These learning resources I’m about to point you to are huge – you could spend the next several months just learning about indexing. It’s up to you how deep you want to go:

If you like learning via books:

  • SQL Server Query Performance Tuning – chapter 8 digs into indexing. It’s several dozen pages, and teaches you most of what you need to know to get started. Plus, if you’re going to invest in a book, I want you to be able to keep gaining from it over time, so this book will pay off in more areas of our DBA Training Plan when I refer to other chapters later.
  • Expert Performance Indexing – a whole book dedicated just to SQL Server indexes.

If you like learning via blog posts:

If you prefer videos & online classes:

Whoa – this is hard work, right? Figuring out the right indexes, testing to make sure they’re getting used – can’t we just defragment our indexes and call it a day? Nope, and I’ll explain why in the next episode.

Brent Ozar with coffee

Hi! I’m Brent Ozar.

I make Microsoft SQL Server go faster. I love teaching, travel, and laughing. I live in California with my wife Erika. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.

Want to advertise here and reach my savvy readers?

Menu
{"cart_token":"","hash":"","cart_data":""}