Nav SQL Table Locking, performance Issue

anilkumar

Hi Experts,

Hereunder is the brief Client Database Structure :

Currently Operating 100 stores with a target of 300 stores within 1-2 years.

Store :

Each store has its own Navision native data base in which Primarily retails invoice are being generate. Statements are being created in day end and replicated to HO database daily.

HO :

HO Database is a Navision Server with SQL server Option. Day end statements from all 100 stores are being replicated to HO database daily and statements are being posted at HO which actually created ledger entries at HO. Later on Item ledger entries are again being replicated to Stores.

Currently HO & Store database has following Hardware Configuration:

Existing HO Server Configuration

Server HP Reliant ML570 G4 Server
Processor 2 X 64 bit Dual-Core Intel Xeon Processor 3.20GHz
RAM 1 X 6 GB
Hard disks 6 X 146 GB
RAID 5
Array Smart Array P400 Controller
1 Logical Array

SQL installation path : C:\Program Files\Microsoft SQL Server
Data files path : \Navision HO\Database\
Log files path : \Navision HO\Database\

Networking 2 X Dual NC371i multi-function Gigabit NICs

Operating System Windows Server 2003 R2 Standard Edition(SP2)
Database SQL Server 2000(SP4)

Existing each Store Server Configuration

Processor P4
RAM 512 MB
Hard disks 40 GB

Problems faced by the client :

1- Table locking of Item Ledger Entry while posting transfer Orders,statements.

2- Navision procedures getting slow (i.e. Scanning of item in transfer Orders) when some posting is being done.

Kindly suggest me whether the Hardware configuration is sufficient, or Upgradation is required, and How to handle Table locking in SQL and performance improve.

Marije_Brummel

There are to many things that come into play here to just say ok or not ok.

I'd strongly recomend talking to landsteinar and/or SQL Perform. They have one of the largest databases around wich does exactly the same.

Collecting data from stores. This is a 1.5Tera + database.

David_Singleton

That hardware configuration is way off. RAID 5, log on the same drive as the DB etc.

You really need to get an expert to look at you actual system, or this problem is going to get worse.

kriki

[Topic moved from Navision forum to SQL Performance forum]