Development

Five Mistakes Performance Tuners Make

Last Updated July 27, 2017

Development, Execution Plans, Indexing, SQL Server

There’s no Top in the title And that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that. This is just a short collection of things that I’ve done in the past, and still find people doing today when troubleshooting performance. Sure, this list could…

Locking When There’s Nothing To Lock

Last Updated July 27, 2017

Erik Darling

Development, Execution Plans, Indexing, Locking, Blocking, and Isolation Levels

Demo Day We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome. Where things get tricky is with locking demos. Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t…

[Video] Last Season’s Performance Tuning Techniques

Last Updated August 1, 2017

Brent Ozar

#SQLPass, Development, Videos

You’re kinda-sorta comfortable doing performance tuning on SQL Server. You’ve read a few blogs, you monitor Page Life Expectancy, you rebuild your indexes, and you add an index here or there. However, you haven’t been to a day-long performance tuning class yet, and you’re wondering what you’re missing. Thing is, in SQL Server, performance tuning…

Other People’s Blog Posts I Talk About the Most

Last Updated July 27, 2017

Brent Ozar

Development

In my work with clients and classes, some blog posts come up a LOT. Forcing a Parallel Query Execution Plan by Paul White – Paul’s posts usually cover execution plan components in incredible detail, but the real gem in this one is the section called “Parallelism-Inhibiting Components.” If your T-SQL includes this stuff, the whole…

What Do You Think About ORMs?

Last Updated July 12, 2017

Tara Kizer

Development

I was recently asked what I thought about ORMs (Entity Framework, NHibernate, Dapper, etc) while we were looking at implicit conversion warnings in execution plans. Before I answered the question, I let them know that my answer is based on being a production DBA and the numerous performance problems I have had to troubleshoot that…

Last Season’s Performance Tuning Techniques: Slides & Demos

Last Updated August 1, 2017

Brent Ozar

#SQLPass, Development

Right now, Erik and I are presenting at the 24 Hours of PASS. We’re talking about Last Season’s Performance Tuning Techniques: Wanna play along with us as we show how your performance skills might be a little out of date? Here’s the demos and the slides: Last Season's Performance Tuning Techniques from Brent Ozar Fill…

sp_BlitzCache: Eventual Compatibility With Azure

Last Updated November 16, 2017

Erik Darling

Execution Plans, Microsoft Azure, sp_BlitzCache

Better than nothing Yesterday it was announced that global temp tables were available in public preview for Azure. That means that unmodified versions of sp_BlitzCache (which uses a global temp table to hold a whole bunch of information) will be compatible up there. This is a nice addition for Azure, and an even nicer addition…

SQL 2016 SP1 Shows You Wait Stats in Execution Plans. Or Does It? [UPDATED]

Last Updated October 5, 2017

Brent Ozar

Execution Plans, Wait Stats

SQL Server 2016 Service Pack 1 brought us performance tuners all kinds of shiny goodies – like the ability to see wait stats inside a query’s actual execution plan. I was really excited when this first came out, but I keep seeing some real oddities. Let’s keep this one really simple: I’m using the Stack…

What I Love About the Stack Overflow Database

Last Updated July 7, 2017

Brent Ozar

Development

I’ve been using the Stack Overflow public database for my performance tuning demos for a few years now, and I just keep finding more reasons to love it. It’s got a few simple tables with a lot of data. The main ones are Users, Posts (questions & answers), Comments, Votes, and Badges, ranging from 1GB…

A Better Way To Select Star

Last Updated November 20, 2018

Erik Darling

Development, Execution Plans, Indexing, SQL Server

Mindless Self Promotion I liked writing this blog post so much that I wrote an entire presentation on it. If you’d like to see it at GroupBy, click the link and vote. Update: You can watch the video for this session here. Much has been written about this It’s probably one of the lowest hanging…

Query Plans: Trivial Optimization vs Simple Parameterization

Last Updated June 16, 2017

Erik Darling

Execution Plans, Indexing, SQL Server

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…

Introducing sp_BlitzQueryStore

Last Updated November 16, 2017

Erik Darling

Execution Plans, First Responder Kit, Query Store (QDS), sp_BlitzQueryStore

Oh, so you’re the one While SQL Server 2016 adoption is still pretty low, and 2017 still quite isn’t out yet, that’s no reason to not invest a few days of dev time into a stored procedure that is only usable by the smallest portion of SQL Server users. Right? Look, someone around here has…

Why Your Biggest Query Plans Don’t Show Up in Some DMVs

Last Updated May 26, 2017

Brent Ozar

Bad Idea Jeans, Execution Plans

SQL Server has three ways to get execution plans from the plan cache: sys.dm_exec_query_plan – around since SQL Server 2005 sys.dm_exec_text_query_plan – added in 2005 SP2 sys.query_store_plan – new in SQL Server 2016 And there’s an important problem with the first one. To show it, let’s take one of my many bad ideas, building queries that take 12+…

Logical Errors And Query Performance

Last Updated May 26, 2017

Erik Darling

Execution Plans

Is that a nice way of saying typo? People will often tell you to clearly alias your tables, and they’re right. It will make them more readable and understandable to whomever has to read your code next, puzzling over the 52 self joins and WHERE clause that starts off with 1 = 2. It can…

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…

What Do You Want to See in a Free Query Store Tool?

Last Updated May 19, 2017

Erik Darling

Execution Plans, Monitoring, Query Store (QDS), SQL Server

I love Query Plans No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah. It’s a beast, but it’s my kind of beast. At around 4500 lines of code, when you want to make…

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