Skip to content

April 2021

SQL Server Parallel Index Defragmentation

I work for a company that has a pretty large SQL database. It’s about 4 terabytes in size. We had been using Ola Hallengren’s index maintenance script for a while but it got to be such that the script would take over 24 hours to run. That became a problem. That’s not to say there was really anything wrong with Ola’s script. It really is an excellent solution. However, it does only do index defragmentation serially … meaning one at a time. The same would be true for SQL Server Maintenance Plans. It works fine on smaller databases but when you have a HUGE database that no longer works well.

To solve this I created multiple jobs using his script and tried to break up the indexes between the jobs to have somewhat of a balance between them. That became a pretty big hassle to maintain. I would have to every-once-in-a-while go in and tweak it and some days it would work fine and other days it would not. So, I set out to see if anyone had created a solution to defragment indexes in parallel … meaning more than one at a time. I could not find one on the web so I built my own.

My solution will create a SQL Agent controller job and multiple worker jobs. The controller job will create a queue of indexes to be defragmented based on runtime parameters passed to it. Once the queue is created it will spawn the worker jobs which will proceed to take indexes off the queue until the queue is depleted or the termination window has expired.

The below table shows the performance gains we were able to get with our index defragmentation. Just using one job with Ola’s script took over 24 hours. When we broke it out into four jobs it still took 6-8 hours. With my script and six worker jobs the time went down to 3 hours for most all the indexes. There are two REALLY big indexes that take about 7 and 4 hours individually because the data in the table changes a lot daily. Finally, I realized that most of the indexes were set to a fill factor of 100%. Which means that I was constantly having high fragmentation on tables. So, I rebuilt all the indexes at 85% fill factor and the defragmentation time went down to about an hour per day with those two other indexes still being the outliers. I feel like that is a pretty good result.

ScriptJobs Duration
Ola Hallengren’s script1Over 24 hours
Ola Hallengren’s script46-8 hours
My script63 hours with 1 index at 7 hours and 1 at 4 hours
My script with fill factor at 85%6Monday: 1 hour (30 minutes of that is to build the queue) Other Days: 20 minutes (15 minutes of that is to build the queue)

The maintenance solution is release under the MIT License so feel free to use it as you wish just please let me know if you have found it to be helpful. You can click the button below to download the solution. The second link is to the source code repository.

Index Maintenance InstallerDownload

https://bitbucket.org/davinstuder/index-maintenance/

Release Notes

1.3.1 – Added a check to see if the index can be defragmented online or not. Added start and end logging to the controller stored procedure. Fixed an issue with error logging not saving the error details
1.3.0 – Worker jobs are no longer statically created. They are spawned at runtime dynamically by the controller job. Cleaned up some SQL to standardize on formatting.
1.2.0 – Added history cleanup
1.0.2 – Removed references to deprecated master.dbo.UpdateIndexesQueueHistory table
1.0.1 – Edited query to find highest compatibility level for server
1.0.0 – Initial release

Twas the Night Before Passover

When I was a student at Multnomah we had to do a final project for our Pentateuch class. I wrote this. I found it the other day as I was going through our filing cabinet and pulled it out to read to the kids for Passover. Enjoy!

Twas the night before Passover, when all throughout Israel’s foe
Not a creature was stirring, not even Pharaoh.
The Hebrews painted blood on their doorframes with care,
In hopes that the Spirit wouldn’t stop there.
The Egyptians were nestled all snug in their beds,
While visions of pyramids danced in their heads.
And Moses was sitting and talking with God,
About all the things he done with his rod.
Nine plagues he had brought on the land with a clatter,
But Pharaoh still didn’t know what was the matter.
Away he sent Moses each time with a flash,
Oblivious that again with God he would clash.
More rapid than eagles his curses they came,
And he whistled and shouted and called them by name.
Now, blood! Now, frogs! Now, gnats! And, flies!
Dead livestock! Boils! Hail! Some locusts! Dark Skies!
The tenth one reserved for the firstborn to fall,
God dashed away, dashed away, dashed away them all.
As dry leaves that before the wild hurricane fly,
All the nation of Egypt started to cry.
So, up from the houses the curses they flew,
At all of the Hebrews who’d known what to do.
In the twinkling of twilight they slaughtered a lamb,
Then cooked as commanded from the the Great I AM.
They ate with their cloaks tucked in at their waist,
And as God had commanded they did this with haste.
The bread that they ate couldn’t have any yeast,
If it did, it would certainly spoil the feast.
And bundles of gold and silver they pillaged,
Cause God made them favorable to all in the village.
Now Pharaoh was pondering “What should I do?”
“Cause if they don’t leave, I could die too.”
So he bid all the Hebrews and Moses to part,
But wouldn’t you know it God hardened his heart (again).
He sprang to his chariot, to his team gave a whistle,
Then his army flew like the down of a thistle.
They chased them until the Red Sea they arrived,
But Moses had parted the sea on both sides.
The Hebrews, they crossed the Red Sea without trouble,
So Pharaoh decided to try on the double.
He started to cross the Red Sea with his troops,
But then toward the middle looked up and said “Oops!”
The waters they started to tumble and fall,
Not one man survived, not one man at all.
Then Moses exclaimed, ere they walked toward the land,
“To God be the glory, we were saved by His hand.”