[Last Call] Learn how to a build a cloud-first strategyRegister Now


Slow SQL Response

Posted on 2009-12-18
Medium Priority
Last Modified: 2013-12-11
We have a client running MS Dynamics GP 10. We just upgraded them from GP 9 to GP 10. The database size is approximately 300GB. What we are seeing is for certain screens the first time the user goes to that screen and runs a query it can sit there for 3-4 minutes before responding. Once it is done they can go and do the exact same query and the response is almost immediate. As long as they do not exit the accounting application this query will remain responsive. But as soon as the exit and come back in, it will be slow again on the first try. This is SQL Server 2005. The accounting program did not behave this way under GP 9. Any ideas as to what may be causing this or any way to try to find out what is causing this?
Question by:rwheeler23
LVL 26

Accepted Solution

tigin44 earned 1000 total points
ID: 26084562
There seems an execution plan problem..  do upgrade the statistics and rebuild the indexes...
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 1000 total points
ID: 26084965

Ya, I would agree.  This does not sound like a GP issue per se--more of a SQL optimization issue.  I would focus on standard SQL Server tuning best practices.  Rebuilding the indexes would be a start, and then if that doesn't solve the problem, I would run a Profiler trace on one of the windows that runs slow and then review the Execution Plan results to see what is going on.

GP database tables are indexed to the hilt and cover all standard queries, so there should be no reason why they don't work properly unless you are running an unusual query (i.e. SmartList with strange filter).

On the other hand, a 300 GB Dynamics GP database is absolutely insanely huge.  If that 300 number is correct, I would recommend looking into the Company Data Archive product. It looks like there is more than one, but this is the one I'm familiar with:



Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Author Comment

ID: 26085861
Your are correct company data archive is an alternative. Professional Advantage has the one I prefer to use. Most of the data is project accounting. They are a maerketing firm. I will inquire as to the number of years of data they are keeping.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you need to import sales transactions from another system into Microsoft Dynamics GP as Sales Order Invoices, you may need to store some additional data related to the customer, the transaction, or the line items.  Even if your company is manuall…
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question