mibuso.com
Home› SQL Performance
Options

Severe SQL Server 2005 database performance issues

srfrseansrfrsean Member Posts: 3
edited 2008-02-14 in SQL Performance
We are having severe performance issues on Nav 4.0 running against SQL Server 2005. The disk performance is clearly a problem and is being addressed with a hardware upgrade. After that is anyone having major problems with concurrent user activity when running against SQL Server?

Comments

  • DenSterDenSter Member Posts: 8,307
    It is quite common to have performance issues with NAV on SQL Server. This is such a complicated area though that each implementation has its own set of unique problems.

    In general:
    • Make sure your hardware is up to standards. At the very least it is recommended to separate storage of the data files from the operating system spindles, with a dedicated physical drive for the transaction log. Do NOT use RAID 5, no matter what your hardware vendor tells you.
    • Put in place maintenance plans for your SQL Server database. Unlike the native C/SIDE database server, which can be installed and forgotten, SQL Server needs to be maintained. There are many posts about this, so you might want to search the forum. You can purchase tools for this, and personally I have my preference about which tools to use
    • Your tables will probably need to be tuned. The tools I just spoke about also has analysis capabilities to help quickly identify which tables to tune. You can also analyze the system manually, but that will take much more time.
    • Code review. This you should not do until you have done all other steps, as it gives the least 'bang for the buck'
    Of course these are general guidelines. Your situation might be completely different. If the code is bad enough, it will be a much bigger problem than usual. Hardware upgrades, as long as the initial hardware was set up properly, usually doesn't provide nearly as much improvement as expected. I have to say though that I have also seen hardware upgrades make a HUGE difference, even though at first I was not sure if that would make much of a difference.

    You would probably benefit from having an experienced specialist involved to help you. Start with your partner, and if they don't have the right resources I'd recomend getting someone externally involved.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Alex_ChowAlex_Chow Member Posts: 5,063
    srfrsean wrote:
    We are having severe performance issues on Nav 4.0 running against SQL Server 2005. The disk performance is clearly a problem and is being addressed with a hardware upgrade. After that is anyone having major problems with concurrent user activity when running against SQL Server?

    SQL Performance problems are always touchy subjects and is unique to every installation because every business is ran differently.

    There's a person out in Michigan who does it pretty well. His website is: www.risplus.com
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • srfrseansrfrsean Member Posts: 3
    Thanks, we're moving the locally stored datafiles onto SAN RAID 1 + 0 disk hopefully tonight. We have already spec'd out a new server runningi Windows 2003 Enterprise so we can go to 8GB or more of RAM.

    When you say tune the tables are you referring to new indexes/redesign indexes or actual table changes?

    I appreciate the help. I just took this application on recently and it needs some help fast.

    srfrsean
    DenSter wrote:
    It is quite common to have performance issues with NAV on SQL Server. This is such a complicated area though that each implementation has its own set of unique problems.

    In general:
    • Make sure your hardware is up to standards. At the very least it is recommended to separate storage of the data files from the operating system spindles, with a dedicated physical drive for the transaction log. Do NOT use RAID 5, no matter what your hardware vendor tells you.
    • Put in place maintenance plans for your SQL Server database. Unlike the native C/SIDE database server, which can be installed and forgotten, SQL Server needs to be maintained. There are many posts about this, so you might want to search the forum. You can purchase tools for this, and personally I have my preference about which tools to use
    • Your tables will probably need to be tuned. The tools I just spoke about also has analysis capabilities to help quickly identify which tables to tune. You can also analyze the system manually, but that will take much more time.
    • Code review. This you should not do until you have done all other steps, as it gives the least 'bang for the buck'
    Of course these are general guidelines. Your situation might be completely different. If the code is bad enough, it will be a much bigger problem than usual. Hardware upgrades, as long as the initial hardware was set up properly, usually doesn't provide nearly as much improvement as expected. I have to say though that I have also seen hardware upgrades make a HUGE difference, even though at first I was not sure if that would make much of a difference.

    You would probably benefit from having an experienced specialist involved to help you. Start with your partner, and if they don't have the right resources I'd recomend getting someone externally involved.
  • DenSterDenSter Member Posts: 8,307
    Make sure that the TL is put on its own, separate, dedicated, physical disk array. Most commonly, NAV databases need a 2 spindle RAID 1 for the TL. The more spindles you use in a 1+0 for the data files, the better. It's throughput capacity you are looking for, not storage capacity.

    Tuning tables in NAV means removing redundant indexes, and eliminating redundant SIFT levels. Having too many indexes and SIFT levels is by far the biggest cause of performance problems on NAV.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • bbrownbbrown Member Posts: 3,268
    DenSter wrote:
    ...a 2 spindle RAID 1...

    Aren't all RAID 1 arrays 2 spindle? :D

    Or do you mean span the log over 2 RAID 1 arrays?
    There are no bugs - only undocumented features.
  • bbrownbbrown Member Posts: 3,268
    How large is the database and how many users?

    Have you considered moving to 64 bit Windows Server and SQL Standard 64? If the only reason you are moving to Enterprise is larger memory support this may be a more economical move. Of course depending on other factors there may be reasons to use Enterprise. The need to support 8 GB is not one of them.
    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,307
    bbrown wrote:
    DenSter wrote:
    ...a 2 spindle RAID 1...

    Aren't all RAID 1 arrays 2 spindle? :D

    Or do you mean span the log over 2 RAID 1 arrays?
    Yes all RAID 1 arrays are 2 spindles, but not all 2 spindle arrays are RAID 1 now are they :mrgreen:
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • krikikriki Member, Moderator Posts: 9,121
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Sign In or Register to comment.