Execution Plans

Can SELECT * Make a Query Go…Faster?!?

Last Updated January 7, 2020

Most of the time, you’ll hear advice that you should select as few columns as possible so that SQL Server can do less work. However, one of my clients hit a neat edge case scenario where SELECT * queries actually ran faster than picking a subset of columns. I can’t share the exact query, but I…

How to Make SELECT COUNT(*) Queries Crazy Fast

Last Updated December 22, 2019

Brent Ozar

Columnstore Indexes, Execution Plans

When you run a SELECT COUNT(*), the speed of the results depends a lot on the structure & settings of the database. Let’s do an exploration of the Votes table in the Stack Overflow database, specifically the 2018-06 ~300GB version where the Votes table has 150,784,380 rows taking up ~5.3GB of space. I’m going to measure…

The Two Ways to Fix Non-Sargable Queries

Last Updated December 16, 2019

Brent Ozar

Execution Plans

In the database world, when we say something isn’t sargable, what we mean is that SQL Server is having a tough time with your Search ARGuments. What a sargable query looks like In a perfect world, when you search for something, SQL Server can do an index seek to jump directly to the data you…

How to Think Like the Engine: When a Seek Isn’t

Last Updated November 19, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine, Indexing

In our last episode, I introduced the concept of scan predicates: execution plan operations that weren’t able to seek directly to the rows they needed. Let’s take another query: Transact-SQL DropIndexes; GO CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO SELECT Id FROM dbo.Users WHERE LastAccessDate > '1800/01/01' AND DisplayName = N'Brent Ozar'; GO…

How to Think Like the Engine: Index Column Order Matters a LOT.

Last Updated November 19, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine, Indexing

We’ve been working with the clustered index of the Users table, which is on the Identity column – starts at 1 and goes up to a bajillion: And in a recent episode, we added a wider nonclustered index on LastAccessDate, Id, DisplayName, and Age: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO 123…

How to Think Like the SQL Server Engine: What’s the Tipping Point?

Last Updated November 17, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine

In our last episode, I’d expanded our query to include DisplayName and Age – two columns that weren’t in our nonclustered index: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate > '2018-09-02 04:00' ORDER BY LastAccessDate; 1234 SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate > '2018-09-02 04:00' ORDER BY LastAccessDate; So as a result,…

How to Think Like the SQL Server Engine: So Index Seeks are Great, Right?

Last Updated October 22, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine

In our last episode, we added a nonclustered index, and now even though this query has a filter and an ORDER BY, it hardly did any work at all – just a lightweight index seek: Ah, index seeks – nothing better than that, right? Well, not exactly. Let’s make one tweak to the WHERE clause…

Why Query Plans Can Look Different on Different Servers

Last Updated October 15, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine

In the first post in my How to Think Like the Engine series, I started by doing a pretty simple query: But I noted that if you were following along on your computer, you might see a different execution plan. If I tweak just one thing about my SQL Server, I see a different plan:…

DBA Training Plan 13: Why Do Cached Plans Go Bad?

Last Updated September 5, 2019

Brent Ozar

DBA Training Plan, Execution Plans

It’s a little bit of a trick question: what could make a good query plan turn bad? In the last episode, we used sp_BlitzCache to spot the most resource-intensive, longest-running query plans in your server’s cache. Go run those queries again, right now, and I bet you’re going to see plans in the cache that…

Research Paper Week: Plan Stitch: Harnessing the Best of Many Plans

Last Updated August 11, 2019

Brent Ozar

Execution Plans

Those of you who follow me on Instagram, Twitter, or Facebook know I’m taking most of August off for vacation. Erika and I spent the last couple of weeks in Telluride, had a brief stop in Sacramento for SQL Saturday, and this week we’re touring wineries in Napa and Sonoma. As part of recharging, I…

DBA Training Plan 12: What Query Plans Are In Cache?

Last Updated August 11, 2019

Brent Ozar

DBA Training Plan, Execution Plans, sp_BlitzCache

In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I’ve also talked about the dynamic management views (DMVs) that let you query what’s in SQL Server’s memory. Let’s put those two things together…

DBA Training Plan 11: The Basics of Executing a Query

Last Updated August 15, 2019

Brent Ozar

DBA Training Plan, Execution Plans

Up til now in the DBA Training Plan, we’ve been taking inventory of our servers, making sure the data’s well-protected, and understanding the basics of how the data’s stored in indexes inside those data files. Now, let’s start looking at performance, and let’s start by zooming really far out to think about how SQL Server…

What the Arrow Sizes in Query Plans Really Mean

Last Updated June 21, 2019

Brent Ozar

Execution Plans

Precisely 80.3% of you are going to learn something in this post. Earlier this week, I asked what you thought the arrows in estimated and actual query plans meant. I asked you to just guess without doing any research, and here’s what you answered: There are a lot of different opinions, and I can see…

Poll: What Do You Think The Arrow Sizes in Query Plans Mean?

Last Updated June 18, 2019

Brent Ozar

Execution Plans

Don’t read the documentation – just guess. What do you think the arrow sizes in query plans mean in SSMS 17/18? After you take the poll – just guess – then you can see what other people think. Friday, I’ll share a demo you can use to prove the answer.

Why Order Isn’t Guaranteed Without an ORDER BY

Last Updated May 15, 2019

Brent Ozar

Execution Plans

Start with the Stack Overflow database, with no nonclustered indexes. If you’ve been playing around with demos, run my DropIndexes proc just to clear things out, which leaves the clustered indexes in place. Say you run this query: Transact-SQL SELECT TOP 100 Id, DisplayName FROM dbo.Users; 12 SELECT TOP 100 Id, DisplayName FROM dbo.Users; By default,…

Index scans aren’t always bad, and index seeks aren’t always great.

Last Updated April 16, 2019

Brent Ozar

Execution Plans, Indexing

Somewhere along the way in your career, you were told that: Index seeks are quick, lightweight operations Table scans are ugly, slow operations And ever since, you’ve kept an eye on your execution plans looking for those performance-intensive clustered index scans. When you see ’em, you go root ’em out, believing you’ve got a performance…

Finding Froid’s Limits: Testing Inlined User-Defined Functions

Last Updated June 25, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, SQL Server 2019, T-SQL

This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…

1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

Last Updated May 26, 2020

Brent Ozar

Adaptive Joins, Parameter Sniffing, SQL Server 2019

So far, I’ve talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don’t accurately show what happened. But so far, I’ve been using a simple one-table query – let’s see what happens when I add a join and a supporting index: Transact-SQL CREATE INDEX IX_OwnerUserId_Includes ON…

Parameter Sniffing in SQL Server 2019: Air_Quote_Actual Plans

Last Updated May 26, 2020

Brent Ozar

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

My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them – kinda – with adaptive memory grants. However, the post finished up by talking about how much harder performance troubleshooting will be on 2019 because your query’s memory grant is based on…

Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants

Last Updated May 26, 2020

Brent Ozar

Memory Grants, Parameter Sniffing, SQL Server 2019

This week, I’m demoing SQL Server 2019 features that I’m really excited about, and they all center around a theme we all know and love: parameter sniffing. If you haven’t seen me talk about parameter sniffing before, you’ll probably wanna start with this session from SQLDay in Poland. This week, I’m going to be using…

Execution Plans

Save your cart?