{"id":355080,"date":"2024-05-20T23:01:19","date_gmt":"2024-05-20T23:01:19","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=355080"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=355080","title":{"rendered":"MSSQL: Table Rebuild and Reorg in highload 24\/7 Environments<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

How do you deal with index fragmentation if your SQL server is working in high load environment with 24\/7 workload without any maintenance window? What are the best practices for index rebuild and index reorganize? What is better? What is possible if you have only Standard Edition on some servers? But first, let’s debunk few myths.<\/p>\n

Myth 1.<\/strong> We use SSD (or super duper storage), so we should not care about the fragmentation. False. Index rebuild compactifies a table, with compression it makes it sometimes several times smaller, improving the cache hits ratio and overall performance (this happens even without compression). <\/p>\n

Myth 2<\/strong>. Index rebuild shorten SSD lifespan. False. One extra write cycle is nothing for the modern SSDs. If your tempdb is on SSD\/NVMe, it is under much<\/strong> harder stress than data disks.<\/p>\n

Myth 3.<\/strong> On Enterprise Edition there is a good option: ONLINE=ON, so I just create a script with all tables and go ahead. False. There are tons of potential problems created by INDEX REBUILD even with ONLINE and RESUMABLE ON — so never run index rebuilds without controlling the process.<\/p>\n

Finally, we will tackle the REBUILD vs REORGANIZE subject and what is possible to achieve if you have only Standard Edition.<\/p>\n

Enterprise Edition: Index Rebuild with ONLINE=ON and RESUMABLE=ON<\/h2>\n

RESUMABLE index rebuild — theory<\/h2>\n

This is from where I started: up to 100Tb per server, AlwaysOn with 2-3 readable replicas, local SSDs \/ NVMe with up to 200K iops, Enterprise Edition.<\/p>\n

Reminder about RESUMABLE=ON. With RESUMABLE=ON you can always pause an operation using (killing the connection that is performing the rebuild, also puts rebuild in a paused state):<\/p>\n

ALTER INDEX ...  PAUSE<\/code><\/pre>\n
You can list indexes in resumable state using the following query:<\/p>\n
SELECT total_execution_time, percent_complete, name,state_desc,   last_pause_time,page_count   FROM sys.index_resumable_operations;<\/code><\/pre>\n
When rebuild is suspended, such operation even can fail over<\/em> to another server! Awesome!<\/p>\n
Also, column percent_complete shows the overall process<\/em>. (for indexes with WHERE clause, however, this value is not correct)<\/p>\n
Using ALTER INDEX … RESUME<\/strong> we can resume the operation (in fact, RESUME<\/strong> is just a syntax sugar — you can resume operation by simply executing the REBUILD<\/strong> command again instead of resume, just use the same options — for example, you can’t change MAXDOP on the fly). Using ALTER INDEX … ABORT<\/strong> you can kill the index rebuild.<\/p>\n
In the RESUMABLE<\/strong> mode transactions are short, so you should not worry that LDF growth will run out of control.<\/p>\n
First try, failed miserably<\/h2>\n
So, let’s generate a script with ALTER INDEX<\/strong> for all indexes in our database and run it. It would be for a few days, so let’s start it on Friday so we can check the progress on Monday.<\/p>\n
Time to close the laptop and go home. But why are support people running between workplaces? I hear they are discussing some «slowdowns»… Wait, what are these alerts about AlwaysOn Queue Length? is it somehow related to my script? Oops, this is more serious, people are yelling about locks, apps are unresponsive, timeouts. Wow, my Rebuild lis blocking other connections! Alert dashboard is all red and yellow! <\/p>\n
My boss is angry and telling me to stop this damn rebuild and never — your hear — never! Try it again! I want to save you from this, so let’s analyze all potential problems in advance. How can rebuild affect the production system?<\/p>\n