How to Think Like the SQL Server Engine

How to Think Like the SQL Server Engine, Videos

You’ve heard of my free How to Think Like the Engine class, and maybe you even started watching it, but…it has slides, and you hate slides. Wanna see me do the whole thing in Management Studio, starting with an empty query window and writing the whole thing out from scratch live? This session is for you.…

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

November 21, 2019

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.

November 20, 2019

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: Included Columns Aren’t Free.

November 11, 2019

In our last cliffhanger episode, I said that if we ran this query: Transact-SQL UPDATE dbo.Users SET Age = Age + 1 WHERE Id = 643; 123 UPDATE dbo.Users SET Age = Age + 1 WHERE Id = 643; And we had this index: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); 12 CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON…

How to Think Like the SQL Server Engine: Should Columns Go In the Key or the Includes?

November 7, 2019

In our last episode, in between crab rangoons, I had you create either one of these two indexes: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id) INCLUDE (DisplayName, Age); GO CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO 1234567 CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id) INCLUDE (DisplayName, Age);GOCREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age);GO And I said…

How to Think Like the SQL Server Engine: Building Wider Indexes to Deal with Bad T-SQL

November 6, 2019

In our last episode, we were running into problems with these two queries: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN '2018-08-31' AND '2018-09-01' ORDER BY LastAccessDate; SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE CAST(LastAccessDate AS DATE) = '2018-08-31' ORDER BY LastAccessDate; 123456789 SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN…

How to Think Like the SQL Server Engine: When Statistics Don’t Help

November 5, 2019

How to Think Like the SQL Server Engine, Statistics

In our last episode, we saw how SQL Server estimates row count using statistics. Let’s write two slightly different versions of our query – this time, only looking for a single day’s worth of users – and see how its estimations go: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN '2018-08-27' AND…

How to Think Like the SQL Server Engine: Using Statistics to Build Query Plans

November 4, 2019

How to Think Like the SQL Server Engine, Statistics

In our last episode, SQL Server was picking between index seeks and table scans, dancing along the tipping point to figure out which one would be more efficient for a query. One of my favorite things about SQL Server is the sheer number of things it has to consider when building a query plan. It…

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

October 23, 2019

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: What’s a Key Lookup?

October 22, 2019

In our last couple of queries, we’ve been using a simple query to find the Ids of everyone who accessed the system since mid-2014: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014-07-01' ORDER BY LastAccessDate; 1234 SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014-07-01' ORDER BY LastAccessDate; But Ids alone aren’t all that useful – so let’s…

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

October 21, 2019

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…

How to Think Like the SQL Server Engine: Adding a Nonclustered Index

October 17, 2019

When we left off in the last post, our users kept running this query, and they want it to be really fast: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; 1234 SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; Let’s pre-bake the data by creating a copy of…

How to Think Like the SQL Server Engine: Running a Query Repeatedly

October 16, 2019

Earlier in this series, we ran a query with ORDER BY, and we realized that it was CPU-intensive work that tripled the cost of the query: Now, let’s run that query a bunch of times. In SSMS, you can add numbers after the GO, and SSMS will run your query a number of times. I’ll…

How to Think Like the SQL Server Engine: The Perils of SELECT *

October 15, 2019

In our last post, we ran a query with an ORDER BY, but we only got one column in the SELECT: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; 1234 SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; The estimated cost was about $18 Query Bucks because SQL…

How to Think Like the SQL Server Engine: Adding an ORDER BY

October 10, 2019

We started out the How to Think Like the Engine series with a simple query with a WHERE clause: Now let’s add an ORDER BY: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; 1234 SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; Here’s the updated plan – note…

Why Query Plans Can Look Different on Different Servers

October 9, 2019

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

How to Think Like the SQL Server Engine

October 7, 2019