Indexing

Home
Indexing

SQLBits Attendees: Here Are Your Prerequisites for Mastering Index Tuning.

Last Updated September 1, 2020

Howdy folks. It’s time for SQLBits! This year, I’m teaching a one-day Mastering Index Tuning workshop from 9AM-5PM London time, and you can join in online. Pricing is £300 for the one-day workshop, or £800 for a full conference pass. You can register here. Once you’re registered, here’s what you need to do: Watch my…

[Video] How to Think Like Clippy (Subtitle: Watch Brent Wear Another Costume)

Last Updated July 27, 2020

Brent Ozar

Indexing, Videos

Remember Clippy, the Microsoft Office assistant from the late 1990s? He would pop up at the slightest provocation and offer to help you do something – usually completely unrelated to the task you were trying to accomplish. Sadly, the Office team told Clippy that he didn’t make the stack rankings cut, so he relocated over…

When a Columnstore Index Makes Your Query Fail

Last Updated June 8, 2020

Brent Ozar

Columnstore Indexes

If you wanna count the number of rows in a table, I’ve usually said, “It doesn’t matter what you put inside the COUNT() – it can be COUNT(*), COUNT(Id), COUNT(1), or COUNT(‘Chocula’), or even COUNT(1/0) – it all works the same.” And that was true right up until I added a columnstore index. Start with…

SQL ConstantCare® Now Tells You Which Indexes to Drop.

Last Updated May 25, 2020

Brent Ozar

Indexing, SQL ConstantCare

Indexes slow down data changes. SQL Server doesn’t have the concept of asynchronous indexes – copies of the data that it could update later when it’s bored. It has to keep every nonclustered index up to date, every single time you change data, like a delete/update/insert. Mo’ indexes, mo’ problems. That’s why in my Mastering…

No, You Can’t Calculate the Tipping Point with Simple Percentages.

Last Updated May 7, 2020

Brent Ozar

Indexing

This morning, Greg Gonzalez (who I respect) posted about visualizing the tipping point with Plan Explorer (a product I respect), and he wrote: The tipping point is the threshold at which a query plan will “tip” from seeking a non-covering nonclustered index to scanning the clustered index or heap. The basic formula is: A clustered…

If You Have Foreign Keys, Don’t Update Fields That Aren’t Changing.

Last Updated December 29, 2021

Brent Ozar

Foreign Keys, T-SQL

If you update a row without actually changing its contents, does it still hurt? Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That’s a great post, and you should read it. But foreign keys add another level of…

Why Ordering Isn’t Guaranteed Without an ORDER BY

Last Updated April 11, 2020

Brent Ozar

Indexing, T-SQL

If your query doesn’t have an ORDER BY clause, you can’t reliably predict the order of your results over time. Sure, it’s going to look predictable at first, but down the road, as things change – the indexes, the table, the server’s configuration, the size of your data – you can end up with some ugly…

[Video] What Percent Complete Is That Index Build?

Last Updated March 24, 2020

Brent Ozar

Index Maintenance, SQL Server 2017, Videos

SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but…only if the data isn’t changing. But of course your data is changing – that’s the whole point of doing these operations as resumable. If they weren’t changing, we could just let the operations…

Things to Consider When SQL Server Asks for an Index

Last Updated March 1, 2020

Brent Ozar

Indexing, SQL ConstantCare

One of the things I love about SQL Server is that during query plan compilation, it takes a moment to consider whether an index would help the query you’re running. Regular blog readers will know that I make a lot of jokes about the quality of these recommendations – they’re often incredibly bad – but…

Making TRY_CAST and TRY_CONVERT Queries Faster with Indexed Computed Columns

Last Updated March 1, 2020

Brent Ozar

Computed columns, Execution Plans, T-SQL

I know this is gonna sound crazy, but let’s say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let’s say your application frequently ran a query looking for dates in that column, like this: Transact-SQL SELECT *…

WHERE GETDATE() BETWEEN StartDate AND COALESCE(CancelDate, EndDate) Is Even Harder to Tune.

Last Updated February 12, 2020

Brent Ozar

Development, Indexing

In my last post, we started with a memberships table, and each membership had start & end dates. I’m going to create the table and populate it with everyone having an active membership – their StartDate is the same as their Stack Overflow account creation date, and their EndDate is around a year or two…

WHERE GETDATE() BETWEEN StartDate AND EndDate Is Hard to Tune.

Last Updated February 19, 2020

Brent Ozar

Development, Indexing

Say you’ve got a memberships (or policies) table, and each membership has start & end dates: Transact-SQL USE StackOverflow; GO DROP TABLE IF EXISTS dbo.UsersMemberships; CREATE TABLE dbo.UsersMemberships (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, UserId INT NOT NULL, StartDate DATETIME NOT NULL, EndDate DATETIME NOT NULL, CancelledEarlyDate DATETIME NULL); GO 1234567891011 USE StackOverflow;GODROP TABLE IF…

Building SQL ConstantCare: Let’s Tune an Index in Postgres.

Last Updated January 14, 2020

Brent Ozar

Indexing, PostgreSQL

This week, our SQL ConstantCare® back end services started having some query timeout issues. Hey, we’re database people – we can do this, right? Granted, I work with Microsoft SQL Server most of the time, but we host our data in Amazon Aurora Postgres – is a query just a query and an index just…

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…

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…

The Many Problems with SQL Server’s Index Recommendations

Last Updated November 13, 2019

Brent Ozar

Indexing

These days, I jump back & forth a lot between SQL Server and Postgres. (We use AWS Aurora Postgres to store SQL ConstantCare®‘s data.) Whenever I come back to the sweet, sweet graphical execution plans in SQL Server Management Studio, I breathe a sigh of relief. Dang, these things are so much easier for me…

How to Think Like the SQL Server Engine: Included Columns Aren’t Free.

Last Updated November 11, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

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?

Last Updated November 11, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

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

Last Updated November 9, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

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…

Indexing

Save your cart?