Index maintenance script rebuild/recreate/defrag

(0) Share

Report

Posted on by Stijn B

Dear all,

I have a question concerning index maintenance.
There are a lot of source & opinions concerning the "statistics" option.
I am inclined to follow M De Voe's opinion on this.
However, I'm searching for a good/up to date index maintenance script.

I found this one from R. Stefanetti, https://community.dynamics.com/nav/b/arounddynamicsnavworld/archive/2015/09/19/sql-server-reorganize-or-rebuild-indexes-script-v2-0 But I didn't quite get it to work.

I also found this one, https://gallery.technet.microsoft.com/Script-to-Rebuild-and-60d0ab15 But I'm doubting if it's suited for Dynamics NAV databases.

So which one are you guys using and on a daily or weekly basis?

Thank you for your input.

Kind regards,

Stijn B

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Alexander Ermakov 28,096 on at

Like (1)

Report

What SQL version do you have?

What might probably causing your performance problem is not fragmentation, as there are very few queries are using "ordered scanning" which might be affected by that. Updating the statistics might have better effect. By default, SQL will update the statistics when 20% of the data has been updated. In order to bypass that standard setting, there is a traceflag 2371, intoduced starting from SQL 2008 R2 SP1. This would incorporate SQL to verify statistics more often.

Was this reply helpful? Yes No
Stijn B 66 on at

Like (0)

Report

It's SQL2014.

I'm having no performance problems (yet), I'm just wondering what are the optimal settings & maintenance plans.

As you stated, I still need to create a maintenance plan that will update the statistics on a daily or weekly basis.

The auto setting (for update & create statistics) is already switched on. However I read it is still advised to schedule a maintenance plan to handle this on top of the auto settings. Since the maintenance plan does "more"....

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,096 on at

Like (0)

Report

Actually, I was not talking about a maintenance plan, but rather to fire a certain flag in SQL settings, so the update of statistics would be performed automatically earlier than it is done by default settings. So, the update of statistics would be done as soon as certain amount of data is modified/added, thus it would happen not on a scheduled basis, but automatically just when needed.

However, it does not exclude the general maintenance plan. I would recommend to make a full database index rebuild on a weekly basis.

Was this reply helpful? Yes No
Suggested answer

Amol Salvi 18,698 on at

Like (0)

Report

Hi Stijn

May be Following link will help you

technet.microsoft.com/.../2008.08.database

technet.microsoft.com/.../ms140255(v=sql.105).aspx

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

I'm a DBA, and I use Ola Hallengren's scripts. I read M De Voe's post and would tend to agree, to an extent, if you are not seeing performance issues. The key is picking the thresholds. The tricky problem with statistics is that MS recommends using trace flag 2371 for AX servers, which changes the auto-update statistics thresholds - plus, one cannot really trust that ALL statistics will always automatically be updated. There are different kinds of statistics and the auto-update only catches some of them. I update column and index statistics nightly, but only changed statistics. I'm struggling right now with regular index maintenance tasks for my AX databases, and reading everything I can find on the best way to manage it as they do seem to be a bit different. :) I can tell you to make sure you have 2 settings checked - auto-update and auto-update asynchronously or you probably will see some performance degradation.

https://www.sqlskills.com/blogs/erin/sqlskills-sql101-updating-sql-server-statistics-part-i-automatic-updates/

Was this reply helpful? Yes No