dba

Window Functions and Cruel Defaults

Last Updated March 12, 2018

My First Post Here… Well, my first technical post, was about how the default index creation method is OFFLINE. If you want that sweet, sweet Enterpri$e Edition ONLINE goodness, you need to specify it. It’s been a while since that one; almost six months to the day. So here’s another one! But Window Functions Are…

Performance Benefits of Unique Indexes

Last Updated June 7, 2020

Erik Darling

SQL Server

SQL server loves unique indexes Why? Because it’s lazy. Just like you. If you had to spend all day flipping pages around, you’d probably be even lazier. Thank Codd someone figured out how to make a computer do it. There’s some code below, along with some screen shots, but… TL;DR SQL is generally pretty happy…

Clustered Index key columns in Nonclustered Indexes

Last Updated March 12, 2018

Erik Darling

Columnstore Indexes, Indexing, SQL Server

Clustered indexes are fundamental And I’m not just saying that because Kendra is my spiritual adviser! They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another…

Getting the last good DBCC CHECKDB date

Last Updated August 15, 2024

Erik Darling

CHECKDB and Corruption, SQL Server

whether it’s a new job or a new (old) server If you’re reading this, you’ve at some point in your career stared at a server, for the first time, with great trepidation. The smarter you are, the greater your trepidation is. The 2nd century mathematician Trepidatius the Wimpy had an equation that described this, but…

Finding Tables with Nonclustered Primary Keys and no Clustered Index

Last Updated February 13, 2017

Erik Darling

Indexing, SQL Server

i’ve seen this happen Especially if you’ve just inherited a database, or started using a vendor application. This can also be the result of inexperienced developers having free reign over index design. Unless you’re running regular health checks on your indexes with something like our sp_BlitzIndex® tool, you might not catch immediately that you have…

Logical Query Processing

Last Updated March 12, 2018

Erik Darling

Execution Plans, SQL Server

You can’t do that on management studio Recently, while working with a client, I did something in a query that they were mystified by. I didn’t think much of it, but I thought it might be useful to you, dear readers, as well. Along with an explanation. Here’s a sample query that takes advantage of…

The sp_rename follies

Last Updated June 29, 2015

Erik Darling

SQL Server

Before we get started… I know. I know. BOL. It’s documented. They even show you how to rename a table. Thanks, sp_rename! But sometimes… You just forget. And as with most simple mistakes, fixing them is… Weird. Here’s what happened to me recently, when I was working on a table swapping demo. Transact-SQL CREATE TABLE…

Indexing for GROUP BY

Last Updated March 12, 2018

Erik Darling

Indexing, SQL Server

It’s not glamorous And on your list of things that aren’t going fast enough, it’s probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you’re performing aggregations on. We’ll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! Transact-SQL…

And Party and Alt Shift

Last Updated April 14, 2015

Erik Darling

SQL Server

This is a cool SSMS trick I picked up a while back Though not nearly as far back as I wish I had. It’s so cool I made a .gif of it in action. When you’re done putting your socks back on, I’ll tell you how it works. This .gif was brought to you by…

SELECT INTO and non-nullable columns

Last Updated April 9, 2015

Erik Darling

SQL Server

SELECT…INTO is one of my favorite SQL Server features. It’s great for creating table skeletons with false WHERE clauses (1=2), moving a real table to a staged/temp location for testing, etc. In SQL Server 2014 It acquired the ability to go parallel, which is pretty neat, but that’s not what we’re talking about here. It…

Online Index Creation and Cruel Defaults

Last Updated February 13, 2017

Erik Darling

Indexing, SQL Server

Have you ever been watching queries crawl, feeling sort of helpless because you can’t change them? And even if you could, it’s hitting a new custom column in an ISV table that no one bothered to tell you about. I’ve been there. I’ve been pomade deep there. I’ve also had my Robot tell me: “there’s…

When Your DBA Job Sucks: The Phases of Burnout

Last Updated February 12, 2017

Kendra Little

Professional Development

When your databases always look like this, eventually you feel the same way. Even when you love being a DBA, working the job can turn into a grind. There’s many times when you’re up all night troubleshooting just to face another day at the office handling user complaints. You hear at the water cooler that…