Posts by Erik Darling

Indexing, SQL Server, Statistics

Here at Brent Ozar Unlimited We have a proud tradition of not blaming index fragmentation for everything. There are points you should deal with it, but they’re probably not 5% and 30% and 1000 pages. But that’s not what this blog post is about. I’m hoping to clarify why we’re more interested in up to…

SQL Server 2014 SP2: DBCC CLONEDATABASE

Last Updated July 12, 2016

Getting developers production data stinks DBAs are stuck in this annoying cycle where they need to give developers production, or production-like data, but… Production data can be huge. Hundreds of gigabytes if you’re lucky, several terabytes if you’re not. Then once it gets there, you have other considerations. Either you lock down Dev, or you…

New Sample Databases From Microsoft

Last Updated July 4, 2016

In celebration of AdventureWorks being out of business for a decade Microsoft has released a couple new databases. Wide World Importers is a fresh-faced start up, probably a drug smuggling front, run out of a basement office next to a temp agency. There’s some JSON involved. No one ordered bikes. It’s a hoot. The OLTP…

Last Day Shenanigans

Last Updated July 4, 2016

To follow up on my NY Times Best Selling blog post On First Day Deal Breakers, here’s something you can do on your last day, as a friendly reminder that everyone is losing a valued team member. Change the color of everyone’s error messages. Dammit. “Change the font too.” –Brent Monstrous. Probably the meanest part…

Availability Group Direct Seeding: Mind Your Backup Jobs

Last Updated February 9, 2017

Always On Availability Groups, Backup and Recovery, SQL Server

I’ll get right to the point While you’re Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it’s one you should be aware of up front. In the screencap below, courtesy of sp_whoisactive there’s a…

TDE and Backup Compression: Together At Last

Last Updated September 21, 2017

Note: THERE ARE BUGS IN THIS FEATURE. Make sure you read all the way through the post to catch the updates from Microsoft as more bugs were found. TDE is one of those things! You either need it, and quickly learn how many things it plays the devil with, or you don’t need it, and…

Availability Group Direct Seeding: TDE’s Frenemy

Last Updated February 9, 2017

Always On Availability Groups, SQL Server

From the Mailbag In another post I did on Direct Seeding, reader Bryan Aubuchon asked if it plays nicely with TDE. I’ll be honest with you, TDE is one of the last things I test interoperability with. It’s annoying that it breaks Instant File Initialization, and mucks up backup compression. But I totally get the…

Availability Group Direct Seeding: Extended Events and DMVs

Last Updated February 9, 2017

Always On Availability Groups, SQL Server

As of this writing, this is all undocumented I’m super interested in this feature, so that won’t deter me too much. There have been a number of questions since Availability Groups became a thing about how to automate adding new databases. All of the solutions were kind of awkward scripts to backup, restore, join, blah…

Availability Group Direct Seeding: How to fix a database that won’t sync

Last Updated February 9, 2017

Always On Availability Groups

This post covers two scenarios You either created a database, and the sync failed for some reason, or a database stopped syncing. Our setup focuses on one where sync breaks immediately, because whatever it’s my blog post. In order to do that, I set up a script to create a bunch of databases, hoping that…

Interview Question Follow-up: How do you respond?

Last Updated June 24, 2016

Normally I’d update the original post But I wanted to add a bit more than was appropriate. For my interview question, I asked how you’d respond to a developer showing you progress they’d made on tuning a sometimes slow stored procedure. While a lot of you gave technically correct answers about the recompile hint, and…

The Worst Way to Judge SQL Server’s HA/DR Features

Last Updated February 13, 2017

Architecture, High Availability, SQL Server

We love to help people plan for disasters We’re not pessimists, we’ve just seen one too many servers go belly up in the middle of the night to think that having only one is a good idea. When people ask us to help them, we have to take a lot into consideration. The first words…

SQL Server 2016: Availability Groups, Direct Seeding, and You.

Last Updated February 9, 2017

Always On Availability Groups, SQL Server

One of my least favorite things about Availability Groups Well, really, this goes for Mirroring and Log Shipping, too. Don’t think you’re special just because you don’t have a half dozen patches and bug fixes per CU. Hah. Showed you! Where was I? Oh yeah. I really didn’t like the backup and restore part. You…

SQL Interview Question: “How do you respond?”

Last Updated June 7, 2016

Brent’s in class this week! So you get me instead. You can just pretend I’m Brent, or that you’re Brent, or that we’re both Brent, or even that we’re all just infinite recursive Brents within Brents. I don’t care. Here’s the setup A new developer has been troubleshooting a sometimes-slow stored procedure, and wants you…

Getting Started With Oracle Week: Creating Indexes and Statistics

Last Updated June 4, 2016

This is not a deep dive If you’re looking for lots of internals and explanations of what happens behind the scenes, don’t read past here. I almost made a READPAST joke. It’s that kind of day. This is just a basic overview of creating some indexes and gathering statistics. Why? Because someone just paid about…

Getting Started With Oracle Week: NULLs and NULL handling

Last Updated June 4, 2016

We’re not so different, you and I In any database platform, you’ll have to deal with NULLs. They’re basically inescapable, even if you own an island. So let’s compare some of the ways they’re handled between Oracle and SQL Server. Twofer If you take a look at the two queries below, there are a couple…

Getting Started With Oracle Week: Aggregating

Last Updated June 4, 2016

I probably should have written this one first Most of these are exactly the same as in SQL Server. There are a whole bunch of interesting analytic functions, most of which should look pretty familiar to anyone who has spent time querying SQL Server. Most, if not all, can be extended to be window functions,…

Getting Started With Oracle Week: Joins

Last Updated June 4, 2016

Oh, THAT relational data Thankfully, most major platforms (mostly) follow the ANSI Standard when it comes to joins. However, not all things are created equal. Oracle didn’t have CROSS and OUTER APPLY until 12c, and I’d reckon they’re only implemented to make porting over from MS easier. It also introduced the LATERAL join at the…

Getting Started With Oracle Week: Generating Test Data

Last Updated June 4, 2016

Bake your own cake Pre-cooked example databases are cool for a lot of things. The first being that everyone can have access to them, so they can follow along with your demos without building tables or inserting a bunch of data. If you mess something up, it’s easy to restore a copy. The main problem…

Is your SAN’s cache killing tempdb?

Last Updated February 13, 2017

SQL Server, Storage, TempDB

Let’s start with definitions Many SANs have caching built in. What kind of cache is important, because if you’re dealing with non-SSD storage underneath, you could be waiting for a really long time for it to respond. Let’s start with some definitions of the most popular caching mechanisms available for SANs. I’m not going to…

First Day Deal Breakers

Last Updated February 12, 2017