Product has been added to your cart.

dba

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…

Implicit vs. Explicit Conversion

Last Updated March 12, 2018

Everyone knows Implicit Conversion is bad It can ruin SARGability, defeat index usage, and burn up your CPU like it needs some Valtrex. But what about explicit conversion? Is there any overhead? Turns out, SQL is just as happy with explicit conversion as it is with passing in the correct datatype in the first place.…

Why monitoring SQL Server is more important than ever

Last Updated February 13, 2017

Moving parts SQL Server keeps on growing. With every new edition, you get more features, feature enhancements, and uh, “feature enhancements”. As I’m writing this, SQL Server 2005 is less than a week away from support ending, and SQL Server 2016 is up to RC2. Brent’s retrospective post got me thinking a bit. We went…

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…

Stats Week: Messin’ With Statistics

Last Updated March 23, 2017

SQL Server, Statistics

If there’s one thing living in Texas has taught me It’s that people are very paranoid that you may Mess With it. Even in Austin, where the citizenry demand weirdness, they are vehemently opposed to any form of Messing, unless it results in mayonnaise-based dipping sauce. Me? I like Messing With stuff. Today we’re going…

Indexes Helping Indexes

Last Updated February 13, 2017

Indexing, SQL Server

This post isn’t going to solve any problems for you It was just something I stumbled on that struck me as funny, while working on a demo for something else. Brent didn’t believe it at first, so I thought I’d share with the class. Blog. You, Your Name Here. Hiya! So there I was, creating…

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…

Unique Indexes and Row Modifications: Weird

Last Updated March 12, 2018

Indexing, SQL Server

Confession time This started off with me reading a blurb in the release notes about SQL Server 2016 CTP 3.3. The blurb in question is about statistics. They’re so cool! Do they get fragmented? NO! Stop trying to defragment them, you little monkey. Autostats improvements in CTP 3.3 Previously, statistics were automatically recalculated when the…

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,…

Improved diagnostics for query execution plans that involve residual predicate pushdown

Last Updated February 13, 2017

Execution Plans, SQL Server

I love stuff like this! Even though it’s not on my list of dream features, it’s pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown…

Filtered Indexes: Just Add Includes

Last Updated May 9, 2018

Indexing, SQL Server

I found a quirky thing recently While playing with filtered indexes, I noticed something odd. By ‘playing with’ I mean ‘calling them horrible names’ and ‘admiring the way other platforms implemented them‘. I sort of wrote about a similar topic in discussing indexing for windowing functions. It turns out that a recent annoyance could also…

Tracking tempdb growth using Extended Events

Last Updated February 13, 2017

SQL Server, TempDB

Everyone knows tempdb is a wierd [sic] place Brent refers to it as a public toilet. I agree with that sentiment. It will let anyone in to do whatever they want. Recently I was trying to track down what was causing tempdb’s log to grow. I was thinking about using a Trace to do it,…

Trace Flag 2330: Who needs missing index requests?

Last Updated February 13, 2017

Bad Idea Jeans, Indexing, SQL Server

Hey, remember 2005? What a great year for… not SQL Server. Mirroring was still a Service Pack away, and there was an issue with spinlock contention on OPT_IDX_STATS or SPL_OPT_IDX_STATS. The KB for it is over here, and it’s pretty explicit that the issue was fixed in 2008, and didn’t carry over to any later…

The case for Query Store in tempdb

Last Updated December 4, 2020

Execution Plans, Query Store (QDS), TempDB

Query Store is so cool Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI. “Try looking into that place where you dare not look! You’ll find me there, staring out at you!”…