SQL WennSoft GP questions

(0) Share

Report

Posted on by Community Member

a general observation (and considering there are only 5 GP users) our operation of extracting data and using GP has been progressively slower. It is still at a point of concern, even without as many errors, as it causes a lot of frustration and lost time. Is there anything we can continue to do, to alleviate some of this slowness and also continue to improve the ways we tend to SQL hold ups and GP errors.

I don’t believe this is a server memory related issue, and there is plenty of free space on the server for now. So, in addition to my question above, what may be done, considering database maintenance. Is there anything we can safely do to normalize the database?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Boy, that's a pretty broad question. There are so many things that can slow a system down it's almost impossible to troubleshoot via a forum. However, check the Dex.ini file and make sure SQL Logging is not turned on. If it is, it will have a value of TRUE. Set it to FALSE. Writing to a SQL Log over an extended period will definitely slow things down.

Also, you don't mention where the slowness exists. Is it upon login? Running reports? Opening windows? Posting transactions? All of the above?

What GP version are you on? SQL? How many, if any customizations or 3rd party apps do you have and what are they?

Need more detailed info to even begin helping with this one.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I would like to say that I could narrow it down, however, we are experiencing slowness with a wide array of issue; specifically running reports or just general SCID summary modifications.

[General]

SQLLogSQLStmt=FALSE

SQLLogODBCMessages=FALSE

SQLLogAllODBCMessages=FALSE

ScriptDebugger=TRUE

*However, can I set the ScriptDebugger to “FALSE”?

The DEX file is not logging. And we have very few applications, hitting the SQL box or database aside from MS CRM and the WennSoft products, such as MobileTech and Customer Portal.

SQL is running at 2008 R2 as well as the server version and GP is 2010

5 users accessing at a time running on a box with 24GB RAM

Production DB is 9104.88 MB of 593.32 MB available

For lack of a detailed description, I can simply say that the users, up until recently have experience typical processing time when using GP/SQL. Now, however, it is slow and freezing more frequently, especially when performing the aforementioned tasks. It almost feels like we are reaching some sort of a threshold. I am most interested in how we can refresh and/or clean up our DB.

Was this reply helpful? Yes No
Melissa Sandrovich on at

Like (0)

Report

I think you might want to get you GP Partnered involved to help with some diagnostics. There are so many things it could be.

Also - you are mentioning issues with just about everything. Have you tested with GP on the server? Maybe you are having network issues.

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

I'm not sure if this has been resolved, but I noticed you mentioned ScriptDebugger=TRUE.

Unless you're actively using Debugger to troubleshoot an issue, the recommendation is to change this line to FALSE in the Dex.ini, as we've seen the Script Debugger cause odd behaviors within Dynamics GP application when it's being loaded with the application, that we don't see when it's not loaded.

If you haven't already, there's also our Dynamics GP Performance White Paper that I'd recommend looking at. In this document, we go through a number of causes we've seen cause performance type issues with Dynamics GP. The document mentions GP 2010, but it's the same for GP 10 and GP 2010 as well, as not that much has changed.

www.microsoft.com/.../details.aspx

As mentioned already, there are so many things that can potentially cause performance issues in Dynamics GP, it's almost impossible to troubleshoot through Forums.

Thank you,

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi,

Like the others, it's a big question to answer on the forum. I can only add things that have made a remarkable difference in performance for some of my larger clients. When I say 'larger', I mean a larger database. In my world that is something in the realm of 100 gig for the .mdf file.

What turned us from 'take a coffee break' to 'this works' was the distribution of the data. We added more hard drives to the installation. Not more 'partitions' more physical drives. We separated each of these onto a different physical drive, or drive array:

Temp db

swap file

application

transaction log (.ldf)

database file (.mdf)

operating system (windows)

For larger databases, we split the .mdf file into multiple files in SQL.

Also, look at your inventory valuation method (if you use inventory). Average perpetual will take forever (and I mean forever) to post if you have a heavy transaction load and allow your inventory to go negative. The system will recalculate the cost at every line. If you are looking for a level cost, consider changing to standard cost. The system will speed up so much, you will think it's not really processing the transactions.

Look at what else is running on the SQL server and the amount of memory and priority you give SQL vs other tasks.

Consider using a process server. This won't 'speed up' the actual processing, but it will throw the job of doing the processing off of your workstations so that the workstation can continue on without waiting for whatever you're doing to complete.

If you have millions of records in your database, you might consider archiving or deleting old data to speed up your searches, etc.

I hope this at least gives someone a few tips on how to speed up a slow production.

Kind regards,

Leslie

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi Wennsoft,

Also have a look on the below general link

support.microsoft.com/.../898982

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

There are many things needs to be consider for your case. I would suggest you to contact GP partner or Microsoft Support team to look into this case.

Hope this helps!!!

Was this reply helpful? Yes No