question

kastabeg avatar image
kastabeg asked

sql server rebuild index task problem

Hello everybody, sorry fo my bad english please.

I have sql server: 2019 (RTM-CU15) (KB5008996)

Indexes in my databases were very fragmented (60- 90 %)

I created and executed rebuild index task:

безымянныи.png


Fragmentation of all indexes became ~ 0 %.

I executed rebuild index task again and it started rebuilding of all GOOD (0%) indexes in all tables again.

I don't understand why it's happening , fragmentation is about zero percent.

Thank you.

indexes
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered

How do you know it's actually doing REBUILDs and not just running sys.dm_db_index_physical_stats() behind the scenes to figure out if there's anything to do or not? One easy way to tell is to hit that "VIEW T-SQL" button and look for actual REBUILDs.

As a bit of a sidebar, this tool is the "Easy Button"... unfortunately, it's also connected to the "Waste your disk and memory space" button if most of your Clustered Indexes are keyed in an "ever increasing" manner and your INSERTs are followed shortly after by "ExpAnsive" UPDATEs because newly inserted rows are going to go in at 100% page fullness and there's no room for the expansion caused by the UPDATEs.

I also question the wisdom of waiting until 31% fragmentation because most of your indexes are non-clustered indexes and most of those are NOT ever increasing. That means that the pages got full and started fragmenting quite badly and you let that happen. By the time the indexes have made more of their own free space by splitting pages, you come along and remove all that free space so that travesty can happen all over again instead of preventing the fragmentation by doing a Rebuild just as soon as fragmentation is happening for real, which is when it gets over 1% fragmented.

If you want to learn some stuff about how indexes work and why you shouldn't follow what the whole world has incorrectly been using as supposed "Best Practices" for the last more than 2 decades, what the following 80 minute flick including the part after the Q'n'A. And, it's not just about GUIDs... I just use GUIDs to demonstrate how wrong people have been doing index maintenance.

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



10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.