Optimise Navision

Simo_ba

Hi guys,
i'm not an expert in SQL, but i need to stop runnig Optimise Tables from Navision and relpace it with something else that will be run at the SQL server side as scheduled Job.
So i found Stryk SP ssi_delzerosift, but i read in the Mibuso site that deleting SIFT record may cause Flowfield calculation troubles.

So guys i'm really confused, what can i do to plan a maintenance plan for my Navision Database instead of runing Optimise Tables.???
i'll apreciate any suggestion.
Thank you.

kriki

If you use the latest update for NAV 4.0SP3, then you won't have problems with the flowfield calculations.
So you can use it.

Even if you don't deleting SIFT records, create a job to rebuild the indexes on SQL. This should be done at least once a week and each day you should do a SQLstatistics calculation. Or (if possible) do each day an indexrebuild.

stryk

The problem with inconsistent FlowFields after SIFT maintenance affects the NAV versions 4.0 Build No. >= 19365 to < 25143; this was fixed with NAV 4.0 SP3 Update 6 Build 25143.
Also affected: NAV 5.0 before SP1

Previous versions of NAV - 3.70 and older - don't have this bug.

Simo_ba

Thanks guys,
Unfortunatly, i ran a rebuiled index from sql it takes 18h to be done, is it something to be done to improve my data base performance, by the way i bought your sql-performance book stryk good job, keep up the good work.
so my issue is, my client want to replace Navision Table Optimisation, with a scheduled Job in Sql, any suggestion guys??
Thank you a lot.

bbrown

Simo_ba wrote:

Thanks guys,
Unfortunatly, i ran a rebuiled index from sql it takes 18h to be done, is it something to be done to improve my data base performance, by the way i bought your sql-performance book stryk good job, keep up the good work.
so my issue is, my client want to replace Navision Table Optimisation, with a scheduled Job in Sql, any suggestion guys??
Thank you a lot.

There are probably things that can be done. Exactly what depends on the details of the performance issues. We are running a nightly reindex/reorg plus stats update that runs in about 30 to 40 minutes on a 120 GB database.

DenSter

An index rebuild taking 18 hours feels to me like it has one of two factors. Ether you have severe hardware issues, or probably more likely there are users (or other processes) connecting to the database while the reindexing takes place. I've seen a reindexing of a table with one record take an hour because it was constantly being updated by a NAS timer.

Simo_ba

can you guys send me T-sql for relbuild index SQl2005. reorg also if it possible 'id like to comapre it with my script.
Can you guys help me with that?
1.wich option should i check when rebuilding index.
A-Reorganazie pages with default amount o free space or
B-Change free space per page percentage to <what will be the best %>??
Advanced Options:
C-Sort results in tempdb Yes or No??
D-Keep Index online Yes or No??.

2-Should i make a Database singleUser only when processing a rebuild index??

Reorg:
Compact large object Yes No?
Thank you

kriki

You can use the maintenance plan for creating the rebuild index.

1. "A" But in the SQL-server options, I would put the fillfactor to 90%

"C": Yes, but only if you have the temp-DB on its own dedictated drives. I don't remember if you need Enterprise edition or not
"D": Not needed, it you do the rebuild index during off-hours. BTW: you need the Enterprise edition if you want to do it online.

2. Not necessary if done during off-hours.

Reorg : Compact large Object : NO

bbrown

Fragmentation less than 10% = Ignore

Fragmentation between 10% and 30% = Reorganize

Fragmentation greater than 30% = Rebuild

Simo_ba

Thanks guys,
i run a rebuilt indexes and it takes eternity, is there any suggestion to check something to do with.
Thank you.

kriki

Simo_ba wrote:

Thanks guys,
i run a rebuilt indexes and it takes eternity, is there any suggestion to check something to do with.
Thank you.

What is "eternity"? How big is your DB? Did you do some SQL-tuning?
This last can help a lot because you throw away a lot of indexes.

Simo_ba

Hi kriki,
my DB is 100GB, i have 12 company, we did some sql tuning,when we upgrade to sql2005 and Nav4 sp3.
which suggested tuning may helpe on Reindex performance issues?.
When i run Nav Tables Optimisation it taked 7hours,
when i run Rebuilt index it taked 18 hours.
can you suugest to me some tuning to do?

kriki

It is strange that the indexrebuild takes more time then the optimize in Navision.
The SQL tuning should remove some unnecessary indexes/SIFT-levels, thus reducing the time to re-index them.

What is the diskconfiguration of your server?
For your DB I would recommend at least: (all disks RPM15000)
4 disks in RAID10 (even better 6 disks) for DB
2 disks in RAID1 for temp-db
2 disks in RAID1 for logfile (4 disks in RAID10 in case you have full recovery model)
and 2 disks in RAID1 for system (or if needed : put this together with the temp-DB).

PS : I hope you don't do a shrink of the DB-file or the TL-file. This should be avoided as it can create fragmentation and takes extra time to do a indexrebuild.
PS2 : also check if your disks aren't too fragmented.

DenSter

with 12 companies you have huge numbers of tables, which is probably severely magnified by huge numbers of keys and SIFT levels. I'll say it again: your hardware needs to be looked at, you need to make sure no users are connected when the reindexing takes place, and you could probably use a LOT more index tuning.

What you should do is purchase some tools for your maintenance, and get an expert involved to help you tune your database. I could continue here, but that would turn into advertising pretty quickly

Marije_Brummel

Yup. That is the biggest danger of this part of the forum.

18 hours on 100GB with 12 companies is definately not bad.

I've seen miracles happen with a reindex but also dissapointed customers who expected more.

Try to get a helicopter view of your issues and avoid to focus on one part only.

David_Singleton

kriki wrote:

