[Video] Watch Brent Tune Queries

Ever wonder somebody else does it? Watch over my shoulder as I spend 9 minutes in PowerPoint explaining the big picture, and then about 40 minutes working on this stored procedure in the StackOverflow2013 database: If you enjoy that, the Watch Brent Tune Queries page has another video and other query examples. Enjoy!
Read More

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

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.

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?

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

Why Order Isn’t Guaranteed Without an ORDER BY

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

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

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