Indexing

Identical twins

Yet Another Way Missing Index Requests are Misleading

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

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

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?

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?

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

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
Menu
{"cart_token":"","hash":"","cart_data":""}