Indexing

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

July 27, 2020

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

June 10, 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.

May 26, 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.

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…

Why Ordering Isn’t Guaranteed Without an ORDER BY

April 22, 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?

March 26, 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

March 10, 2020

Brent Ozar

Indexing, SQL ConstantCare

No Comments

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

March 5, 2020

Brent Ozar

Computed columns, Execution Plans, T-SQL

No Comments

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.

February 19, 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.

February 18, 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.

January 20, 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

December 26, 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

November 21, 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.

November 20, 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

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.

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?

November 7, 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

November 6, 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…

How to Think Like the SQL Server Engine: What’s a Key Lookup?

October 22, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

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: Adding a Nonclustered Index

October 17, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Indexing

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…