...
4 disks in RAID10 (even better 6 disks) for DB
...

Interesting.

I woudl be recommending at least 8 and maybe 10 drives for this many companies with 100Gig. Maybe i am just overkilling it, but with 15k SAS drives around $200 each, its just too cheap to bother using less imho.

kriki

David Singleton wrote:

kriki wrote:

...
4 disks in RAID10 (even better 6 disks) for DB
...

Interesting.

I woudl be recommending at least 8 and maybe 10 drives for this many companies with 100Gig. Maybe i am just overkilling it, but with 15k SAS drives around $200 each, its just too cheap to bother using less imho.

They cost only $200? :shock:
I should update myself on hardware costs....
In this case, I would go for as much as technically possible! Will be worth the investment!

NavStudent

Can you explain the 6 disk configuration for raid 10?
Lets me see if I get this.

Lets say I have Disk 1, 2,3. I mirror them with disks a,b,c
1+a,2+b,3+c.

How do you stripe then?

For 8 disk it make sense.

You have disk 1, 2,3,4. You mirror them with disks a,b,c,d
1+a,2+b,3+c,4+d. Then you stripe (1a+2b) and (3c+4d).
Will this be a virtual drive? Wouldn't performance be bad if all the data is written to (1a+2b)?

Thanks for clarification.

bbrown

RAID 10 is a stripe of mirrored drives. RAID 0+1 is a mirror of two strip sets. Which one usually depends on your RAID controller's features.

NavStudent

bbrown wrote:

RAID 10 is a stripe of mirrored drives. RAID 0+1 is a mirror of two strip sets. Which one usually depends on your RAID controller's features.

This doesnt realy answer my question.
As you can see above I am mirroring the drives and then don't know how you would stripe them.

I'm talking about RAID 10 only. You mirror the drives then stripe them.

bbrown

NavStudent wrote:

bbrown wrote:

RAID 10 is a stripe of mirrored drives. RAID 0+1 is a mirror of two strip sets. Which one usually depends on your RAID controller's features.

This doesnt realy answer my question.
As you can see above I am mirroring the drives and then don't know how you would stripe them.

I'm talking about RAID 10 only. You mirror the drives then stripe them.

It's all handled by the controller. Either your controller supports RAID 10 or it doesn't. It would be one of the setup choices

ara3n

For the 6 disk configuration.
(1+a),(2+b),(3+c) lets call them 1',2',3'.

The data is striped across 3 drives.

When you write to these drives lets say. SALSA

S gets written to 1',
A gets written to 2',
L gets written to 3',
S gets written to 1',
A gets written to 2'

Simo_ba

Thanks guys,
Is there any other suggestions , or i have to focus on my hardware only?

DenSter

Maybe you should start your own thread instead of taking this one over.

And no it's hardly ever just hardware, most of the time it's a combination of things.

David_Singleton

kriki wrote:

They cost only $200? :shock:
I should update myself on hardware costs....
In this case, I would go for as much as technically possible! Will be worth the investment!

These days the costs are (in this order)

1/ the time to do the work
2/ the box to put the drives in
3/ The controller

the cost of the drives should not even come into the equation.

So its the same work to ad 4 or 10 drives, and the controller should handle it, so just fill the box up.

Slawek_Guzek

kriki wrote:

2 disks in RAID1 for logfile (4 disks in RAID10 in case you have full recovery model)

:shock: :-k
What's the difference in recovery model ?? Regardless of recovery model every single transaction _has_to_be_written to log file to be considered commited.

Regards,
Slawek

kriki

Slawek Guzek wrote:

kriki wrote:

2 disks in RAID1 for logfile (4 disks in RAID10 in case you have full recovery model)

:shock: :-k
What's the difference in recovery model ?? Regardless of recovery model every single transaction _has_to_be_written to log file to be considered commited.

Regards,
Slawek

Yes, but if you have full recovery, then this means you need to do some TL-backuping. In this case it might be handy to increase speed of doing it to avoid it gets in the way of normal activity.
I have had a customer that made a TL-backup every 10 minutes. It took about 6 minutes to do it and during the TL-backup the system was very slow. (I am not sure anymore if it was a SQL-TL backup or some external tool). So a RAID10 in this case would have helped somewhat.
But if I have to choose between
a) 6 disks in RAID10 for DB + 2 disks in RAID1 for TL
b) 4 disks in RAID10 for DB + 4 disks in RAID1 for TL

I definitly go for a) even with full recovery.

David_Singleton

kriki wrote:

I have had a customer that made a TL-backup every 10 minutes. It took about 6 minutes to do it and during the TL-backup the system was very slow.

Maybe they were making the backup direct to tape? I have seen that kill the system. 6 min to back up 10 minutes of data sounds excessive to me. :-k

kriki

David Singleton wrote:

kriki wrote:

I have had a customer that made a TL-backup every 10 minutes. It took about 6 minutes to do it and during the TL-backup the system was very slow.

Maybe they were making the backup direct to tape? I have seen that kill the system. 6 min to back up 10 minutes of data sounds excessive to me. :-k

Definitely. They were backing it up on another drive on the network. And they had some (big) problems with their network.

garak

Don't make the backup directly to the tape (to slow) or to an network drive (could bring bad performance for the network).

Thats our way: every 10 min TL Backup to an local HDD and copy than to Tape. Also 1x @day one full backup (at night).

kriki

garak wrote:

Don't make the backup directly to the tape (to slow) or to an network drive (could bring bad performance for the network).

Thats our way: every 10 min TL Backup to an local HDD and copy than to Tape. Also 1x @day one full backup (at night).

In general I try to reserve a dedicated 1GB network to be used only for backups and no other traffic. This way I am sure the backup is directly on another server. Just in case someone decides to do a smoketest.