Indexing

  1. Home
  2. Indexing
  3. (Page 3)
Identical twins

Yet Another Way Missing Index Requests are Misleading

Indexing
5 Comments
Graduates of my Mastering Index Tuning class will already be familiar with the handful of ways the missing index DMVs and plan suggestions are just utterly insane. Let’s add another oddity to the mix: the usage counts aren’t necessarily correct, either. To prove it, Let’s take MattDM’s Stack Exchange query, “Who brings in the crowds?” He’s…
Read More

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

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
Aborted index creation

What happens when you cancel or kill a resumable index creation?

Indexing
22 Comments
SQL Server 2019 adds resumable online index creation, and it’s pretty spiffy: Transact-SQL CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1); 12 CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName)  WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1); Those parameters mean: ONLINE = ON means you’ve got the money for…
Read More

Are nulls stored in a nonclustered index?

Indexing
11 Comments
When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it: Transact-SQL CREATE TABLE dbo.Employees ( ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, EmployeeName VARCHAR(50) NULL ); GO INSERT INTO…
Read More

Can deleting rows make a table…bigger?

Indexing
21 Comments
Michael J. Swart posted an interesting question: he had a large table with 7.5 billion rows and 5 indexes. When he deleted 10 million rows, he noticed that the indexes were getting larger, not smaller. Here’s one way that deletes can cause a table to grow: The rows were originally written when the database didn’t…
Read More
Fundamentals of Index Tuning

New Fundamentals of Index Tuning Course

Indexing
2 Comments
You’re a developer who needs to make your application go faster, and you don’t want to (or can’t!) change the queries. You’ve never been to an official training class, and your manager isn’t about to spring for travel. In my new Fundamentals of Index Tuning course, you’ll learn: How to design indexes for a query…
Read More

When Does SARGability Matter Most?

!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
No longer uses the index

Do Functions Stop You From Using Indexes?

Indexing, T-SQL
5 Comments
Say I’ve got a function in my WHERE clause: Transact-SQL SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; 123 SELECT DisplayName  FROM dbo.Users  WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; If I have an index on DisplayName, will SQL Server use it? Sure: Function in the WHERE clause Even though SQL Server can’t seek to “Brent Ozar,” it will…
Read More

Indexed View Creation And Underlying Indexes

Indexing
1 Comment
Accidental Haha While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view. Starting off with no indexes, this query runs in about six seconds. Transact-SQL DECLARE…
Read More
Menu