Product has been added to your cart.

sql server

Questions You Should Be Asking About Your Backups

Last Updated February 9, 2017

Backup and Recovery, SQL Server

#basicbackup So you’re taking backups! That’s great. Lots of people aren’t, and that’s not so great. Lots of people think they are, but haven’t bothered to see if the jobs are succeeding, or if their backups are even valid. That’s not great, either; it’s about the same as not taking them to begin with. If…

Set Statistics… Profile?

Last Updated February 13, 2017

Forgotten, But Not Gone Two of the best built-in tools to figure out if your query tuning efforts are headed in the right direction are SET STATISTICS TIME ON and SET STATISTICS IO ON. If you’re really fancy, you can use SET STATISTICS TIME, IO ON. By fancy I mean lazy. By lazy I mean…

The Law Of The Minimum

Last Updated February 13, 2017

I like science fiction Not enough to go to conventions, or memorize every line, but I have some opinions on things. Dune is probably my favorite series of stories in the genre. Yes, I think Paul would beat Luke in a fight. Sorry, Star Wars people. He’s just kind of a wimp. Throw grenade, digress One…

What To Do If sp_BlitzFirst Warns About High Compilations

Last Updated November 16, 2017

First Responder Kit, sp_BlitzFirst, SQL Server

Compiles Aren’t The Same As Recompiles If you’re seeing high RECOMPILES, this post isn’t for you. We’ll talk about the differences between compiles and recompiles, but not how to troubleshoot recompiles. Recompiles mean one of two obvious things: You have a RECOMPILE hint at the stored procedure or statement level, or SQL found a reason…

DBA Days: Pre-maintenance Window Rituals

Last Updated February 12, 2017

Processes and Practices

Like a DBA batter’s box While we were hanging out in between webcasts at Dell, we got to talking about stuff we used to do before maintenance windows. Things that brought us good luck, warded off blue screens and failed patches, and just generally made us feel better about working well into the early AM…

DBA Days: Killing TempDB softly

Last Updated February 13, 2017

Bad Idea Jeans, SQL Server

Don’t make me regret publishing this This really will take a server down immediately. You can restart it, as we learned during DBA Days, but… If you also go ahead and make this an Agent job that runs at startup, there are additional challenges. Like every time Agent starts up, your server goes down. Score…

DBA Days: Chaos Sloth

Last Updated February 13, 2017

Bad Idea Jeans, Humor, SQL Server

I love a bad idea I mean, just look at me. I’m covered in them! So when Brent wanted to randomly make his server act crappy, I wrote a script for it. Usual caveats here. Don’t run this in production. I’m not responsible for anything you do after you hit copy and paste. In fact,…

Memory Grants Added to sp_BlitzCache and sp_BlitzFirst

Last Updated January 8, 2019

First Responder Kit, Memory Grants, sp_BlitzCache, sp_BlitzFirst

Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…

Why Not Just Create Statistics?

Last Updated February 13, 2017

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…

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…

One weird trick for managing a bunch of servers

Last Updated April 28, 2016

Let’s face it, most people don’t have just one SQL Server How many they tell Microsoft they have is another matter, but let the record show that I don’t condone licensing dishonesty. But going one step further, most places… Well, they’re ‘lucky’ if they have one DBA, never mind a team. Everyone else: Give me…

Psst… Hey buddy, you want some E-Discovery?

Last Updated February 9, 2017

kCura Relativity, SQL Server

One year ago today! Well, sort of one year ago. Who knows when this thing will get published? Only God and Brent. And part of the calendar year fell during a leap year, which was just plain cruel, like a two part Buffy episode where the second part is the first episode of the next…

Changes to auto update stats thresholds in SQL Server 2016

Last Updated February 25, 2016

TL;DR As of CTP 3.3, it’s the same behavior as Trace Flag 2371, in 2008 R2 SP1 and onward. That basically means that the bigger your table is, the fewer rows need to be modified before an automatic statistics update occurs. Slightly longer… The change was announced over here. At first I thought, woah, cool,…

Moving Databases with ALTER DATABASE

Last Updated February 18, 2016

True story A long time ago, I had to actually do stuff to databases. One thing I had to do was move data files around. Maybe some knucklehead had put system databases on the C: drive, or a LUN was filling up, or we got a new LUN. You know, whatever. Natural curiosity oft leads…

How often should I run DBCC CHECKDB?

Last Updated February 13, 2017

CHECKDB and Corruption, SQL Server

There’s an old DBA saying… May you already have a backup restored A half hour before your boss knows there’s corruption What? There’s no such thing as old DBA sayings? Well, maybe if you all said something other than “no” once in a while, you’d be more quotable. Hmpf. Anyway, this is a serious question! And…

No but really, how big should my log file be?

Last Updated July 13, 2018

Most of you are going to hate this And TL;DR, there’s a script at the end of the post. But like The Monster At The End Of This Book, it’s worth it not to skip the middle. There are about a billion but-what-ifs that could come into play. I can’t possibly answer all of those…

A funny thing happened on my way to set up Mirroring…

Last Updated February 9, 2017

Database Mirroring, SQL Server

I’ve set up Mirroring about a billion times I’m not bragging about that. I’d rather say that I set up a billion AGs, and not one of them ever failed. But then I’d be lying to you; those things fail like government programs. One thing I’d never done, though, is set up Mirroring with a…

Don’t Use Scalar User-Defined Functions in Computed Columns.

Last Updated May 6, 2020

Scalar functions in computed columns cause all queries that hit that table to execute serially. But it gets worse! Scalar functions in computed columns cause index maintenance to go single-threaded. If you’re running Expensive Edition, index rebuilds can be both online and parallel. That’s pretty cool, because it keeps all your gadgets and gizmos mostly…

Still Serial After All These Years

Last Updated March 29, 2018

With each new version of SQL comes a slew of new stuff While some changes are cosmetic, others bewildering, and the rest falling somewhere between “who cares about JSON?” and “OH MY GOD TAKE MY MONEY!”, but not really my money, because I only buy developer edition. Aaron Bertrand has done a better job finding,…