Recently there’s been a few questions asked online about slow clustered index rebuilds within SQL Server for rowstore indexes. So, I thought I would cover some of the reasons that I have experienced here.
In truth, some of these I discussed in a series I posted last year about maintaining larger SQL Databases better. You can read the recap I posted about it in detail and find the links to the older posts here.
Because it’s 2019, the chances of you having a 64bit version of SQL Server installed on a 32bit version of Windows are slim. So, if it’s OK with everyone I will leave that off the list?
To make things easier I have provided quick links to each one in the below list.
- Creating big clustered index
- Busy SQL server
- Online clustered index rebuild
- Using compression on an index
- Low spec SQL Server
1. Creating big clustered index
If you’re not that familiar with the database, it’s always worth checking the size of the table in question first. Otherwise, you may find that you’ve started a clustered index rebuild on a really large table.
Which can cause problems, especially during the working day.
You can do this either by running a query or running sp_spaceused on the table. Of course, you can also view the ‘Disk Usage by Table’ report in Management Studio instead to save having to type.
Of course, sometimes the server is just too busy. Especially if you are attempting to do this during the working day.
If you are going to try a rebuild during the day, I suggest picking a period when you know the server is reasonably quiet. In addition, running your favourite DMV which shows what is currently running would also help.
One option is to try and rebuild it out of hours. However, if you do this try and select a time period when no maintenance tasks are running.
Otherwise, you could end up with the clustered index taking a long time to run. You could also end up having your maintenance jobs over running as well.
I better point out that online rebuilds in general tend to take longer. Mostly because behind the scene’s it’s making a rebuilt copy of your index and then it swaps around to the new index once it has completed.
However, there is another key point I should mention here.
If you have a query start that uses your existing index before the rebuild ends you must wait for the query to complete before it is swapped over for the new index.
Of course, instead of letting the query finish you can always cancel the query if it’s deemed not as important.
4. Using compression on an index
If you do use row or page compression on a clustered index it does mean that it will take a bit longer to rebuild. Because it has to do extra work behind the scenes.
Back in the UK we have a saying, which is “eyes too big for your belly”. To clarify, it’s a food reference. It means that you try and take on more than you can handle.
I have seen clustered index rebuilds take some time because large rebuilds are attempted on servers which probably could do with an upgrade.
To set realistic expectations here, if your server has a couple of cores and 4GB of RAM then it’s probably going to take a while to rebuild a 750GB clustered index.
If you struggle with this particular issue and have a problem justifying an upgrade I have some advice. Try and demonstrate the index rebuilding faster on a more powerful machine you have available.
If you don’t have one then deploy a virtual machine of the same specification in the cloud and rebuild the index there. Afterwards, deploy a virtual machine with the specification you would like. Which you can then use to show the index rebuilding there instead.
In case you’re not sure which cloud provider to use you can try using Azure here.
Final word about slow clustered index rebuilds
Anyway, that’s just some of the reasons why you encounter slow clustered index rebuilds within SQL Server.
Of course, there are other reasons. However, these are the ones I generally encounter.
If there’s any others that you find are common then feel free to add a comment below.