SQL Rebuild Index vs NAV Client Optimize!!

FCP · 2007-10-24

Someone told me that doing table Optimization with Navision Client is better than using SQL 2005 Rebuild Index task.

Is this true? :-k I hope not, because the client optimization cannot be Scheduled to night hours

How often should the rebuild index used?

Thanks

Savatage · 2007-10-24

FCP wrote:

Someone told me that doing table Optimization with Navision Client is better than using SQL 2005 Rebuild Index task.
Is this true? :-k I hope not, because the client optimization cannot be Scheduled to night hours
How often should the rebuild index used?
Thanks

Not sure the answers to some of th questions posted here, but I do remember starting a post way back on mbsonline.org about Table optimization and If it was necessary? and if so, How Often?

I think the feelings were mixed that there is an actual gain in performance or not. I forgot if it was the reading got faster and the writing got slower or vice versa.

I guess other can chime in on that. I mainly do it for space once in a while. Any here's a post from the Tip's N' Tricks forum about Optimizing from Ara3n, so it possible!:
Optimizing Tables in Job Scheduler
http://www.mibuso.com/forum/viewtopic.php?t=10331

stryk · 2007-10-24

The "Table Optimizer" does two things:

1) Delete "Zero" SIFT Entries
2) Rebuild Indexes doing a CREATE INDEX ... WITH DROP_EXISTING

The Standard MP can not delete the SIFT stuff, except if you include e.g. my stored procedure for this: http://www.stryk.info/ssi_delzerosift.zip

The CREATE INDEX thing is actually the hard way to rebuild indexes; while it is running the table is actually offline.

The MP uses the ALTER INDEX command which is a "smoother" way of defragmenting indexes; and with EE you could do it in an ONLINE mode!
The result (restauration of Fillfactors, Defragmentation of Indexes) of both methods is pretty much alike.
Of course, if you really measure the fragementaion usind DBCC SHOWCONTIG you would find differences, maybe the CREATE slightly better; but for the experienced performance it does not matter.

So, I recommend to stick to the MP!

Best regards,
Jörg

FCP · 2007-10-25

Jörg

MP = ?Maintenace plan?

Just to be sure I got it right! You're saying to keep a standard Maintenace plan that rebuilds de index, since there's no major performance differences? :?:

Another thing bother's me. My SQL is with Option Full and the log does not stop to grow, this forces me to sometimes put it in SIMPLE and clean the log file. I bet there's better way, please advice me!!

Best regards,

Rui

cnicola · 2007-10-25

You can setup an MP

for backing up the log file and then empty it automatically.

ara3n · 2007-10-25

You need to backup the log and then shrink it.

nunomaia · 2007-10-25

FCP wrote:

Jörg

MP = ?Maintenace plan?

Just to be sure I got it right! You're saying to keep a standard Maintenace plan that rebuilds de index, since there's no major performance differences? :?:

Another thing bother's me. My SQL is with Option Full and the log does not stop to grow, this forces me to sometimes put it in SIMPLE and clean the log file. I bet there's better way, please advice me!!

Best regards,

Rui

Before shrinking your log files you should know you acreage log file size. You shouldn’t decrease your log files to a minimum size. Besides rebuilding indexes you periodically delete zero entries in SIFT tables with script supplied by stryk.

BGI · 2007-10-25

ara3n wrote:

You need to backup the log and then shrink it.

Shrinking log files is imho a very bad thing to do and should not be done on a normal working production database.

If you do a daily backup of the log files (or even more frequently), the log file does not become smaller in size, but the space inside the file will be reused.

Log file will automatically expand. This will take time and will take time when people are working.

Constantly shrinking and expanding the log file will cause a lot of defragmentation.....

In a normal working environment the size of the log file should stay aprox. the same, so why shrink it as it later in the day will expand again....

Rgds
Benny

BGI · 2007-10-25

FCP wrote:

Jörg

MP = ?Maintenace plan?

Just to be sure I got it right! You're saying to keep a standard Maintenace plan that rebuilds de index, since there's no major performance differences? :?:

Another thing bother's me. My SQL is with Option Full and the log does not stop to grow, this forces me to sometimes put it in SIMPLE and clean the log file. I bet there's better way, please advice me!!

Best regards,

Rui

MP: in the maintenance plan on sql2005 you have the option to keep the index online will rebuilding.

LOG file: besides a backup of data you MUST also do a backup of the log file, otherwise the log keeps growing. After a backup the size will not be smaller but the space inside the logfile will be reused!!

DenSter · 2007-10-25

ara3n wrote:

You need to backup the log and then shrink it.

No you don't want to do that. Shrinking the file will result in severe fragmentation of the file, because it gets expanded during the day. All you need to do is make sure you run proper backups, and the file size should just be left alone.

<edit> I should really read the whole thread before replying, someone had already made the point :oops: </edit>

DenSter · 2007-10-25

If your log file has expanded a LOT because you've never done a log backup, you can shrink it to a taarget size, using the DBCC SHRINKFILE command, which has a target size parameter. Go to Books Online for more information and a sample code snippet.

thaug · 2007-10-25

You can shrink it from within the Management Studio/Enterprise Manager as well. However, as mentioned, that should really not be done on a regular basis. The only time that I have done it is after a restore which requires a lot of LOG space (at least 110% of database size I would estimate).

FCP · 2007-10-27

So I should not shrink the log, I got that, but since I have the log growing really fast what solutions do I have? What I'm I doing wrong? Is it the full Option? Is there a way to keep the log size in control? Again please advise.

PS - I've searched the forum and steel I haven't seen an answer that really states what's the best practice, related to Index and Log size.
Waht to do with SQL maintenace for NAV.

nunomaia · 2007-10-27

If you have full backup option activated you should have make regular backups of log files to truncate log file. His avoids having huge log files.

DenSter · 2007-10-28

It depends on what recovery model you are using. If you have it set to 'Full' then the only way to truncate the log is by backing it up. With the 'Simple' recovery model, it truncates the log when the system runs a full backup, and you won't have to maintain the log file. You should get your partner to help you set this up and explain it further. We can all write big articles about this, but the easiest way is to have someone in your office showing it to you.

stryk · 2007-10-28

MP: in the maintenance plan on sql2005 you have the option to keep the index online will rebuilding.

This requires Enterprise Edition!

Regarding the TLog size: the optimal size of the TLog is so that it is never necessary to expand it (Log Growth = new Virtual Log = more administartion = slower performance) but should not waste too much of disk space, also.
To grant this it is necessary to truncate the TLog space by frequent backups, and this depends on your backup strategy and available disk space.
The big advantage of TLog backups is, that with them you could restore a system - in case of disaster recovery - until the very last committed transaction! So running the SIMPLE recovery model would be a waste of this big benefit! FULL is the only option for a productive NAV database.

When it comes to rebuilding the indexes, the TLog wil be used more than usual; e.g. if your DB size is 50GB, a normal TLog size could be 3GB. After re-indexing it could be grown to 55 GB!
So assuming the index maintenance is performed one per week - at the weekend - the process could look like this:

- Normal TLog size during week: 3GB
- Friday, 20:00, End Of Business; Full Backup
- Friday, 21:00: Set Recovery Model to SIMPLE
Run Re-Indexing
If necessary SHRINK TLog to 3GB
Set Recovery Model to FULL
- Saturday, 12:00: Full Backup

With SQL Server 2005 Maintenance Plan this all could be perfectly designed, mixing standard tasks with own TSQL tasks etc.!

kriki · 2007-11-21

[Topic moved from Navision forum to SQL General forum]

SQL Rebuild Index vs NAV Client Optimize!!

Comments

Categories