Posts by Erik Darling

Erik Darling

365 posts

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

Why SQL Developers Keep Making The Same Mistakes

Last Updated June 27, 2018

Erik Darling

Development

Dead Horses I read a lot of SQL blogs, both new and old. What’s striking is how many blogs seem to cover the same subjects from different angles, over and over again. This isn’t to knock anyone’s blogging at all — but what I do want to do is try to proffer an explanation as…

Ola Hallengren’s Scripts Keep Getting Better

Last Updated June 27, 2018

Erik Darling

SQL Server

You Know Him, You Love Him You trust your database backups and integrity checks to him, and he has been KILLING IT on new features lately. A while back, Ola decided to Open Source his scripts on GitHub (Soon to be known as Microsoft LiveHub for Business Essentials). As more and more people embrace GitHub…

Building SQL ConstantCare®: Collecting Query Plans

Last Updated June 24, 2018

Erik Darling

SQL ConstantCare

Coming Soon In an upcoming release, we’ll start collecting query plans from your servers. Since this may result in the transmission of PII, it will be opt-in only. We won’t be getting this by default, because we don’t want anyone to be uncomfortable with the level of data we’re collecting from their servers. This is…

First Responder Kit Release: Like pssdiag, if pssdiag had parents who cared

Last Updated July 18, 2018

Erik Darling

First Responder Kit Updates

I went to the PFE and guess what he told me? Guess what he told me? He said girl you better try fix those spinlocks too No matter what you do, now run this tool You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1620: @AAvella schooled us on Group Policy registry settings. Someday, we’ll get our act…

How The Cloud Turns Performance Tuning Into Cost Savings

Last Updated June 20, 2018

Erik Darling

Cloud Computing

Barely Metal In Ye Olde Olden Dayyes, companies would outline hardware budgets and refresh cycles. Every 3-5 years, they’d lick a thumb and hold it up to the CPU fans. If they felt a soft breeze, they’d buy the same number of CPUs. If they felt a hard breeze, they’d buy a few more. This…

300 Blogs And Running

Last Updated June 9, 2018

Erik Darling

Humor

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…

A Surprising Simplification Limitation

Last Updated June 9, 2018

Erik Darling

Execution Plans

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…

Can Non-SARGable Predicates Ever Seek?

Last Updated October 7, 2018

Erik Darling

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),…

Why You Should Stop Depending On SQL Server Versions In Code

Last Updated June 8, 2018

Erik Darling

Development

It Used To Be That when you wrote scripts that had to work across different versions of SQL Server, you were pretty safe. Microsoft would release a new version, or a Service Pack that had something new in it, and it wouldn’t start showing up in older versions. That’s not so true anymore. Microsoft has…

Indexing Strategies For Cross Apply

Last Updated June 8, 2018

Erik Darling

Execution Plans, Indexing

Slicked Brad The apply operator remains one of my favorite tools, but much like CTEs and windowing functions, they’re not totally magical unless you look at your indexes. Starting with a dead simple query on the Users table, it’d be easy to come up with an ideal index for this one thing. Transact-SQL SELECT u.Id,…

How Table Variables Mess With Parallelism

Last Updated May 30, 2018

Erik Darling

Execution Plans, SQL Server

This Is A Parallel Query Hi There This Is A Temp Table Transact-SQL CREATE TABLE #Posts (OwnerUserId INT); 1 CREATE TABLE #Posts (OwnerUserId INT); This Is A Parallel Insert Into A Temp Table Sort of asterisk (The Insert isn’t parallel, but the part of the plan leading up to it is) This Is A Table…

Is Your Database Databasic?

Last Updated May 30, 2018

Erik Darling

Processes and Practices

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.…

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

Last Updated June 4, 2018

Erik Darling

First Responder Kit, First Responder Kit Updates

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…

Mysterious Forwarded Records

Last Updated May 26, 2018

Erik Darling

Indexing

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…

How Many Kinds Of Joins Are There?

Last Updated May 26, 2018

Erik Darling

Adaptive Joins, Execution Plans

Quite So There are three in SQL Server, not counting the Adaptive Join which is just a placeholder for either Nested Loops or Hash Joins. If you look closely at each of those joins, you’ll see all sorts of interesting things pop up. Hopefully none of them will be Sorts. NOT EXISTS Nested Loops has:…

Behind Every Trivial Plan Is A Good Demo

Last Updated May 26, 2018

Erik Darling

Execution Plans, Indexing

A While Back I started this Q&A on Stack Exchange about ISNULL. It’s one of the most common client issues I see, and putting stuff like that out in a different community to get more opinions on it is valuable. Also, I’m saving up points for a Stack Overflow Swiss Army Knife. That’s what you…

A Strange Place For A Memory Grant

Last Updated January 8, 2019

Erik Darling

Execution Plans, Memory Grants, SQL Server

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…

Trivial Plans, Simple Parameterization and Check Constraints

Last Updated May 17, 2018

Erik Darling

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…

Stuff People Tell Me Is The Default

Last Updated May 21, 2018

Erik Darling

Humor

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?”,…

Quick Tips For Debugging Large Stored Procedures

Last Updated May 16, 2018

Erik Darling

T-SQL

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 —…

Posts by Erik Darling

Save your cart?