Posts by Erik Darling

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

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!”…

SQL Server Features I’d Like To See, PostgreSQL Edition

Last Updated October 27, 2015

BUT FRANCE HAS A FREE PONY That’s right. PostgreSQL is basically free. The MIT license is like the Church of England of licenses. They do not care what you do. But I care what they do! If you read the first part of this article, you saw some Oracle features that I wish SQL Server…

SQL Server Features I’d Like To See, Oracle Edition

Last Updated March 18, 2016

Oracle, SQL Server

BUT FRANCE HAS A PONY I really like SQL Server. Most of the time. Okay, so most of the time I like SQL Server most of the time. Don’t get me wrong, if I had to go back through the career-time continuum and pick a RDBMS to work with, I’d probably still choose it over…

Window Functions and Cruel Defaults

Last Updated March 12, 2018

SQL Server, T-SQL

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…

Why You Should Apply (Take It From The New Guy)

Last Updated September 7, 2015

Personal Benchmark I’ve always thought that I’d know I have the right job when, if I were to win a dynastic sum of money from the lottery, or inherit it from a long-lost relative, I would still show up for work every day. This is that job. Seven months ago, when I saw that the…

Is leading an index with a BIT column always bad?

Last Updated March 12, 2018

“Throughout history, slow queries are the normal condition of man. Indexes which permit this norm to be exceeded — here and there, now and then — are the work of an extremely small minority, frequently despised, often condemned, and almost always opposed by all right-thinking people who don’t think bit columns are selective enough to…

Performance Benefits of Unique Indexes

Last Updated June 7, 2020

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

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 February 13, 2017

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…

Logical Query Processing Follow-up

Last Updated July 24, 2015

i like questions! Because I don’t always like talking to myself, and robots are sometimes murderous and insane. So when this one came up in the comments of my previous post, I thought it would make a good follow-up blog. It’s a really good question, and definitely one I found myself asking quite a bit…

Finding Tables with Nonclustered Primary Keys and no Clustered Index

Last Updated February 13, 2017

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

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

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

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…

Indexing for Windowing Functions

Last Updated March 12, 2018

Indexing, SQL Server, T-SQL

Hooray Windowing Functions They do stuff that used to be hard to do, or took weird self-joins or correlated sub-queries with triangular joins to accomplish. That’s when there’s a standalone inequality predicate, usually for getting a running total. With Windowing Functions, a lot of the code complexity and inefficiency is taken out of the picture,…

Forcing Join Order Without Hints

Last Updated May 6, 2015