Posts by Erik Darling

I Know You Have Yours Gone Fishin’ I have mine. When I go looking at a server for the first time, I wanna know about stuff like corruption, stack dumps, 15 second I/O warnings, frozen I/O, and non-yielding schedulers. You know, the real meaty, hair on fire stuff. Hm. Meat on fire. I’m hungry. Despite…

Where Clustered Index Keys Dare

Last Updated May 7, 2018

Indexing, SQL Server

Colonel Clustered We’ve blogged a couple times about how clustered index key columns get stored in your nonclustered indexes: here and here. But where they get stored is a matter of weird SQL trivia. You see, it depends on how you define your nonclustered index. “It Depends” We all scream for dependencies! Hooray! If you…

When Query Plans Lie Part 2

Last Updated May 7, 2018

Execution Plans, Indexing, SQL Server

Getting Weirder In Part 1, we looked at how query plans can tell us little lies. Now we’re going to look at how those little lies can turn into bigger lies. Adding An Index Right now, this is our query: Transact-SQL SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS u WHERE u.Reputation >= 100000; 123 SELECT u.UpVotes,…

When Query Plans Lie Part 1

Last Updated May 7, 2018

Freaking Out Brent Is a full time job. Some days I can get him to hit the desk drawer tequila, and other days I crash and burn. This day was so monumental I almost hired someone to do an action movie narration of this blog post. Let’s Start Easy Over in Stack Overflow, we’re going…

Parameter Fluid Optimization

Last Updated May 4, 2018

Optimizer Studies I don’t have a real job, which means that if something strikes my fancy, like staring at a spot on the wall for an hour, I can do it. Occasionally things are a bit more inspired, and so I go poking around what SMRT PPL might be up to. All you nice people…

Hey, That’s Not My Sort!

Last Updated April 28, 2018

Understand Your Plan Mr. Optimizer does the rock n roll hoochie koo with Mrs. Optimizer, c. 1953. When reading query plans, you may sometimes see a sort when you didn’t explicitly ask for data to be sorted. Sometimes they show up to support order-preserving operators like stream aggregates, merge joins, or segments. Other times, they…

I’m speaking at SQL Saturday NYC

Last Updated April 27, 2018

No, Not Brent Just me. And a few dozen other people, on May 19 (2018). I have sessions on the query optimizer, and all the stuff I think is cool in SQL Server 2017. Away Days You know, I get it. The weather is just finally getting nice in these parts. No one wants to…

First Responder Kit Release: Darn May Showers

Last Updated May 9, 2018

First Responder Kit, First Responder Kit Updates

There’s usually something that I think is funny in this space. Instead, I’m going to extend my already extensive day of civic duty by letting you know something interesting about jury duty. Sometimes, if you show up when they ask you to, they can make you come back for a second day if they…

How Computed Columns Can Cause Blocking

Last Updated May 1, 2021

Locking, Blocking, and Isolation Levels

The short story: when you add a computed column that references another table, like with a scalar user-defined function, you can end up causing concurrency problems even when people didn’t really want to go see that other table, and that table is locked by someone else. Here’s my query: Transact-SQL SELECT Id, u.DisplayName, u.BadgeCount FROM…

So You Wanna Debug SQL Server Part 2

Last Updated April 19, 2018

Birth Control Debugging, and, heck, even learning about debugging, is a time-consuming and often unhelpful process when it comes to tracking down issues in SQL Server. It is arduous and tedious, and yields little chance of making you any friends. So why write about it? Mostly because no one else is willing to — at…

Index Key Column Order And Supporting Sorts

Last Updated April 12, 2018

Whatever Man When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access other key and included columns to satisfy other parts of…

Parking Garages And Predicates

Last Updated April 12, 2018

Execution Plans, Indexing, SQL Server

Hotels on Wheels Parking garages and predicates have a “lot” in common. GET IT? No no but seriously. They do. If you’ve ever lost your ticket, forgotten where you’ve parked, or just driven around hopelessly waiting to see an empty spot, you probably wish there were a better way to do things. Transact-SQL CREATE TABLE…

How To Break SQL Server’s XML Data Collection

Last Updated April 5, 2018

Humor

Oh, XML When we first met, it was about 2011. I had to load files full of you into a table. It wasn’t so bad. Seriously. You were well-formed, and didn’t change from file to file. Even using SQL Server 2005, I could take you. Later on, we got really close when I awkwardly started…

Why sp_BlitzLock Can’t Show Complete Columnstore Deadlock Information

Last Updated June 25, 2021

Deadlocks

People seem to care about deadlocks That’s why I wrote sp_BlitzLock. Why not sp_BlitzDeadlock? Well, I had this song stuck in my head. But enough about that. While poking around with things recently, I created a pretty typical deadlock on a table, but this one had a clustered column store index on it. Of course,…

The Many Mysteries of Merge Joins

Last Updated April 8, 2018

Not A Single Picture Of A Zipper Image humbly borrowed from https://70srichard.wordpress.com/2014/12/17/beverly-hills-cop/ There are some interesting things about Merge Joins, and Merge Join plans that I figured I’d blog about. Merge joins have at least one interesting attribute, and may add some weird stuff to your query plans. It’s not that I think they’re bad,…

First Responder Kit Release: Driving Miss Data

Last Updated May 9, 2018

First Responder Kit Updates

Ugh. Bungabase slow. When Grog try count meat, Glug eat meat. Then Grog get hungry and Glug try count meat. Bungalock. Need big head make bungabase fast. You big head? You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1459: A while back, Brent blogged about implicit transactions. In the comments, a smart commenter named Dan pointed…

Hash Join Memory Grant Factors

Last Updated January 8, 2019

Execution Plans, Memory Grants

Buskets Much like Sorts, Hash Joins require some amount of memory to operate efficiently — without spilling, or spilling too much. And to a similar degree, the number of rows and columns passed to the Hashing operator matter where the memory grant is concerned. This doesn’t mean Hashing is bad, but you may need to…

Missing Index Impact and Join Type

Last Updated March 22, 2018

Indexing

Just Another Way No matter how you delve into missing index requests — whether it’s the plan level, DMV analysis, or (forgive me for saying it), DTA, the requests will generally be the same. They’ll prioritize equality predicates, the columns may or not may be in the right order, the columns may or may not…

So You Wanna Debug SQL Server Part 1

Last Updated March 24, 2018

Start here! This is a gentle introduction to getting set up and started. I’m nowhere near as good at this as I want to be, but I’ve been inspired by a couple wizards: Bob the Ward and Paul the White. The first thing you need to do is go download WinDbg (or the debugger of your…

Table Valued Parameters: Unexpected Parameter Sniffing

Last Updated May 26, 2020