Product has been added to your cart.

Execution Plans

What’s Faster: IN or OR? Columnstore Edition

Last Updated September 11, 2023

Columnstore Indexes, Execution Plans

Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019: SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID IN (1, 2, 3); SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1 OR CustomerID = 2 OR CustomerID = 3; 123456789 SELECT *FROM Sales.SalesOrderHeaderWHERE CustomerID IN (1,…

This Week, Fundamentals of Query Tuning Classes are Free!

Last Updated October 9, 2022

Execution Plans

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose. This week is Fundamentals of Query Tuning. Last week’s class had hands-on labs you had to follow, but this week’s doesn’t. You’re welcome…

Estimated and Actual Plans Can Have Different Shapes.

Last Updated August 4, 2022

Execution Plans

A reader posted a question for Office Hours: Hi Brent, What is your take on Hugo Kornelis’s explanation of execution plan naming. As her his explanation, estimated exec plan is simply an execution plan whereas actual execution plan = execution plan+run-time stats. Do you agree that the naming is flawed and confusing? – Yourbiggestfan I…

SQL Server Pop Quiz: A Key Lookup Without the Index

Last Updated July 17, 2022

Execution Plans

How do we get this to show up in an execution plan: Without having an index seek or index scan operation on the same table? Take any copy of the Stack Overflow database and write a query that will produce a key lookup on the Users, table, but it’s not allowed to have an index…

I Wish SQL Server Warned About Hard-Coded Estimates.

Last Updated June 23, 2022

Execution Plans

I wish we got a yellow bang on execution plans when SQL Server was making up an estimate out of thin air. I’ll give you an example: if you compare two columns on the same table, looking to find rows where they’re equal, SQL Server has a hard-coded estimate that 10% of the rows will…

SQL Server 2022 Tells You Why A Query Can’t Go Parallel.

Last Updated June 7, 2022

Execution Plans, SQL Server 2022

Until 2022, when a query couldn’t go parallel, all we got was a really cryptic note in the execution plan properties saying NonParallelPlanReason = CouldNotGenerateValidParallelPlan. But starting with SQL Server 2022, even when I’m running under older compatibility levels: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */ GO DECLARE @TableVariable TABLE(Total BIGINT); INSERT…

“Index Seek” Doesn’t Mean Much.

Last Updated May 4, 2022

Execution Plans

When you see “index seek” on an execution plan, that doesn’t mean SQL Server is jumping to exactly the row you’re looking for. It only means that SQL Server is seeking on the first column of the index. This is especially misleading on indexes where the first column isn’t very selective. To explain, I’ll take…

Mastering Query Tuning

5 Ways to Measure a Query

Last Updated November 1, 2021

Execution Plans

In my free How to Think Like the Engine class, we start out by measuring query performance using logical reads. That’s the number of 8KB data pages that SQL Server has to read in order to find your query’s results. That’s the measure I use the most because generally speaking, the less data your server…

What Is a Cost-Based Optimizer?

Last Updated September 17, 2021

Execution Plans

When you execute a query, the database server has to figure out things like: Which table to process first Which index to use on that table Whether to seek on that index or scan it Which table to process next How to join the data between those two tables When to sort the data For…

“I’m getting index seeks. Why are my row estimates still wrong?”

Last Updated June 6, 2021

Execution Plans

If you’ve got good indexes to support your query, and statistics to help SQL Server guess how many rows will come back, how can SQL Server still come up with terribly incorrect row estimates? To demonstrate, I’ll use the 2018-06 version of the Stack Overflow database, but any recent version will work as long as…

What’s the Buggiest Feature in SQL Server?

Last Updated May 5, 2021

Execution Plans, SQL Server

Not the one you like the least, or the one that requires the most work. What’s the one that you can’t use because it’s just flat out busted? For me, it’s SSMS’s Live Query Statistics, aka live query plans. The idea is so awesome – you click Query, Include Live Query Statistics, and then when you…

Execution Plans Don’t Have the Yellow Bang They Really Need.

Last Updated April 29, 2021

Execution Plans

When SQL Server is purely making an estimate up out of thin air, with no freakin’ idea what the real answer is, it doesn’t give you any kind of warning. It just produces an estimate that looks scientifically accurate – until you know how it’s actually getting calculated. Let’s ask SQL Server how many users have…

Changing Statistics Cause Longer Compilation Times

Last Updated February 13, 2021

Execution Plans, Statistics

I need to be up front with you, dear reader, and tell you that you’re probably never going to need to know this. I try to blog about stuff people need to know to get their job done – things that will be genuinely useful in your day-to-day performance tuning and management of SQL Server.…

Can You Get Parameter Sniffing on Updates and Deletes?

Last Updated January 27, 2021

Execution Plans

Sure you can – parameter sniffing is everywhere. Anytime that you have one piece of code that needs to handle a varying number of rows, you’re probably gonna have to worry about parameter sniffing. I’ll start with any Stack Overflow database and run a couple of non-parameterized update statements. I’m using literals here, not parameters:…

Finding the One Query to Tune in a Multi-Query Batch

Last Updated November 30, 2020

Execution Plans

When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on? For starters, do NOT believe the percentages you see in execution plans. Let’s take a classic performance tuning issue: scalar functions. I’ll start with the Stack Overflow database – if…

MAXDOP Isn’t Really MAXDOP. It’s More Like DOP.

Last Updated November 30, 2020

Execution Plans

Here’s how Books Online describes the Max Degree of Parallelism setting: You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. And here’s what the SQL Server 2019 setup screen says: When an instance of SQL Server runs on a computer that has more…

Bonfire of the vanities

“UPDATE, INSERT, and DELETE are not normally processed in parallel”

Last Updated September 8, 2020

Execution Plans

Years ago, when troubleshooting performance, I stumbled across this Microsoft documentation on parallel query processing that says: Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE…

Watch Brent Tune Queries at SQLSaturday Oslo

Last Updated August 29, 2020

Execution Plans, Videos

This morning, I had the honor of presenting virtually at SQLSaturday Oslo. The session title, Watch Brent Tune Queries, explains it all. Enjoy! In the session, I used the 2018-06 Stack Overflow database, but any recent full-sized copy (not the 2010-2013 copies) will work. Here’s the query I worked with: /* Watch Brent Tune Queries:…

Date Tables are Great for Users, but Not So Great for Performance

Last Updated August 5, 2020

Execution Plans, Videos

Date tables help users filter data based on day of week, holiday, this year vs last year, and other kinds of filters that business users usually wanna add to their reports. If you haven’t used a date table before, here are a couple of primers on how to create one and use it: Creating a…

Parallelism Can Make Queries Perform Worse.

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing

While I was building lab queries for my all-new Fundamentals of Parameter Sniffing course – first live one is next week, still time to get in – I ran across a query with delightfully terrible behavior. I’m always torn when I build the hands-on labs. I want to make the challenges easy enough that you…