Erik Darling, Author at Brent Ozar Unlimited®

Performance Tuning

Improved diagnostics for query execution plans that involve residual predicate pushdown

December 15, 2015

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 in SQL Server 2012

Performance Tuning

Filtered Indexes: Just Add Includes

December 8, 2015

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…

Performance Tuning

Tracking tempdb growth using Extended Events

December 2, 2015

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, but then I remembered that it's at least 2015, and I should be using Extended Events. Don't worry, there won't be any PowerShell. You can keep reading.

Performance Tuning

Trace Flag 2330: Who needs missing index requests?

November 25, 2015

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 versions. For people still on 2005, you had a Trace Flag: 2330.

Performance Tuning

The case for Query Store in tempdb

November 3, 2015

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.

You can read all about what it does and what you can do with it around the internet. You can be suitably impressed and enchanted by the promise of data that's persisted between restarts, being able to quickly and easily address plan regression issues, and so forth.

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

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

October 14, 2015

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 Oracle. Probably. And, because I don't exclusively grow facial hair from my neck, I wouldn't be allowed to choose PostgreSQL. They'd kick me off the mailing list.

T-SQL & Development

Window Functions and Cruel Defaults

September 29, 2015

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 Awesome
Heck yeah they are. And how. Boy howdy. Etc. You get the point. I'm enthusiastic. What can be cruel about them? Glad you asked!

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

September 8, 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.

Performance Tuning

Is leading an index with a BIT column always bad?

August 26, 2015

“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

August 18, 2015

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

Performance Tuning

Clustered Index key columns in Nonclustered Indexes

August 12, 2015

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 time. A long time from now. When I've raised an army, in accordance with ancient prophecy.

Getting the last good DBCC CHECKDB date

August 5, 2015

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.

Performance Tuning

Finding Tables with Nonclustered Primary Keys and no Clustered Index

July 22, 2015

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 a heap of HEAPs in your database.

Performance Tuning

Logical Query Processing

July 14, 2015

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

July 8, 2015

Before we get started...
I know. I know. BOL. It's documented.

They even show you how to rename a table.

Performance Tuning

Indexing for GROUP BY

June 30, 2015

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! [crayon-6a37e9749ace5863475743/]…

Performance Tuning

Indexing for Windowing Functions

June 17, 2015

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.

Forcing Join Order Without Hints

May 6, 2015

Brent buys lunch for the ladies The purpose of this post is to show a bit of syntax that often gets overlooked in favor of using query hints to force joins to occur in a particular order. We'll start by creating three tables. One for employees, one for orders, and one for items in the…

And Party and Alt Shift

April 22, 2015

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. Pure ALT+SHIFT magic. Hold down both…