Nav SQL Table Locking, performance Issue

anilkumar
Member Posts: 136
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.
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 :

Log files path :

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.
Anil Kumar Korada
Technical Consultant
Technical Consultant
0
Comments
-
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.0 -
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.David Singleton0 -
[Topic moved from Navision forum to SQL Performance forum]Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions