Slow SQL Response

Posted on 2009-12-18
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

    There seems an execution plan problem..  do upgrade the statistics and rebuild the indexes...
    LVL 18

    Assisted Solution

    by:Steve Endow

    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

    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

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    A frequent question here in the MAS90 Zone is, How do you get data out of MAS90 for analysis, reporting or using the data on your web pages?   This is an introductory level tutorial to getting data out of MAS90 using ODBC. Data can be accessed by…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now