Posts by Erik Darling

300 Blogs And Running

Why Bother Blogging? I blog primarily because there’s a Certain Kind of Person that irks me to no end. The kind of person who: Withholds information (you wouldn’t understand anyway) Mocks people for not knowing what they know (oh, you silly things) Uses the people they mock to validate their necessity (you’d be lost without…
Read More

A Surprising Simplification Limitation

When It Comes To Simplification Rob Farley has my favorite material on it. There’s an incredible amount of laziness ingenuity built into the optimizer to keep your servers from doing unnecessary work. That’s why I’d expect a query like this to throw away the join: Transact-SQL SELECT COUNT(u.Id) FROM dbo.Users AS u JOIN dbo.Users AS…
Read More

Can Non-SARGable Predicates Ever Seek?

Development, Indexing
Cheating At Candy Crush The short answer is that yes, they can. But only with a little extra preparation. Before I show you what I mean, we should probably define what’s not SARGable in general. Wrapping columns in functions: ISNULL, COALESCE, LEFT, RIGHT, YEAR, etc. Evaluating predicates against things indexes don’t track: DATEDIFF(YEAR, a_col, b_col),…
Read More

Is Your Database Databasic?

Pumpkin Spice When I’m looking at someone’s server, it’s easy to tell the kind of care it’s been under. We’ve written a lot about servers that have gotten the wrong kind of care. You know, no backups, no checkdb, but someone managed to turn auto create stats off for every database, and enable affinity masking.…
Read More
Pocket Square

First Responder Kit Release: Just When You Think There’s Nothing New Left To Do

T*m* f*r An*th*r F*rst R*spond*r K*t R*l**s*. All joking aside! A big thank you goes out to a few people this go around: @jadarnel27 for not only contributing a bunch of Super Professional T-SQL, but also for writing a web scraping application to compile a list of current SQL Server versions. @nedotter @ktaranov and Aleksey Nagorskiy for putting together…
Read More

Mysterious Forwarded Records

Thinking Of You When people think about Heaps and the problems they can cause, they don’t often think of temp tables and table variables as Heaps. Of course, without a clustered index, any table is a Heap. This isn’t an argument for or against indexing temp tables, but while working with a client we came…
Read More

A Strange Place For A Memory Grant

If You Hang Around Fetish Bars Long Enough… You learn things about memory grants in execution plans. Stuff like the things that usually need memory Hash Join Hash Match Sort Parallelism Exchanges But there’s something that rarely gets discussed, even in the deepest, darkest, dankest dungeons. Let’s loosen the clamps and look at it. Keys…
Read More

Trivial Plans, Simple Parameterization and Check Constraints

Execution Plans, Indexing
Trivial Plans Are Weird They’re the optimizer’s “Yes, Dear”.  The thing is, sometimes “Yes, Dear” can miss some important details. Cost based decisions about operators Potential index improvements Contradiction detection for check constraints Simple parameterization, according to… uh… Official Microsoft documentation? Can only occur in a Trivial Plan. Which makes sense. Simple parameterization is considered…
Read More

Stuff People Tell Me Is The Default

Playing The Odds Assuming that life carries on as usual for a couple more weeks, I’ll have been a consultant here for three years. In that time, I’ve had some really interesting conversations with people, mostly about why things are the way they are on their servers. My favorite response is “isn’t that the default?”,…
Read More

Quick Tips For Debugging Large Stored Procedures

Hell Here! Get Your Fresh Hell, Here! Let’s face it — as far as developer tools go, SSMS is pretty bad. Intellisense? The jokes write themselves. Don’t get me wrong, SSMS is a good management tool, but it’s not a good development tool (and what kind of maniac sticks debug — F6 — next to execute —…
Read More