Unanswered: MS SQL/Great Plains Server Utilization Nightmare!
1. Issue started popping up a month ago.
2. Microsoft says its a "network issue", and that we need to update our LAN infrastructure to Gbit ethernet.
3. The scenario:
1. Great Plains/MS SQL running on a dual Xeon w. 6GB onboard memory.
2. 30-40 users accessing GP @ any given time.
3. Server utilization (memory at about 1.9 for the OS & other processes/ MSSQL session taking about 1.7-1.9GB, so I still have a comfortable leeway of about 2GB)
4. Issues of server utilization spikes happens when running certain number of batches of report queries. Users created special forms using the VBA (Visual Basic for Applications) IDE. The other 30 or so users begin to timeout/loose db connectivity/and or sessions die during this process.
5. Not sure if DPS (Distributed Process Server) is being run to spread the workload on other workstations on the network. The forcefulness of Microsoft to say this is a network traffic/bandwidth utilization issues makes me think they are employing DPS for this application.
My questions are:
1. Could those customized VBA scripted reports be generating unecessary queries to tables due to sloppy coding, hence causing SQL to spike in cpu/memory utilization? Could there be dependencies hidden to the scripters that are kicking off that are hidden to them? Could certain stored procedures be kicking off due to hidden/unknown dependencies required in order to generate the reports being requested? Is there a log that shows these hidden dependencies?
2. If DPS is being employed to distribute the process workload for these jobs, that would cause traffic storms, even on a 10/100 switched network? If I don't have a layer 3 switch, is there a way to sniff out this traffic/strip the packets to view/ferret out this traffic generated?
3. We can run this report directly on the server, so this lends me to believe that this is not traffic generated between the SQL/GP server to the client, but a direct spike in server utilization, causing SQL/GP server response time to all the other clients to decrease, hence causing lost sessions/timeouts to the rest of the clients. Does this make sense?
4. Are there SQL performance tweaks we can make to optimize the performance/response time of SQL/GP, even under the extreme report generation/posting procedures? Is DPS the only way to go to relieve the workload on the SQL/GP server?
5. Now, the users also mentioned some "patches" that were made to the GP app, in order to repair some corrupted files. Unclear on that one.