Navison sql 2000

jcald81

I am working on a project where navision/sql seems slow when doing postings and when pulling some records. The server has two 3.2gig processors 8gb of ram a raid 10 and raid 1 drive the mdf files are on the raid 10 drive and the log files are on the raid 1 mirrored drives. It is a2k3 server. Server connects to a gb switch and the 40 +- clients connect to a 100mb switch that has a 1gb uplink to the gb switch. In anycase one select statment returns 25000 reads when i see it in profiler not alot of cpu time or anything though. Should I work on this table a clustered index or clustered and non clustered. The main problem is postings orginally bathches of 2000 where being done and this slowed down everyone significantly, now the bathes are being broke up into 250 at a time. The only slow down I can think of here is i/o time for the disk which should be optimized since the log files are being written to a seperate raid and the db's that are being written to are striped across 4 drives. The database size is about 12gb we actually have a 8gb training database and a 12gb live database. Also is there a way to set logfiles to grow to like 512mb then start overwriting or do i just have to dump them and start at new one I'm thinking I could configure a scheduled task to do this if I have to but I prefer the idea of just overwriting the logfile after it grows to a certain size. Is anyone out there tried putting sp4 on the sql server? I am about to try this but hate to try it on a live site without some testing or feedback first. Would breaking the database up into a coupble of ndf files help maybe like 2gb or so each and spread them out on seperate drives? Im pretty sure the network and hardware is optimal. One last question does navision makes its own temporary store procedures ive seen some sp_exec 3x, xx, xx, xx

If i cut and past this in query analzer it can't find the stored procedure. Any input or help is greatly appreciated.

bbrown

A few suggestions/comments:

Navision:

Consider disabling the SQL maintenance of SumIndexes. Recalculating these values can add significant overhead to SQL. Turning of the ones used for reporting only can add performance. I had a system a couple of years ago that was posting purchase orders of 400 - 500 lines. Turning off SumIndexes helped.

Hardware/OS:

SQL Service Pack 4:

There is a known issue with large memory servers. There is a patch available. See Microsoft Knowledge Base Article 899761. I have had SP4 running for a few months at 2 clients plus my development machine. I have not seen any issues.

Is the logfile on a dedicated drive? By dedicated I mean that no other files are on this drive.

Are you expanding the database/logfile manually or relying on autoqrow? Autogrow can seriously impact performance if it occurs during peak system usage.

Is SQL aware of the extra memory? (Enterprise?)

With the large transaction sizes, consider expanding the number of drives in the RAID 10 array. Along with writing new data, a posting involves a large amount of reads. Spreading these reads across more drives will help performance.

I do not recommend modifying the indexes outside of Navision.

There is not a way to set a growth limit on log files. The solution is to trunctate the file more often.

Consider moving the samller datafile (*.mdf) to its own RAID 1 drive.

Navision does not use stored procedures. The Navision C\Side statements are converted to SQL by the Navision SQL Client and executed.

Hope this is helpful.

jcald81

The logfiles are on a dedicated drive I just set up the other day its a 73gb mirrored hot swap scsi drive. The server originally started out with 4gb we added another 4gb ddr 2100 with ecc I put the /PAE switch in the boot ini so the server is recognizing it and set sql memory usage to have 6gb of reserved memory. Just a note or annoyance about sql why do they put a slide bar to set memory instead of letting a person just type it in, its very hard to get it set to a exact amount like this. I will see what i can do about limiting the sum indexes. For the past 6 months the logfile autogrow was set to 1mb which I complained and complained about and finally got acccess and changed it to autogrow by 10% . Right now there are 4 drives in the raid 10 array the server physcially has space for another 2 drives which could be added into this array. I will try your suggstions I greatly appreciate your help. Thanks

bbrown

Run the memorystatus command in query analyzer and verify the amount of memory SQL is using. What versions of SQL and Windows Server are you using.

Do not rely on Autogrow. Watch an grow the files manually. Use AutoGrow only as a safety net.

Consider disabling SQL maintenance of unneeded indexes. Indexes used only for reporting do not need to be maintained in SQL.

Review the Navision code and use of indexes. Set a key before any database calls and set filters for all fields in key. Use the performance toolkit to assist you here.

Implement a periodic rebuilding of indexex.

(i am headed out for the day. I will check this post when I return).

kine

See tables in MS SQL On-Line books - page "Maximum Capacity Specifications" for check, if you have correct SQL version and OS type for Memory limits and CPU limits... Do not forget to use switches /PAE /3GB on OS and /AWE on MS SQL server.

Divide the HDDs to more separate spindles in Raid 1.

If you are not backuping transaction logs, do not use them and switch to Simple recovery mode.

If you have CPUs with Hyper-threading, disable the HT (somewhere in BIOS or elsewhere). It is not good for DB servers (deadlocking between threads of one application).

kriki

kine wrote:

If you are not backuping transaction logs, do not use them and switch to Simple recovery mode.

But this way, or just truncating the logfiles, you lose the recovery-possibilities of SQL. To keep the recovery-possibilities of SQL and keeping the log-file small, you can do a transaction-log backup every hour and store it on another disk.

For the rest, others have given you good hints. I can only add one:
To keep SQL fast, you need to rebuild the SQL-statistics at least once a week. You might do this together with the backup of the DB.

bbrown

NEVER PARTITION PHYSICAL HARD DRIVES.

Disk system performance is directly tied to the number of physical drives and read/write heads available. Partitioning drives requires the system to share the available drive time across the file systems.

Yes, you should backup the transaction log before truncating it. The SIMPLE recovery mode does not disable the transaction log, it jsu truncates it on checkpoint. A large transaction can still result in a large log.

davmac1

If you are on version 4, you could look at downloading SP1 if it does not get delayed again.
It supports a number of SQL Server tuning enhancements in Navision.

markvandermeij

First: the technology used by Navision is ancient technology; it's something I used 10 years ago when using 4GL's like dataflex.

Second: the way it's implemented using their Native database is great and fast. If you can keep the numbeer of users to a maximun of 50 and moving all batch postings to none working times.

Third: Now they are using other DB-engines like SQL Server, all modern DB engines are focusing on client-server technology, this is not how Navision does it. Navision is still a fat client (where all code is handled) and the DB-engine only processes the queries past to it.

Take a look at the processor time used by the SQL server and you wll see it's minimal. Looking at the amount of network packages it will be a multitude of the processing time.

While everyone is waiting for the version where the C/AL code is based on .NET technology I'm waiting for the version where the major posting units (cu 80 & 90) are moved to the (SQL) server as stored procedures. This would really move Navision to the top of ERP applications.

I like Navision and especially how workflow processes are handled, I guess this the final optimization will be moving it to true client server application with all proceses already implemented.

I know this not going to give any answers, but it's something to remember, (hardware won't solve all your problems).

jcald81

Does navision make some of its own tempoary stored procedures now? By the way Figured out the slow down problem the o/s install that sql 2000 was running on had some issues with sp1 paticularly a acpitabl.dat it hung there during boot time removed sp1 and it sped up considerably