Posts by Erik Darling

Erik Darling

365 posts

Restoring tempdb since GETDATE(). Now blogging at ErikDarlingData.com.

From The Server To The SAN

Last Updated January 3, 2019

Erik Darling

Hardware, SQLBits, Storage

Enter SANdman When people buy SANs, it’s often quite a large investment. Whether it’s all SSD, all flash, or there are tiers of storage that different types of data live on, those disks aren’t cheap. When people visualize their SAN, it’s usually just the server and the pool of drives. But there’s some important stuff…

The Pleasure Of Presenting With Joe Obbish

Last Updated November 26, 2020

Erik Darling

Execution Plans, GroupBy Conference, Indexing, Videos

Grouple On At the December 2018 GroupBy I had the honor of co-presenting with one of the smartest guys around, Joe Obbish. Here’s the video of our session, “The Problems With Optional Operators”, and a link to the resources. Enjoy! Resources: Slides and Demos

Database Design Choices To Consider When You’re Worried About Scale

Last Updated January 2, 2019

Erik Darling

Architecture

Tipping the Kilter I’m intentionally avoiding physical/hardware choices in this post, like how much hardware to buy, and scaling out vs. scaling up. It’s not that I don’t care about those things — I do, and they’re important — but most new apps these days are starting in the cloud, or an on-premise VM. In the…

Why Do Some Indexes Create Faster Than Others?

Last Updated January 2, 2019

Erik Darling

Execution Plans, Indexing

To warn you ahead of time, this post is an exploration without an answer (yet). There’s some interesting stuff in here, but no conclusions. If that’s not your kind of post, feel free to skip it. If it is, well, here goes nothin’… Wild, Wild Life Creating indexes is kind of a funny thing. By…

An Idea For Improving DBCC CHECKDB

Last Updated January 2, 2019

Erik Darling

CHECKDB and Corruption

Hard Enough At this point, we’ve all probably got a database that’s a terabyte or more in size. If you’re anything like I was, you’ve got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails. When you’ve got a lot of large databases, normal maintenance is out the…

Fast Food Code

Last Updated January 2, 2019

Erik Darling

SQL Server

Cheap and Easy We’ve all done it. We all know what and where it is, because we’ve probably left a comment for ourselves to go fix it later. It wasn’t me, I swear to myself. Either it was expedient, it seemed to work locally, it was fast on a small amount of data, or we…

When Does SARGability Matter Most?

Last Updated August 9, 2019

Erik Darling

Execution Plans, Indexing

!erehT iH I know what you’re thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster. Just watch the video. For more reading, check out these posts: Sargability: Why %string% Is Slow Optional Parameters and Missing Index Requests Computed Columns: Reversing Data…

Updated First Responder Kit for January 2019: Extraneous Syllables

Last Updated December 26, 2018

Erik Darling

First Responder Kit Updates

Does anyone know what the date really is? I usually don’t, and I often consider how doomed humanity would be if I were the smartest person on the planet during any given era. Anyway, you should go hug someone smarter than you. Unless they have sensory or boundary issues. Or a restraining order against you. Thank…

Query Store And Cruel Defaults

Last Updated December 19, 2018

Erik Darling

Execution Plans, Query Store (QDS)

Gosh Darn Now, look, it’s no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache. For a variety of reasons, though, we run into people who can’t or won’t turn it on, or who had to turn…

Sniffed Nulls and Magic Numbers

Last Updated May 26, 2020

Erik Darling

Parameter Sniffing, SQL Server

I Sniff Your Milkshake Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine. I goes something like this: If this variable is passed in as NULL, substitute it with something else.…

Never Judge A Query By Its Cost

Last Updated December 12, 2018

Erik Darling

Execution Plans

Signs and Numbers When tuning queries, or even finding queries to tune, there’s a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you’re looking at, costs are estimates, and often don’t reflect how long a query runs for…

Have Availability Groups Gotten Easier Lately?

Last Updated December 3, 2018

Erik Darling

Always On Availability Groups, High Availability, SQL Server

Easy Lover I don’t blog a lot about AGs. If we’re being honest (and I do try to be honest with you, dear reader), I just like performance tuning topics way more. When new features get announced for AGs, some of you may ooh and aah, but not me. I Make A Face I don’t…

Indexed View Matching With GROUP BY And DISTINCT

Last Updated November 29, 2018

Erik Darling

Execution Plans, Indexing

Bit Of A Kick I’ve been playing with indexed views a little bit lately for some demos in my Bits Precon. There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT. Fair enough, but you can do GROUP BY. And what’s pretty…

Indexed View Creation And Underlying Indexes

Last Updated November 29, 2018

Erik Darling

Indexing

Accidental Haha While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view. Starting off with no indexes, this query runs in about six seconds. Transact-SQL DECLARE…

Updated First Responder Kit for November 2018: Get Into Sports Dummy

Last Updated November 30, 2018

Erik Darling

First Responder Kit Updates

COACH! I dunno, I just like this picture. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1786 – @dallyhorton made a good point: if we’re gonna warn you about Change Tracking, we should tell you which database it’s enabled for. #1791 – Who do DMV queries think they are, blocking each other? Get over yourselves. #1793 – CheckIds shouldn’t be…

Tales From Overindexing: Too Many One Column Indexes

Last Updated November 23, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Master Plan Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti. Usually, there’s a view involved. Usually. Sometimes, there’s just really weird indexing. Here’s A Thing, Look At It Promises This is a query against one table. No views, no functions, no…

Getting Sneaky With Forced Parameterization

Last Updated November 23, 2018

Erik Darling

Execution Plans

Silly Rules I’ve blogged about some of the silly rules about where Forced Parameterization doesn’t work. One rule that really irked me is this one: The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT…INTO, or FOR XML clauses of a query. TOP and FOR XML, get used, like, everywhere. TOP is pretty obvious in its usage.…

Not So Forced Parameterization

Last Updated November 26, 2018

Erik Darling

Execution Plans

Asking The Wrong Question Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don’t bother asking if it’ll even work when you do. There are a long list of things that are incompatible with Forced Parameterization, on a page that’s pretty hard to find. Now, there’s…

Going to SQLBits in 2019? Come to my pre-con!

Last Updated November 19, 2018

Erik Darling

SQLBits

My Favorite Conference I love SQLBits, and not just because it gets me out of the country for a week. It’s about the best conference a training buck can buy. There’s tons of great technical material without a lot of the marketing schtick that tends to sneak in. Head over here to register now. I’m…

Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster

Last Updated December 29, 2021

Erik Darling

Execution Plans, Foreign Keys

FINALLY… This is the last post I’ll write about foreign keys for a while. Maybe ever. Let’s face it, most developers probably find them more annoying than useful, and if you didn’t implement them when you first started designing your database, you’re not likely to go back and start trying to add them in. Depending…