Posts by Erik Darling

Erik Darling

365 posts

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

Adventures In Foreign Keys 4: How to Index Foreign Keys

Last Updated December 29, 2021

Erik Darling

Execution Plans, Foreign Keys

This week, we’re all about foreign keys. So far, we set up the Stack Overflow database to get ready, then tried to set up relationships, and encountered cascading locking issues. Dawn Of The Data I don’t know how long the recommendation to index your foreign keys has been a thing, but I generally find it useful…

Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly

Last Updated December 29, 2021

Erik Darling

Execution Plans, Foreign Keys, Locking, Blocking, and Isolation Levels

Legendreary In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up. I didn’t even get to the point where I wanted to test all those relationships. But there’s an even worse surprise waiting for you if…

Adventures In Foreign Keys 2: Common Foreign Key Errors

Last Updated December 29, 2021

Erik Darling

Execution Plans, Foreign Keys

This week, we’re all about foreign keys. Yesterday’s post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships. You Had Two Jobs! Let’s say I wanted to implement Foreign Keys to do two things If a user deletes their account, all of their badges, comments, and…

Adventures In Foreign Keys 1: Setting Up Foreign Keys in Stack Overflow

Last Updated December 29, 2021

Erik Darling

Execution Plans, Foreign Keys

In A Foreign Key, In A Foreign Table Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then…). The same goes for check constraints, too. Thankfully, things like Primary Keys and Unique Constraints are sort…

The Curse of Cursor Options

Last Updated November 2, 2018

Erik Darling

Execution Plans

Red Skies At Night I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there’s a decent reason, something like:…

SUM, AVG, and arithmetic overflow

Last Updated October 26, 2018

Erik Darling

T-SQL

You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error. Transact-SQL SELECT u.Id, u.DisplayName, SUM(p.Score) AS SumPostScore, AVG(c.Score) AS SumCommentScore FROM dbo.Users AS u JOIN dbo.Posts…

Filtered Indexes vs Parameterization (Again)

Last Updated October 21, 2018

Erik Darling

Execution Plans, Indexing

At First I Was Like… This won’t work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they’re from stored procedures, dynamic SQL, or in databases with forced parameterization enabled. Unfortunately, it seems limited to filtering out NULLs…

Batch Mode For Row Store: Does It Fix Parameter Sniffing?

Last Updated May 26, 2020

Erik Darling

Batch Mode, Execution Plans, Indexing, Parameter Sniffing

Snorting The Future SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback. These optimizer tricks have…

Batch Mode For Row Store: What Does It Help With?

Last Updated January 8, 2019

Erik Darling

Batch Mode, Memory Grants

SQL Server 2019 Introduces something pretty cool: the ability for row store indexes to be processed in batch mode. Batch mode is, of course, the preferred processing realm of column store indexes. If you’re still in the dark about column store, you might wanna start by reading up on Niko Neugebauer and Joe Obbish’s blogs about it.…

Skewing Parallelism For Fun And Profit

Last Updated October 5, 2018

Erik Darling

Execution Plans

What Is Skewed Parallelism? When queries go parallel, some assumptions get made: There’s enough work to keep multiple threads busy Each thread will get an equal amount of work to do The ‘equal amount of work’ part is particularly important, because in a parallel plan, each thread gets an equal share of memory up front.…

Index Tuning Week: Missed Missing Index Opportunities

Last Updated September 28, 2018

Erik Darling

Execution Plans, Indexing

A Reminder With a new baby in the house, I often find myself comparing her to various things. Barnacles. Ham. Pugs. No, I swear, I love her. SQL Server Has A Baby, Too That baby is the optimizer, and when it starts crying, it uses missing index requests to tell you something is wrong. Usually.…

Index Tuning Week: Fixing Nonaligned Indexes On Partitioned Tables

Last Updated September 28, 2018

Erik Darling

SQL Server

Unquam Oblite This post will not change your life, but it will help me remember something. When you decide to partition a table to take advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, or you have an existing partitioned table that takes advantage of data management features, because IT IS NOT…

Making The Query Plan Profile Hint More Useful

Last Updated October 4, 2018

Erik Darling

Execution Plans

Way Back When I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name — query_plan_profile. There are currently some rather unfortunate limitations to the event in the GUI. Right now, there’s no query plan tab so you…

First Responder Kit Release: Fingers Crossed!

Last Updated October 2, 2018

Erik Darling

First Responder Kit Updates

This is a small release just to make sure you don’t forget about us. We know you’re busy. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1755 – @MisterZeus added a new check for alerts that don’t send information out. I mean, is that even an alert? #1766 – @jadarnel27 added some code to help folks who have a standard (non-sa)…

The New Lightweight Query Plan Profile Hint

Last Updated September 28, 2018

Erik Darling

Execution Plans

Recent Updates To SQL Server 2016 and 2017 introduced a new USE HINT that lets you direct an actual execution plan to a new Extended Event, called query_plan_profile. The hint by itself doesn’t do anything, and the XE by itself doesn’t do anything. You need to have both. Alright then, let’s do that. Sessions Assuming you’re on…

A Simple Stored Procedure Pattern To Avoid

Last Updated May 26, 2020

Erik Darling

Execution Plans, Parameter Sniffing, SQL Server

Get Yourself Together This is one of the most common patterns that I see in stored procedures. I’m going to simplify things a bit, but hopefully you’ll get enough to identify it when you’re looking at your own code. Here’s the stored procedure: Transact-SQL CREATE OR ALTER PROCEDURE dbo.you_are_not_clever ( @cd DATETIME NULL) AS BEGIN…

One Hundred Percent CPU

Last Updated September 12, 2018

Erik Darling

Humor

Raise Your Hand If You’ve ever wanted to play a prank on your co-workers, but just didn’t have a any ideas that didn’t involve exploding Hot Pockets. Now you have something even less safe than molten cheese squirts! A stored procedure that pushes CPUs to 100%. All of’em. Transact-SQL CREATE OR ALTER PROCEDURE dbo._keep_it_100 AS…

Quirks When Working With Extended Events To Track Locks

Last Updated September 13, 2018

Erik Darling

SQL Server

Worse Than Mobile Browsing I have a love/hate relationship with Extended Events. Yes, they’re powerful. Yes, you can track interesting things. But they’re just not intuitive, like, 10 years on. Part of the problem is that the narrative from Microsoft has been that it’s a full replacement for Profiler and Traces. How many monitoring tool…

Can Forced Parameterization Go Wrong?

Last Updated May 26, 2020

Erik Darling

Execution Plans, Parameter Sniffing, SQL Server

App Like That If you’ve got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice. Brent will be sitting on one shoulder telling you to use Forced Parameterization. I’ll be on the other shoulder asking if you’ve really thought this whole thing through while you…

Announcing SQL Server 2019

Last Updated September 10, 2018

Erik Darling

SQL Server 2019

TEASE Who Let The Docs Out? Ignite must be coming up. If you head over to Microsoft’s GitHub repo, you can peruse around for stuff updated recently. Maybe you’ll create an account. Maybe you’ll start contributing to open source projects. Maybe you’ll quietly slip into a world of solitude for days on end. Happy Saturday!…

Posts by Erik Darling

Save your cart?