Performance/speed degradation

pmusu
pmusu Member Posts: 55
edited 2009-05-27 in SQL Performance
Hi,

We have a client who is experiencing a gradual decrease in speed on a particular process. There has been no recent changes to the code. They are using Navision 3.70B with SQL Server.

Has anyone encountered a similar problem? A few things they have done is rebuilt the indexes (using SQL maintenance). Last year the performance on this particular task was 5 seconds. It now takes over 1 minute.

We cannot seem to replicate the problem on our local system (they provided a backup). Using client monitor we are not noticing any bottleneck in the process (31ms).

Any suggestions?

Thanks in advance.

Comments

  • garak
    garak Member Posts: 3,263
    Did you check the querys from NAV to SQL with the SQL Profiler and the client monitor?
    Which process has problems with the performance?
    What for kinds maintenance jobs exist and how often runs this jobs?
    There are some codechanges or some indexchanges?
    Do you make it right, it works too!
  • pmusu
    pmusu Member Posts: 55
    garak wrote:
    Did you check the querys from NAV to SQL with the SQL Profiler and the client monitor?
    Which process has problems with the performance?
    What for kinds maintenance jobs exist and how often runs this jobs?
    There are some codechanges or some indexchanges?

    Thanks for the quick response.
    I have used client monitor. They have a customized form (non-standard). It is an quote/order form. They click on a button where you change the status.

    In client monitor the statement which takes the most processing time is 31ms. I am focusing on that process. I originally asked the question because I am still not sure what is causing such a decrease in performance.

    Thanks and regards
  • garak
    garak Member Posts: 3,263
    edited 2009-05-28
    Take also a look to your SIFT Structure and check also with the Profiler on the SQL Server.

    Here a link with the needed indicators: viewtopic.php?f=34&t=32657
    Do you make it right, it works too!
  • stryk
    stryk Member Posts: 645
    With NAV 3.70 I suppose you use SQL 2000 ...
    One crucial aspect is to optimize the SIFT structures of important tables (= many records, many SIFT, heavily used) by reducing the number of "buckets" - see: http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx

    And: how big is your DB? How many users? What are the hardware specifications of the SQL Server?

    Last but not least I recommend to search this forum for "SQL Performance" and you'll find tons of advices to optimize NAV/SQL performance!

    Regards,
    Jörg
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool

Categories