Constant rebuilding indexes to fix performance - why?

Question

We have a database server (2016 SQL Server), that we have added a step of 'rebuilding indexes' to the deployment process.

In decades of working with MS SQL Server at a many companies, I've never ONCE had to rebuild the indexes in order to fix a performance problem.

Yet, we do it at least every 2 weeks, and often times more often than that. And I'm told "yep! that fixed the problem!" It seems to me, it is much more likely to have fixed a symptom. I know, I've had an issue with a database where a query would run > 10 minutes the first time, and in a few milliseconds after it had completed once. (In this case, it had built a temp index on Right(field,8) that someone was using in the query, and adding an index on that fixed it)

I'm thinking maybe a rebuild is causing everything to be loaded into memory, so the server has the entire index right there and ready to use?

Has anyone else seen this, and is this an indication of another issue that maybe we can fix?
(More RAM, better disks, or something?)

Answer 1

Has anyone else seen this, and is this an indication of another issue that maybe we can fix?

What they are possibly trying to "fix" with index rebuilds, may be either statistics problem, or parameter sniffing problem.

Try updating statistics with fullscan instead of index rebuild, and see if that fixes the performance problem (when it happens) to confirm. Updating stats is less heavy operation than index rebuild

Second possible thing is Parameter sniffing - sometimes can be at fault for when the query ran fine before but then suddenly became slow. Read this and especially watch that video with Brent Ozar's presentation

https://www.brentozar.com/sql/parameter-sniffing/

In the video Brent explains what parameter sniffing is, and how people "fix" it when emergency happens (starts at 16:55). Rebuilding indexes is one of them. Note that upper option is worst, bottom one "clear particular plan from plan cache" is best one

Also in the video Brent explains what options are out there to fix it "long term"

I'm thinking maybe a rebuild is causing everything to be loaded into memory, so the server has the entire index right there and ready to use?

No, certainly not. Rebuilding index doesn't cause everything to be loaded into memory.

Answer 2

I am all with you with "this fixes rather symptom than the root cause". But this "fix" through index rebuild has plethora of drawbacks. What drawbacks? I am glad you ask:

1. index rebuild is fully logged operation - your T-LOG and its backups (provided recovery model = full) grow
2. if you have query plans in your execution plan cache on your SQL server and these plans touch the rebuilt indexes, they're marked for recompilation - hence new plan gets created
3. while rebuild runs, you put additional I/O & CPU load on your server
4. in case you aren't on SQL enterprise edition, you cannot rebuild WITH ONLINE, hence you can cause additional blocking

ad. 2) - this is where it might seem you "solved" the problem with index rebuild, but index rebuild could rather be consequence for better, more tailored execution plan for the problematic query/queries.

In case you run into slow server next time and you wanna confirmation that index fragmentation is not your root cause problem, you can choose plan B instead of index rebuild next time.

Plan B If you have this emergency and wanna launch index rebuild, please, try DBCC FREEPROCCACHE instead, but use it scarcely (preferably just once to prove case). DBCC FREEPROCCACHE evicts every plan from your plan cache and you can have chance to get plans better tailored for the workload you currently have. If your emergency stops after giving SQL freeing the plan cache, then you proved index rebuild is not helping per-se, but rather causing similar effect as DBCC FREEPROCCACHE, but with DBCC FREEPROCCACHE, unlike index rebuild, you are not experiencing drawbacks 1-4 described above.

I, however, strongly advise against running DBCC FREEPROCCACHE often to "help" server. That is bad practice.

How to solve the problem in more sustainable fashion is rather out of scope of this post, but let me know should you be interested in more links covering this topic, please (or google search will do).

EDIT:In case you confirm, after all, that DBCC FREEPROCCACHE has the same effect as index rebuild (things suddenly start to go faster), then I dare to say you suffer from parameter sniffing. Erland Sommarskog has great article about that: Erland's page

Another supporting argument why not to launch index rebuild is to check how fragmented the indexes are in the first place. Great article with T-SQL to check the fragmentation: sqlshack

Also, for the sake of completeness, DBCC FREEPROCCACHE is not the only option how to evict execution plan(s) from cache and you can evict only 1 plan from cache if you have SQL plan handle. You also wipe part or whole plan cache by:

• failing over to another node if you are using clustering or always on availability groups
• running UPDATE STATISTICS (queries using the updated stats are also marked for recompilation after you update them)
• executing sp_recompile Microsoft docs ... to scrutinize execution plan cache, find the troublemaking queries and lots of details about them and get e.g. said plan handle, I would kindly direct you to first responder kit github repo link, namely its sp_blitzcache stored procedure.

Answer 3

It very depends on how database is used. If it is a static database (like archive) and you only read from it, then index rebuild is not important because data are not changed. But if database has a lot of insert/update/delete then rebuild might play an important role together with updating statistics.

So there is no one answare.

Here is interesting video from Brent Ozar about Index maintenance: https://www.youtube.com/watch?v=iEa6_QnCFMU