Do I really need Re-Build Index Task in SQL Server all the Time?

Question

I have created Re-build Index task which will run every Sunday night @11:00 PM.

I do not have Re-Organize Maintenance Task created

1. Do I really need Re-build Index Task or I need it only when the Fragmentation Level is above 30% ?
2. Do I need Re-Organize Index Task when I have Re-build Index Task ?

My Major concern is, we have the Databases in Full Backup Recovery Model and the T-Log Auto-Growth setting is in '%' .

This is eating up all my T-log drive space and creating mess for SQL Server Functionality .

Please advice ?

Answer 1

Do I really need Re-build Index Task

The answer to that is very much "it depends". If the data in your tables doesn't change much over time the definitely no, at least not often.

or I need it only when the Fragmentation Level is above 30% ?

How much fragmentation you allow before a rebuild is an art rather than a science and may depend on your data patterns and performance metrics+targets, but there is definitely no need to rebuild a large index if there is only a very small amount of fragmentation so definitely check each index before rebuilding to see if it is worth the IO load.

Do I need Re-Organize Index Task when I have Re-build Index Task ?

There is no point reorganising around the same time (either before or after) rebuilding. Reorganising is a less intensive operation then a full rebuild while having some but not all of the benefits. If you rebuild after reorganising then the reorg was wasted as the whole structure is thrown away, if you reorganise after then there is nothing for the process to really do so anything it tries to do is wasted effort.

My Major concern is, we have the Databases in Full Backup Recovery Model and the T-Log Auto-Growth setting is in '%' .

I would generally recommend against % increments as they can produce large unneeded growth at times. Set the growth to a fixed size, reasonably large so it doesn't need to happen too often but not overly large. The values of "reasonably" and "overly" will change depending on your database.

This is eating up all my T-log drive space

If altering your plans to rebuilt/reorg/nothing less aggressively (i.e. not rebuilding everything every time irrespective of current fragmentation) then perhaps you could spread the load over the week by working on a subset of your tables each day. This will make little difference if you DB has one huge structure and the rest of the data is in very small tables (an analytics setup with one huge results table and a series of small dimension tables perhaps) but could be beneficial otherwise. Try altering the current plan to be less aggressive first though, this will be simpler to implement and maintain, and there are many example scripts out there that you can take and tweak for your database's needs.

Answer 2

I'm going to flat-out say no, you don't need to rebuild or reorganize indexes. You can just update statistics on most systems and never noticed a difference. It can make a difference in very specific workloads.

Generally though, with non-frisbee disks, and a decent chunk of memory, it's not getting you to performance Valhalla.

Index maintenance is often a tremendously expensive and time-consuming way to improve one metric: index fragmentation percent. I'm saying this as someone who used to waste a lot of time trying to improve that metric.

It never did quite fix anything, and I never was sure if all the time and resources spent on that maintenance saved my queries a commensurate chunk of time and resources on the other end.

As soon as I stopped, I had more time and resources for more important things, like DBCC CHECKDB.

So when should you rebuild an index? Almost never, unless you need to change something about that index, like fill factor, compression, partition alignment, etc. The one upside of rebuilding indexes is that they update statistics, but you can just do that separately.

When should you reorganize an index? Maybe occasionally, if you really want to compact LOB data. Doing this doesn't even update stats, which is a bummer.

The reason a lot of people think that rebuilding indexes fixes performance issues is because the stats update they do invalidates a bad query plan. But again, you can do that without beating the snot out of your drives and running out of space with just a stats update.

Answer 3

Index maintenance is very vital when it comes to query performance. So it is very important to have it properly planned and implemented. Along with Index maintenance comes the ballooned log file which is the result of Index rebuild and re-organize. Now planning for Index maintenance is important as it might just trigger another issue of 'Transaction full' unless proper precautions are taken.

It depends on you how you want to do the index maintenance, however the solution from Ola hellengren is the best and freely available. Link below :

https://ola.hallengren.com/

With above solution you get multiple options and all can found in the website.

Coming down to your queries:

1.Do I really need Re-build Index Task or I need it only when the Fragmentation Level is above 30% ?

Yes, however this depends on the page count. If the page count is too low then the index fragmentation will always be on a higher side and it wont effect the performance. In our environment we consider Page count > 500 during the index fragmentation analysis. Re-organize when Index fragmentation >5% and <30% Rebuild when Index Fragmentation > 30% The above numbers (%) is not a standard but yes its widely preferred.

2.Do I need Re-Organize Index Task when I have Re-build Index Task ?

It depends on what your needs are. Re-organize is a light weighed operation ,generated lesser logs and always an Online operation. While rebuild is online only for the Enterprise edition of SQL Server and is more heavy operation.

This is eating up all my T-log drive space and creating mess for SQL Server Functionality .

Please advice ?

Do not use Index rebuild ALL but individually.Index maintenance has to be followed-up by regular T-log backups. This helps in controlling the log growth. Have a rough estimate of log growth and the log drive ready with sufficient space. Follow the best practices for transaction log management and Index maintenance will never be a problem.