Live classes start in 1d 07h 54m — Save your seat

Index Maintenance and Fragmentation

Fragmentation assessment, rebuilds, reorganizing, and maintenance policy.

44 associated posts43 primary posts

Database Animations: You’ve Heard of Page Splits. Here’s What They Really Mean.

July 22, 2026

Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.

You've heard that page splits are bad, and they're an indication that your table design is making your storage work too hard. You've heard that the right answer to fix it is adjusting fill factor lower, or doing regular index maintenance.

Read more about Database Animations: You’ve Heard of Page Splits. Here’s What They Really Mean. 15 comments — Join the discussion

[Video] Office Hours: Philosophical Questions at the Waterfall of the Gods

March 6, 2025

I'm in Iceland this week, land of fire and ice, a place I keep coming back to over the years. It's one of my favorite places on earth with stunning scenery, nice folks, and great restaurants. I paused at Godafoss to take your top-voted questions from Pollgab.com/room/brento.

https://www.youtube.com/watch?v=nGoE17cSdl8

Read more about [Video] Office Hours: Philosophical Questions at the Waterfall of the Gods Be the first to comment

Index Rebuilds Make Even Less Sense with ADR & RCSI.

January 29, 2025

Accelerated Database Recovery (ADR) is a database-level feature that makes transaction rollbacks nearly instantaneous. Here's how it works.

Without ADR, when you update a row, SQL Server copies the old values into the transaction log and updates the row in-place. If you roll that transaction back, SQL Server has to fetch the old values from the transaction log, then apply them to the row in-place. The more rows you've affected, the longer your transaction will take.

Read more about Index Rebuilds Make Even Less Sense with ADR & RCSI. 6 comments — Join the discussion

[Video] What Percent Complete Is That Index Build?

March 26, 2020

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 finish.

Read more about [Video] What Percent Complete Is That Index Build? 6 comments — Join the discussion
Performance Tuning

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

March 25, 2019

SQL Server 2019 adds resumable online index creation, and it's pretty spiffy:
[crayon-6ae69060c609d247974697/]
Those parameters mean:

ONLINE = ON means you've got the money for Enterprise Edition
RESUMABLE = ON means you can pause the index creation and start it again later
MAX_DURATION = 1 means work for 1 minute, and then gracefully pause yourself to pick up again later

Read more about What happens when you cancel or kill a resumable index creation? 22 comments — Join the discussion

[Video] Office Hours 2018/1/3 (With Transcriptions)

January 6, 2018

This week Brent and Richie discuss the latest big Intel CPU security flaw, distributed transactions, rebuilding indexes, SQL Server health check, GDPR regulations, Availability Groups issues, improving server performance, patching for instances that share DBs from multiple applications, using heaps, and more...

Here’s the video on YouTube:

Read more about [Video] Office Hours 2018/1/3 (With Transcriptions) Be the first to comment