development

Query Plans: Trivial Optimization vs Simple Parameterization

Last Updated June 16, 2017

Facemaking You know when you think you know something, and the obviousness of it makes you all the more confident that you know it? That’s usually the first sign that there’s a giant gotcha waiting for you. And that’s what happened to me over the weekend. The setup I answered a question. And after I…

Bad Idea Jeans: Multiple Index Hints

Last Updated May 23, 2017

Erik Darling

Bad Idea Jeans, T-SQL

I hate that you can do this Here’s the thing: I’m mostly writing this because I didn’t know you could do it. But it’s cool, because it’ll reinforce some other concepts, and I’ll show you why you shouldn’t do it. I’m talking, of course, about index hints. To be more specific, hinting multiple indexes on…

Don’t Use Scalar Functions in Views.

Last Updated May 1, 2021

Erik Darling

T-SQL

The short story: if your view has a scalar user-defined function it it, any query that calls the view will go single-threaded, even if the query doesn’t reference the scalar function. Now for the long story. Quite often people will inherit and rely on views written back in the dark ages, before people were aware…

Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

Last Updated May 19, 2017

Erik Darling

Adaptive Joins, SQL Server 2017

I think I’ve mentioned that the most fun part of new features is testing them with old ideas to see how they react. It occurred to me that if Adaptive Joins didn’t work with APPLY, I might cry. So, here goes nothin’! Cross Simple Cross Apply…ies can use Adaptive Joins, though at first glance there’s…

How Scalar Functions Can Stop You From Getting Adaptive Joins

Last Updated May 1, 2021

Erik Darling

Adaptive Joins, Execution Plans, SQL Server 2017

If you’re not aware of the performance problems scalar valued functions can (and often do) cause, well, uh… click here. We’ll talk in a few days. If you are, and you’re worried about them crapping on Adaptive Joins, follow along. The big question I had is if various uses of scalar valued functions would inhibit…

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

Last Updated May 12, 2017

Erik Darling

Adaptive Joins, Execution Plans, SQL Server 2017

But is it faster? Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions. A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.…

SQL Server 2017: Interleaved Execution for MSTVFs

Last Updated May 15, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 2017

What I don’t want you to take away from this Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them. Backed by table variables Lots of hidden I/O cost Number of executions may surprise you One important current limitation (May-ish of 2017)…

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

Last Updated May 13, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 2017

Hot Diggety! After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I’d test it out on SQL Server 2016’s latest CU to see if it had been back ported yet. Good news! It has. Obviously. That’s the title of the blog…

SQL Server 2017: Statistics Information Comes To Query Plans

Last Updated May 15, 2017

Erik Darling

Execution Plans, SQL Server 2017, Statistics

Warm fuzzies I think this is how my mom would have felt if I went to college. You see, once you stop cargo culting around index fragmentation, and disk queue length, you start to realize which things are actually important to query performance. Things like statistics, and cardinality estimation. And, sure, having the right indexes…

Adaptive Joins And SARGability

Last Updated May 12, 2017

Erik Darling

Adaptive Joins, SQL Server 2017

There’s a famous saying Non-SARGable predicates don’t get missing index requests. And that’s true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week? You betcha!© One Sided I’m going to paste in some queries, each with something non-SARGable. There are two tables involved: Users and Posts. Only…

Adaptive Joins And Local Variables

Last Updated May 12, 2017

Erik Darling

Adaptive Joins, SQL Server 2017

With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I’m not going to get into Cardinality Estimator math here, we’re just going to look at Adaptive Join plan choice…

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

Last Updated May 12, 2017

Erik Darling

Adaptive Joins, SQL Server 2017

Cheeky If you thought that title sounded familiar, you sure were right. That’s called a classical reference. So here we are, three whole years later, and yet another improvement to the engine promises to fix performance issues forever and ever. While this isn’t exactly an entirely new cardinality estimator, it’s an entirely new branch in…

Look Ma, Adaptive Joins

Last Updated May 12, 2017

Erik Darling

Execution Plans, SQL Server 2017

This probably won’t seem like a big deal soon But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is. I hope Joe Sack has strong ribs. And here’s the tool tip info! Dynamic Duo This…

Hold The FiIter: Startup Expression Predicates

Last Updated April 26, 2017

Erik Darling

Development, SQL Server

Long distance information There was a rather interesting question posted on dba.stackexchange.com recently about CASE expression order of execution with an OR predicate. Mouthful, I know! When I saw it, I got all “I HAVE A REALLY GOOD ANSWER” and started to write demo queries. Then I wrote this blog post instead. Sorry, j.r. —…

SQL Server 2017: query_store_wait_stats

Last Updated May 12, 2017

Erik Darling

Query Store (QDS), SQL Server 2017, Wait Stats

The missing link Back when the Query Store first got announced, I was amazed. The amount of information it gathered and persisted between reboots was such a big improvement over the plan cache, plus being able to force plans was really cool. I was always surprised that wait stats weren’t a part of that, especially…

SQL Server 2017: Quick Rundown

Last Updated May 12, 2017

Erik Darling

SQL Server 2017

ICYMI Playing with SSMS in the lab Microsoft released CTP 2 of SQL Server vNext (well, yesterday today, but who knows when this thing will hit?). There’s some great new stuff in the Engine in this release that I’m just crazy about getting to know better. Going through the Release Notes made me feel things…

Is it ever worth adding indexes to table variables?

Last Updated April 16, 2017

Erik Darling

Execution Plans, Indexing

Disclaimer I found this totally by accident, and it even surprised me. You can probably guess the TL;DR on this is yes, huh? Dude, where’s my table variable? I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret. Okay, so it’s just embarrassing. Anyway. I had…

Replacing ISNULL In A WHERE Clause

Last Updated April 7, 2017

Erik Darling

Execution Plans, T-SQL

I like blogging Really, I do. But sometimes I like a change of pace. Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky…

Fun With Logging Dynamic SQL

Last Updated April 7, 2017

Erik Darling

T-SQL

While working on a demo I thought this might make a fun aside to share on the blog, because working with dynamic SQL can be challenging. Especially if you don’t have a monitoring tool or application profiling to figure out who ran what and when, most of the time you’ll have no idea what went…

Decrypting Insert Query Plans

Last Updated March 25, 2017

Erik Darling

Execution Plans, SQL Server

We’ve all been there Staring at a simple query that has a three mile long execution plan. After the first time, you learn to always look to make sure there’s not a secret view involved. After looking at query plans for modifications involving indexed views, I decided to look at some other fairly common table…