We have implemented MBS navision 3.70IN at a client site using SQL server. There are around 35 users concurrently logged in at Peak time doing the same set of activities. We are facing the following issues: ISSUES: 1. Activity Deadlock error displayed on Sales order processing (new Customized functionality) and Sales order posting for different tables at different times.Primary tables involved are :‘G/L Entry’,‘Reservation Entry’,‘General Journal Line’,‘Structure Order Line Details’(part of India localization for Sales tax etc). 2. Server becomes very slow (about 10 times slower than normal) when multiple users are processing the same functionality.We observed the Lock Request monitor on SQL and it showed 100% lock request during the time when the server is slow(clogged). SERVER CONFIGURATION: Windows 2000 Server with SQL Server 2000 Enterprise. 4GB RAM (SQL server using 1.8GB) RAID 0 Server 4 physical drives of 68 GB each 3 Logical Drives - C (hosts the OS and SQL server) D (Database files),E(Transaction Log files) CONNECTIVITY:Users are connected through VSAT connectivity using CITRIX.Some Users connect through LAN in the vicinity of the Server.Both these types of users experience similar issues as mentioned above. ADDITIONAL INFO: The Sales order process has been customized to reverse calculate the Unit price for each item on the sales line.This process fetches data from several new/Customized tables(like MRP Master,Scheme Master) and modifies the existing/base application tables (sales line,structure Order line details,Reservation Entry) as well as some new tables (Tax Detail Buffer,Scheme Transaction Details). Its been almost a month now since we have been trying to eradicate this problem and although we have been able to considerable reduce its occurance it has not vanished completely (keeps coming at irregular intervals) The parameters we have been working on are: 1. Server ConfigurationThe Server RAID Technology has been changed to RAID 0 from the earlier RAID 5 configuration. 2. Multiple Database Files:multiple data files have been created on separate logical drive letters. 3. Code Optimization: I have optimized the Sales order processing logic (especially the Tax calculation code) which has drastically improved the SO processing speed atleast by 10 times.This will ensure that the Activity Deadlock error is a rarer event. 4. SQL Reindexing: Reindexing has been run on heavy tables through a feature in the Navision Application itself.This needs to be done on a regular (once in every 2 days) basis. 5. MaintainSIFTIndex : This is a property which has been reset to ‘NO’ for tables where the modifications are higher than Flowfield calculations. 6. Object Cache: The Object Cache value has been increased and should be atleast 20MB on all individual Client machines.Object cache, like cache, allows Navision to work faster. Objects such as code, descriptions and windows that will be used on the client computer are stored in the object cache. This means that the client computer only needs to retrieve these objects once from the server. The client computer must have enough memory to store the objects while they are being used. After these activities the performance has improved but the system still goes into a ‘HUNG’ state with ‘activity deadlock’ error messages. We have also been observing the SQL performance monitors (see attached file) to look for areas of abnormal performance. I used the SQL performance monitor tool to analyize the code and the key usage (this data is also attached alongwith). If anyone has experianced similar problems and has solutins to the same please respond. Thanks in Advance Regards Reuben
Attachment: SQL Parameters Observations in excel.zip ( 373587bytes )
Attachment: blocked sessions situation.zip ( 28738bytes )
Attachment: Performance Monitor in Normal Situation.zip ( 2258bytes )
Attachment: Performance Monitor in Hang Situation.zip ( 2190bytes )
quote:
We have implemented MBS navision 3.70IN at a client site using SQL server. 4 physical drives of 68 GB each 3 Logical Drives -
Originally posted by reuben_alexander - 2005 Jul 11 : 06:21:00
OK, step 1 might be to shoot whoever designed your server configuration… how do you even get 3 logical drives on 4 drives in RAID 0? I can see ways of doing it, but none that make sense. Please search this forum for “SQL Performance RAID” and you will find a lot of usefull information. Before you do anything else, rebuild your server.
Ok here we go, just getting rid of the most severe “things” … (sorry, I could not download your attachments) How many CPU are on the Server? You need at least two, better 4! No Hyperthreading! How is you “Processor Queue Length”? Why just using 1.8 GB RAM for SQL Server? Give him all he can get! Add RAM if possible, enable AWE! Erase all that “logical driver” sh… there is no use for these things on a database server, especially not for SQL Server. There are many books giving advice how to configure a SQL Servers disk-subsystem, here some “idealized” example: C:\ OS, Swap => RAID 1 D:\ SQL Server Program => RAID 1 E:\ Database files => RAID 10 F:\ Transaction Log => RAID 10 G:\ Temp DB H:\ Master DB => RAID 1 I:\ MSDB, Northwind, Pubs = RAID 1 Use fast and reliable disks; disable the disks’ “write-caches”! ReIndexing: Which NAVISION feature do you use? Forget about the “Optimize” feature! Use at least “Maintenance Planner”, or better create scripts (for this you could also use the “MBS NAVISION SQL Server Option Resource Kit” - this is intendet for 4.00 but it works fine on 3.70). Trace problematic transactions with the Profiler and feed the Index Advisor to optimize you indexes. Change the order of index fields for the clustered index (SQL site), based on the column’s selectivity. (e.g. to speed up postings with Dimensions it’s a good idea to move the “Document No.” field to the beginning!) Be careful when creating/deleting indexes or SIFT levels: Few indexes = faster write = slower read Many indexes = slowe write = fasrter read What about Index Statistics? Disable the “automatic create/update” stuff; create stats via Job (sp_createstats, sp_updatestats) frequently Pin some small “hot” tables (DBCC PINTABLE), including the “objects” table! How are your “ContextSwitches/sec”? If permanently above 8000 maybe you could use “NT Fibers”? (If you don’t use “Windows Authentification”) The only chance to get rid of Deadlocks is to investigate the processes, maybe using the “Performance Troubleshooting Tools”. Connectivity: The system is as slow as the slowest client is. If a slow client blocks any object all others (even if they are faster) have to wait! Get rid of “weak elements”, use fast clients and network connections etc.! And finally: I hope there is nothing else running on the Server? Disable all useless services, deactivate Virus scanners, etc. Well, above that’s just some “topics” (and of course the list is not complete), each of it could be discussed in detail … but I hope you got some “inspiration” from it. Good Luck!