Howdy, Stranger!
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
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Attain 3.10 Migrate to SQL Server 2000
Faizel_Tilly
Member Posts: 8
Attain 3.10 - I have done a migration from Navision Application Server to SQL Server 2000.
We have a 150 user site and running on SQL Server has created some serious performance problems. Too many table locks, reports take forever to run and system generally very slow.
Can somebody please give me some advise on the following:
1. SIFT Tables - for every key in the base table, we have a SIFT table in SQL. Example - Item Ledger Entry table we have 20 keys, we now have 20 additional SIFT tables in SQL and I would imagine that every one of these tables get updated every time you insert/modify a record. The base table has 3,5m records. Just one of these SIFT table has 4,7M records. Do we really need these SIFT tables in SQL - what happens if we set the MaintainSiftIndex property to NO for some or all keys? Will this have an impact anywhere? Will it improve performance?
2. Locking - who controls locking? SQL or Navision? What is the most efficient method of locking?
3. Any other ideas on making the system run faster?
Any advise will be appreciated.
We have a 150 user site and running on SQL Server has created some serious performance problems. Too many table locks, reports take forever to run and system generally very slow.
Can somebody please give me some advise on the following:
1. SIFT Tables - for every key in the base table, we have a SIFT table in SQL. Example - Item Ledger Entry table we have 20 keys, we now have 20 additional SIFT tables in SQL and I would imagine that every one of these tables get updated every time you insert/modify a record. The base table has 3,5m records. Just one of these SIFT table has 4,7M records. Do we really need these SIFT tables in SQL - what happens if we set the MaintainSiftIndex property to NO for some or all keys? Will this have an impact anywhere? Will it improve performance?
2. Locking - who controls locking? SQL or Navision? What is the most efficient method of locking?
3. Any other ideas on making the system run faster?
Any advise will be appreciated.
FAIZEL
0
Comments
http://www.mibuso.com/forum/viewtopic.php?t=7876
http://www.mibuso.com/forum/viewtopic.php?t=7235
http://www.mibuso.com/forum/viewtopic.php?t=6173
etc.
:-)
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.