How to speed up index reorganize?

Question

Hi,
Added an index maintenance job (Hallengren) to a database (SQL Server 2016) with a few large tables.
Largest table has around 500.000.000 rows with a size of around 250 GB (3 indexes totally 125 GB so total data also around 125 GB).
The script will do a reorg if fragmentation goes over 5 %. This happens approx. every 2-3 day if running maintenance once per day (night).
So far, all good. My problem is that the reorg is running for a very long time. For example, the heaviest index (clustered index and also pk) is running reorg for around 4 hours 30 min even do the index was only fragmented to 5,33%.

My server is a quite thin one. Virtual with 16 GB RAM and 2 CPU cores (quite new Xeon).
Read somewhere that reorg is single-threaded.

Can I assume the reorganize time to decrease if I boost the server with more cores?
Any other suggestion?

Thanks!

Answer 1

The simple solution may be to run reorg less often. Or not at all. How have you determined that your database is best served by running reorg at fragmentation level of 5%?

Then again, I must say that the server seems very meek for a table of that size. I would not only had more cores, but eightfolding the amount of RAM certainly sounds attractive to me.

Answer 2

You have no control over the speed of the reorg. The reorg is mostly dependent on disk IO, not RAM or CPU. You should look at your performance counters to see if you are maxing out your CPU and RAM.

Answer 3

Hi Stefan-1807,

In addition, I think Index reorganize moves and compacts existing pages depending on the page distribution state, the I/O subsystem, the degree of fragmentation, and the fill factor you set, etc.
You can check I/O performance counters Disk Bytes/Sec, Disk Read Bytes/Sec, or Disk Write Bytes/Sec to see if there are any IO issue when running the maintenance plan. Please refer to this article which might be helpful.
And reorganizing compacts index pages to make page density equal to the fill factor of the index, and Compaction is based on the fill factor. You can try to change the fill factor and see if the reorganization is a bit quicker.

Best Regards,
Amelia

Answer 4

I can't get acceleration when using some apps, maybe my device configuration is out of date!

Answer 5

That 5% is a recommendation is one that Paul Randall once made out of more or less thin air. And in a time when spinning disks was all there was.

There are two types of fragmentation: One is that the page linkage in the clustered index does not follow physical pages in the database file. That mattered quite a bit in the days of spinning disks. Not so much with SSDs. The other type of fragmentation has to do with the fullness of pages. You don't want pages that are 50% full or less. This would mean that you spend precious buffer cache on vacuum.

Run reorganize when time permits - or you find that performance of the actual operations of the systems have degraded.