Hello, last weekend we’ve updatet a customer from Navision Attain 3.60 (HF 30) to Navision Attain 3.70 (HF12). The Platform is a Win2K Server with MS SQL Server 2000 (SP 3a). Although we updatet from MDAC Version 2.7 (SP1) to 2.8. Last week everything was fine. This monday we’ve had a lot of problems. The sql-server-process suddenly uses up to 100 % of the cpu-time (4 xeon cpu’s). The system get’s really slow. Has somebody an idea? Any experiences with the newest Attain client an MS SQL Server 2000 an performance problems? regards Christian
Did you check the performance for every cpu ? Also the memory performance counters should be checked . It might be the memory slowing down the system. Also you could try, if the System: Context Switches/Sec is also high, switching to fibre mode scheduling instead of thread (lightweight pooling=1). Never actually used it, but who knows ? Good luck! [;)]
hi, yes all cpu’s are used the same way (between 50 and 60% and up to 100% cpu time). How can I find out if the memory is slowing down the system (which counters do I have to read and how do I interpret these counters)? Our customer has 24 GB RAM. The SQL Server is cofigured to use dynamically between 14 and 20 GB. thank you. Christian
Hi, the counters that you should check are the following: *“Memory:Pages/sec”-This counter shouldnt be consistently greater than 0. *"Process:Page Faults/sec/SQL Server Instance"-if this counter has a high number then you might have a problem of excessive paging and disk trashing. *Theres a “Memory:Available Bytes” that should always be >5000kb, but I dont think you could have this problem with 24 Gb of RAM [:)]. *"Process:Working Set/SQL Server Instance"-this should also be >5000kb. *"SQL Server:Buffer Manager:Buffer Cache Hit Ratio"-This should be>90%. *"SQL Server:Buffer Manager:Total Pages"-a low number indicates frequent disk I/O or trashing. *"SQL Server:Memory Manager:Total Server Memory"-this counter should not be consistently high in comparison to the amount of phisycal memory available. *"Processor:%Privileged Time"-Monitors the percentage of time that the processors spend in privileged time executing operating system kernel commands. *"Processor:%User Time"-This can indicate that other processes or applications are executing and preventing SQL Server operations. *Also, dont forget about “System:Context Switches/Sec” that should be<8000. *For the procs too is “System:Processor Queue Length” wich indicates the number of processes waiting for the procs to free up. If the values for the last two counters are not ok you should switch to fiber mode switching. The rest of them are about memory issues. You should also check (who knows?) the “PhysicalDisk:Avg.Disk Queue Length” counter. This sould be no more than two times the number of spindles. Well… this should keep you busy for about an hour or so ! [:D] Good luck![:p]
hey gigi, thx for your help. i’ll check this. today i’ve found out another interresting thing: since lase weekend the tempdb grows up to 6,5 gb. with the old 3.60 client the sql server tempdb was never used. the size was at a maximum of 400 mb. since the setup of the 3.70 client it seems that the tempdb is used very often (to manage the cursors). the tempdb has the autogrow-flag on and always grows with 10%. that happens during the day and i think that this is a big performance killer too. last night we’ve downgraded to 3.60 again (don’t ask how - we used many dirty tricks ;)). but the tempdb is still in use (remember: last week it was never used). i ask myself why?! there’s no other sql server application on the server. any idea concerning the tempdb? regards christian
hey gigi, it’s me again. nearly all the counters are in the ranges you’ve specified. there are only 2 counters i’m not sure: - server memory manager: total server memory. this counter is at 20 gb. this is the max-value of the configured memory. but i think it’s ok to leave the operating system 4 gb. there are no other applications on the server running. - system: context switches/sec: this counter has a average of 8494 switches per second and a max-value of 16227 switches per second. i think that’s too much. i’ve changed the option lightweight pooling to 1. after a reboot this will take affect. let’s try this one. regards christian
Hi chr, The first counter “Total server memory” being at 100% means (theoreticaly at least) that you should increase the available memory, either by decreasing the s.o. memmory, or by adding more RAM. Personaly, I wouldnt do it-24GB is a lot of RAM![:)] You should decide whats best. About the other counter, I never actually used that option (fibre switch mode) so I dont know if its gonna help. It should take some stress off Win2k, thus working faster, but who knows ?.. Regarding the temp db, did you check for any dead locks ? Does it grow continuosly ? (the temp db I mean)
Isn’t the temp db also heavily used for queries in tables without a (good) key. Can’t you look with the profiler.
quote:
Originally posted by ajhvdb
Isn’t the temp db also heavily used for queries in tables without a (good) key. Can’t you look with the profiler.
Thats a good ideea! I thought of that with another occasion, but theres a question bothering me: if you create an index in sql server, how is this going to affect navision (on long terms)? Has anybody tried that ?
Some clarification: tempdb is not used by Navision for cursor management. Navision uses either dynamic or fast_forward cursors; neither of these are meterialized in tempdb and none of this has changed between 3.60 and 3.70. tempdb is not used for queries lacking good keys; tempdb is used for subqueries and derived tables; for merge joins and sort operations. None of these apply to Navision. Your tempdb growth problem is not related to version 3.70, and I’d put money on that nor is the cpu utilization problem either. Has anything else been done on the server machine? Service pack or MDAC changes? Because it sounds from your cpu problem that your server installation is unstable - it is almost impossible to write client SQL requests that will cause this kind of utilization, so I suspect the server is exploiting a bug. Be careful downgrading back to 3.60 from 3.70 - you have to handle the [$ndo$dbproperty] table properly. How are your problems now that you have done this downgrade? Question: what do deadlocks have to do with tempdb?
Im thinking that deadlocks in tempdb should increase the number of updates (utilisation) in tempdb, thus decreasing performance. How do you know what kind of cursors navision is using ? Id like to know this kind of details too. Do you have some docs or did you start “digging” at some point ?
Don’t change threads to fibres as this stops the Navision application.
hi rob, last weekend we upgraded from 3.60 to 3.70. also we’ve updatet from mdac 2.7 sp1 to mdac 2.8. these were the only changes we made (on the sql server and on all terminalservers). monday morning we had the big problems. the system was not very fast last week but it was working. this week the cpu-time suddenly rises to 100% and stays there for some minutes to some hours. the system is logically very slow at this point. tuesday evening we’ve downgraded from mdac 2.8 back to mdac 2.7sp1 and also we’ve downgraded from 3.70 (hf12) to 3.60 (hf30). all these steps were made with a very close conatct to mbs hamburg and mbs denmark. so we now have the same system like we had last week. but the problems still are like monday morning. mbs hamburg and mbs denmark also told us that there is no change in the cursor-handling between 3.60 and 3.70. but we see (sql profiler) that many cursors have to be recompiled by the sql server and the sql server does this in the tempdb. regards christian
Hi Gigi, I think Robert just has access to some informations that other don’t and never will get… :-). But he is willing to share some of these with us, which we should be very grateful for.
Hello, we have a great problem of performance and deadlock in Italy in a project with 200 users. We are in 3.60 SQL Version. Maybe do you know which MDAC version is necessary for Navision Attain 3.60? Thanks elena
MDAC 2.6 is necessary but 2.7 and later are also fine. This has nothing to do with deadlocks though. Unfortunately this requires troubleshooting the application and is often not easy. The ‘Performance and Troubleshooting Guide’ might help you out.
It seems to be a memory poblem; some time ago i had more than 3 gigs of RAM on a server and i needed to dig to find out how can i make SQL Server use beyond that limit. Do your SQL Server instance really use more than 3 Gigs? Is the AWE support corectly configured? Maybe you would like to read the link above: "Disabling AWE To disable AWE, set awe enabled to 0. This setting is the default. The AWE API is not used. SQL Server 2000 operates in a normal dynamic memory allocation mode and is limited to 3 GB of physical memory. " http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg.asp