Defrag NAV database with 13 company's

borgy

Hi Guys,

In Nav. you've got Database -> Information -> Tables -> Optimize.
This is a very good tool. But i wonder if somebody's got a script which does the same. So i can schedule it.

I run into more sql defrag scripts only they have problems with the 13 company's or schemas that we use in Navision.

Thanks very much.

Specs.
Nav 6 R2
Sql 2008 R2

ndbcs

There is a tool from ExpandIT called Table Optimizer.
But I really don't like those tools that remote control your NAV client.

I suggest using T-SQL scripts for that task.
The one from Ola Hallengreen is excellent.

Tobias

borgy

Thanks, i thaught about SQL too instead of tooling. I wonder about the results of the reorganize job in SSIS, maybe is that enough?
Once a week i use the rebuild job from SSIS, that works fine.

kriki

I am using this one : http://sqlfool.com/?s=dba_indexDefrag_sp.
It has the advantage that you can define a window in which the script can defrag. Finished the window, it waits until another window comes up and than continues the defrag where it left of.
Another tool, I think the best for performance because it calculates a fillfactor for each index, is the tool from http://www.sqlperform.com/ but it is not free.

ndbcs

I don't now what exactly the SSIS job does.
But I think it reorganize everything, regardless of the fragmentation level.
And I think it's much to complicated to use SSIS for a db maintenance task.

If you use one of the mentioned scripts you can run it every night, because the scripts only rebuild/reorganize fragmented indexes.
So they are running very quickly. (depending on your maintenance windows etc.)

The Ola Hallengreen scripts also have a parameter for defining max. run time per job.
And they can take care of updating stats for indexes that would not be rebuild.

Calculating fill factors is nice but I think that you don't really need it.

Tobias

borgy

Hi Tobias,

I think your right in that. The rebuild script rebuilds everyting, so i think the "reorganize" ssis job will do that too.
Thats good for during the weekend, but during days in evening, you don't want that.

Thanks for the tip.

Kind regards,

André

DenSter

Funny how everyone has a different way to do it, and everyone claims their way is "the way"

borgy

Well Daniel, i think differently. I'm will look at this script. Test the script en when it works good maybe i will use it.
And i'm always open for new ideas. To be honest i like the SSIS jobs. Maybe i will have a better look at the Reorganize to.
But i need something that works fast en only solves the problem, not doing everything.

Kind regards,

André

DenSter

There's something good in everyone's suggestion. You can also start the maintenance plan wizard and select index rebuild. Set a fill factor of about 85% (15% free space). Running that on a weekly schedule is a good start, all you have to do is measure how long it takes and find a window to schedule it in.

Another nice part about the SQL Perform tools is that you can select which tables and/or companies you want to reindex, and there is definitely a benefit of having an index fill factor that is right for each individual index.

ndbcs

I never ever (ok, never say never) would recommend using a method that reindex your whole db, because of the pressure that it puts on your transaction log.
Also if you have the time for doing a "full reindex", no one wants those big trn files (let's hope your using full recovery model for your db's).
They cost you much space on your disks and on your tapes.

If you use log-shipping/mirroring etc. it get's even worse.

There are those reallly good free scripts to do it much smarter than what you can do with maintenance plans, so why not using them?

Regarding the fill factor calculation I think it's good to identify those big indexes that never see page splits and give them a fill factor of 100% (e.g. most custered indexes on entry tables).
For the rest it's save to use 90% (or 85%) and smart-reindex every night.
I've seen tools that really calculating bad fill factors and as long as I don't now how they algorithm works, I'll not use them.

But as always, it depends ...

Tobias

DenSter

I did say "it's a good start" to use a standard maintenance plan. It gives you the very basic index maintenance with little effort. For more complex situations we always use a toolset.

Savatage

borgy wrote:

In Nav. you've got Database -> Information -> Tables -> Optimize.
This is a very good tool. But i wonder if somebody's got a script which does the same. So i can schedule it.

Optimizing Tables in Job Scheduler by ara3n
viewtopic.php?f=5&t=10331

kriki

In short:
*Database -> Information -> Tables -> Optimize
-POS:very easy to do, more difficult to implement. In pre 5.0SP1 it could be useful to clean the 0-SIFT records.
-POS:free
-NEG:does all tables/indexes even if not needed
-NEG:it uses the fillfactor that was used when the table/index was created and you cannot change it
-SQL Server maintenance plan with rebuild index
-POS:very easy to do.
-POS:free
-POS:and from 5.0SP1 it is a better solution than "Database -> Information -> Tables -> Optimize".
-POS:you can give another fillfactor
-NEG:does all tables/indexes even if not needed
-NEG:the fillfactor is for all tables/indexes
-SQL Server maintenance plan with index defrag
-POS:very easy to do.
-POS:free
-POS:and from 5.0SP1 it is a better solution than "Database -> Information -> Tables -> Optimize".
-POS:does only tables/indexes that need to be done
-NEG:no new fillfactor
-NEG:no optimal reconstruction of tables/indexes
-NEG:you need to update the statistics
-SQL Perform tools
-NEG:a little more difficult to implement
-NEG:not free
-POS:support in case of problems
-POS:the best fillfactor for each table/index
-POS:does only the tables/indexes that need to be done
-POS:possibilty to split the tables/indexes in different groups to be done in different moments
-NEG:possibilty to split the tables/indexes in different groups to be done in different moments=>you need to do this manually
-TSQL-solutions
-NEG:a little more difficult to very difficult to implement depending on what you need/use (if you write it yourself it is very difficult; if you take an existing script, somewhat difficult and it also depends which free tool you use)
-POS:free
-POS:you can make how you want it
-POS:you can give another fillfactor
-NEG:the fillfactor is for all tables/indexes
-POS:you can define windows when to run it
-POS:does only the tables/indexes that need to be done
-NEG:you need to update the statistics in case of defrags

stryk

As there is so much advertisement for a competitor's product I dare to add this one:

NAV/SQL Performance Toolbox
-POS:installed within seconds
-POS:free (no license fee); it's part of the workshop/training; or provided by about 30 distribution partners worldwide
-POS:support in case of problems
-POS:the best fillfactor for each table/index
-POS:does only the tables/indexes that need to be done
-POS:possibilty to split the re-indexing workload of tables/indexes to be done in different moments; defining priorities; distribute workload over multiple days and/or batches
-NEG:load balancing needs to be set up manually (means per script)

borgy

Hey guys,

Thanks for all your reply's. Once a week i do a rebuild. Good to know you have to pay attention on the transition log. We don't use it at the moment.
I got a good script for normal sql database, but on NAV it's still gives errors.

So i will have a look at your new suggestions here and test them.

Kind regards,

André