Posts by Erik Darling

Erik Darling

364 posts

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

Functions Can Still Use Indexes, Kinda.

Last Updated May 1, 2021

Erik Darling

Execution Plans, Indexing, SQL Server

This sentence gets repeated a lot. You know the one: “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Functions can use indexes, but even so, that doesn’t mean that they’re going to perform as well as queries without functions. Which Indexes? In the SUPERUSER database (yeah, I know, I’m…

Other People’s GitHubs: Fork, Clone, and Pull

Last Updated January 30, 2018

Erik Darling

SQL Server

Let’s say you found an interesting repository (code project) on Github, and you wanted to make a contribution to it. Here’s how it works. Profilin’ Did you know that Richie has a stored procedure call sp_DataProfile? I didn’t even know he had a website until today. I wish he’d talk about this stuff more. In…

First Responder Kit Release: Sunday, Boring Sunday

Last Updated May 9, 2018

Erik Darling

First Responder Kit Updates

This is a modified limited release. After a few new feature releases, we’re achieving stasis. You can download the updated FirstResponderKit.zip here. There are a couple points from the last release that I really need to emphasize: sp_BlitzCache got some new columns added to the output for tempdb spills. If you want to keep logging it…

Computed Columns: Reversing Data For Easier Searching

Last Updated January 19, 2018

Erik Darling

Computed columns, Development, SQL Server

During Training We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching. What’s back-searching? It’s a word I just made up. The easiest example to think about and demo is Social Security Numbers. One security requirement is…

How Much Can One Row Change A Plan, Part 4

Last Updated January 17, 2018

Erik Darling

Execution Plans, SQL Server

Monte Carlo Or Bust In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans. To figure some stuff out about why the plans changed, let’s focus on the Nested Loops Join plan. Why? Because when I force the Hash Join plan, the results are no different. I get…

How Much Can One Row Change A Plan, Part 3

Last Updated January 17, 2018

Erik Darling

Execution Plans, SQL Server

All The Rage We’ve asked how much one column and even one row can change a plan before. The doctor says I’m not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received. So here we go again, ONCE MORE INTO THE FRAY!…

[Video] The Junior DBA Workout Plan

Last Updated January 13, 2018

Erik Darling

Videos

Squat, deadlift, overhead press, and bench press. In the gym, it’s easy to figure out what you need to be doing to get stronger. When you show up to work, you’re overwhelmed by all the things happening on your server(s), and you can’t figure out where to begin. In this session, we’ll talk about building…

When Does Index Size Change Index Choice?

Last Updated January 13, 2018

Erik Darling

Indexing, SQL Server

Size Matters? One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use. The tl;dr here is that it may make more of a difference when it comes to index width (think the number of columns in an index) than it…

SQL Server 2017 CU3 adds optimizer row goal information in query plans

Last Updated March 22, 2018

Erik Darling

Execution Plans, SQL Server 2017

Row Whats? Like it or not, the optimizer has row goals. We’re not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I’ll be adding it to the FRK in an upcoming release. I am going to give you a round up…

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Last Updated May 26, 2020

Erik Darling

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2017

Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…

SQL Server 2017 CU3 add a new CXCONSUMER wait type

Last Updated August 9, 2020

Erik Darling

Execution Plans, SQL Server 2017, Wait Stats

SQL Server 2017’s new CXCONSUMER wait type was originally announced by Microsoft’s Pedro Lopes, and now it’s out. Here’s what it means for performance tuners. According to Pedro, this wait is the “safe” type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn’t evenly balanced across all of our cores. Pedro blogged…

First Responder Kit Release: It’s Too Cold Not To Do A Release

Last Updated May 9, 2018

Erik Darling

First Responder Kit Updates

You look just like a snake when you crawl like a worm. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1310: @CferMN reported quite an odd conversion error! Turns out, division was a better idea than multiplication. #1341: We we missing the newest version of Windows, and we had a broken link. Thanks to @JaySpatzfor letting us know!…

SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

Last Updated December 9, 2018

Erik Darling

Execution Plans, SQL Server 2017

Update This seems to finally be fixed as of CU12 for SQL Server 2017. If you’re not already patched up, head over to the link to get there. Estimated Plans Need Not Apply This is, of course, only available in actual plans. All together now: If a query uses one or more user-defined scalar functions…

Automating Complacency

Last Updated January 5, 2018

Erik Darling

Processes and Practices, Professional Development

There’s no Latin word for Robot I’m all for automation, especially when it comes to the boring stuff. As awesome as the automation routines we have for that are, I still see people messing it up. Right now, there’s no Clippy/HAL mashup glaring at you, telling you that what you’re doing is a bad idea.…

The Perils Of VSS Snaps

Last Updated December 23, 2017

Erik Darling

Backup and Recovery

So much, so often Ah, backups. Why are they so tough to get right? You start taking them, you find out you’re not taking enough of them, or that they’re not the right kind, or that you’re not using checksums or compression, or that you’re not storing them in the right place, or that the…

Heaps, Deletes, and Optimistic Isolation Levels

Last Updated March 12, 2018

Erik Darling

Indexing, SQL Server

The Humble Heap If you don’t know this by now, I’m going to shovel it at you: If you have a table with no clustered index (a Heap), and you delete rows from it, the resulting empty pages may not be deallocated. You’ll have a table with a bunch of empty pages in it, and…

Sizing A New Server? Start With Maintenance.

Last Updated December 16, 2017

Erik Darling

Backup and Recovery, CHECKDB and Corruption, First Responder Kit, SQL Server

Clientele Most of out customers are on slightly (ahem) older (AHEM) hardware, and they have big questions: How do we know what kind of hardware we need? How do we load test it? How do we know what to change to make it better? The load test question is interesting, particularly because getting a reliable…

Introducing sp_BlitzLock: For Troubleshooting SQL Server Deadlocks

Last Updated September 26, 2023

Erik Darling

Deadlocks, First Responder Kit, sp_BlitzLock

Deadlocks are hard – especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain. sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause. sp_BlitzLock works…

Can you prevent deletes and inserts without a WHERE clause from running?

Last Updated December 12, 2017

Erik Darling

Bad Idea Jeans, Humor

File this under bad idea thong In the tradition of Klaus Aschenbrenner wanting to prevent people from writing SELECT * queries, I thought it might be nice to prevent people from running deletes or updates without a WHERE clause. In a vaguely scientific way. Now, I don’t really condone this. It just seemed funny at…

What Your SQL Server Is Really Waiting On: YOU

Last Updated December 9, 2017

Erik Darling

SQL Server

Land of Confusion Most SQL Servers out there have never had anyone do a thorough, targeted assessment on them, the way we do our Critical Cares. This leads to a lot of astray troubleshooting, which may make sense for profiling other problems, but not SQL Server. You know, the kind of tips that come from…