Product has been added to your cart.

Execution Plans

  1. Home
  3. Development
  5. Execution Plans
    6. (Page 2)

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

Brent Ozar
Execution Plans, How to Think Like the SQL Server Engine, Indexing
5 Comments
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…
Read More

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

Brent Ozar
Execution Plans, How to Think Like the SQL Server Engine, Indexing
2 Comments
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…
Read More

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

Brent Ozar
Execution Plans, How to Think Like the SQL Server Engine
5 Comments
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,…
Read More
sp_BlitzCache query warnings

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

Brent Ozar
DBA Training Plan, Execution Plans, sp_BlitzCache
5 Comments
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…
Read More

Why Order Isn’t Guaranteed Without an ORDER BY

Brent Ozar
Execution Plans
7 Comments
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,…
Read More

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

Brent Ozar
Execution Plans, Indexing
9 Comments
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…
Read More
Froid

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

Brent Ozar
Execution Plans, Parameter Sniffing, SQL Server 2019, T-SQL
16 Comments
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…
Read More
1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

Brent Ozar
Adaptive Joins, Parameter Sniffing, SQL Server 2019
1 Comment
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…
Read More

When Does SARGability Matter Most?

Erik Darling
Execution Plans, Indexing
7 Comments
!erehT iH I know what you’re thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster. Just watch the video.   For more reading, check out these posts: Sargability: Why %string% Is Slow Optional Parameters and Missing Index Requests Computed Columns: Reversing Data…
Read More
Menu