How to configure Drives for Optimum performance with SQL

glennrl

I am running Navision 3.6 on a Dell 6600 Quad Xeon machine with 4GB of Ram. I'm using PERC 4 hardware RAID 10 with 4 drives dedicated to the Database files. RAID 1 with 2 drives dedicated to the Log files and RAID 1 with 2 drives for the Temp database.

There are two database files, a primary (.mdf) and a data file (.ndf). Both of these files reside on the RAID 10 array comprised of 4 drives. The log file is a single file and the Temp database is divided into 4 primary files.

I was wondering if anyone could help explain the best way to setup the drive system for optimum performance. Currently I'm seeing a lot of PAGEIOLATCH_EX waits come up. From the client side the system seems slow.

I've read that as you increase the number of drives you should divide the database among them. I'm not sure if that means creating several data files (.ndf) and placing them on the separate drive volumes.

Any help would be greatly appreciated.

Thanks,
Glenn

kine

It can help, but in this case will be solution the key optimizations (optimize cost of modification - use as few indexes as possible, use optimal indexes, use SIFT tables just if needed to keep the performance of reading...) - if you calc the cost of one insert for example on standard table Item Ledger entry, each insert need to modify 16 indexes, 2 SIFT tables with each with 5 levels of details... it is 27 modifications per one record insert (16 indexes, 2*5 SIFT records, 1 record itself). You can disable not-needed indexes (indexes, not keys) and you can lower the cost to 50% without noticable performance lost on reading...

But it needs some time and testing...

Saro

I'm sorry guys i seem to not understand the difference between key and index in navision? can somebody please clear this issue for me? when i go view --> keys in table designer, those are the keys right? so where are the indexes? i think i'm having the problem of understanding the names

kine

Key - it is what you see in Navision - it is Navision key. Index is what is on MS SQL. If MaintainSQLIndex is Yes - for the key will be MS SQL Index created. If no, there will be no SQL Index.

Key - used for reading data from DB and sorting them

Index - used for reading data, but not for sorting. MS SQL is able to sort data in any order without using index for that. It is why you do not need to have Index for all Keys. And because you need indexes mainly for fast reading data (filtering) you need indexes with good selectivity (search for "selectivity" for more info).

See Performance troubleshooting guide for more info about optimizing Navision for MS SQL.

Saro

Thank you very much

DenSter

If you have a RAID 10 for your database files, the hardware controller will already write the files over multiple disks, so you don't have to create multiple files. It should be enough to just create one file per logical drive, set the sice to what you expect the app to grow to in about 1-2 years, and turn the autogrow off. Also important is to keep the log drive dedicated to the log, don't temporarily store backups on the drive, or you'll notice performance decrease.

What could also help is schedule database maintenance (SQL Server updatestats, index defrag, stuff like that), which you should be able to find in the performance toolkit.

Saro

DenSter
Would this also work for navision server version? as to getting the benefits of a raid 10?

kriki

Saro wrote:

DenSter
Would this also work for navision server version? as to getting the benefits of a raid 10?

Also the Navision server gets performance benefits from RAID10.
What I don't know is what is better : 1 RAID10 with 4 disks or 2 RAID1 with each 2 disks.

DenSter

The native database server only knows how to utilize one processor, and there's a maximum RAM usage of 1GB (effectively about 800MB), but yes you can use RAID 10 on the native database server.