SQL 2005 Performance Issue

bbrown
bbrown Member Posts: 3,268
edited 2008-07-28 in SQL Performance
Experiencing random system hangs when scrolling thru list forms.

Example:

1. Open Customer Card and press F5 to open Customer List. Scroll to buttom of list then start to scroll up. Sometimes it works fine, others it will take 30 seconds+ to move up 1 customer.

Also similar behavior in other list forms with larger record sets.

Any thoughts? :-k
There are no bugs - only undocumented features.
«1345678

Comments

  • 2tje
    2tje Member Posts: 80
    SQL?
    lots of flowfields in the list?
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    Can you tell us what version of Navision you're using? I know Navision 2.6 has some SQL performance issues.
    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
  • bbrown
    bbrown Member Posts: 3,268
    Yes this is SQL (See Subject).

    There is one flowField (Balance). Removing it does not resolve issue.

    This is Version 4.0 SP2 (Build 22979).
    There are no bugs - only undocumented features.
  • hedegaard
    hedegaard Member Posts: 25
    You maybe could have a filter on the Customer card on a field that is not indexed.

    With a large number of customers this can make the list act like that.

    /Anders
  • bbrown
    bbrown Member Posts: 3,268
    hedegaard,

    Thanks for the reply.

    No filters are set and the form is using primary key ("No.").
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    An Update...

    Mirosoft has sent me Build 23099. This is a rolldown of SP3. They want me to test if this solves the issue.
    There are no bugs - only undocumented features.
  • hedegaard
    hedegaard Member Posts: 25
    I would use the client monitor and see what it going on.

    /Anders
  • bbrown
    bbrown Member Posts: 3,268
    When moving back up the list, the SQL statement is re-executed and the records retrieved again. This is where the delay is. (from Client Monitor).
    There are no bugs - only undocumented features.
  • frgusto
    frgusto Member Posts: 32
    I am experiencing similar behaviour in the same version.
    Did the MS rolldown solve the issue?
  • DenSter
    DenSter Member Posts: 8,307
    Do you have clustered indeces for those slow tables?

    The clustered index "fix" was a SQL script that did not update the NAV table definition. You run the SQL script, clustered index is added.... make a change to the NAV table definition, clustered index is gone again because the script did not check the box.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • bbrown
    bbrown Member Posts: 3,268
    Thanks for the replies..

    BlackTiger:

    There is no code or predefined filters/sorting on the form. With the exception of a couple of extra fields displayed it is the standard Form 23.

    Frgusto:

    I ran into a problem with the rolldown. As soon as I resolve that, I will test and let you know.

    Denster:

    Good point. I will have to double check that.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    The cluster index is okay.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    I have been able to duplicate this problem in an unmodified CRONUS database. The delay is much shorter, but it occurs in the same spot. I have sent a copy of the Client Monitor output to Microsoft for a response.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    As I said in my email, I was probably overlooking something. I will test this in the morning and let you know.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    UPDATECONTROLS does not appear to be the source of the problem. I ran the following test.

    1. Using an unmodified CRONUS (3.70B NA), I get the same 16 ms delay as when using my orginal CRONUS test database (4.02 modified).

    2. Removed the UPDATECONTROLS from the modified CRONUS database. No difference.

    3. Removed the UPDATECONTROLS from client database. No difference.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    Increasing the Caching – Record Set size from the default of 500 to 10,000 seems to have resolved the issue.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    This is Microsoft's latest response:

    _________

    I have just been informed that this specific issue has been reported by a partner in the UK. The response from development was that this is 'by design'. The issue seems to stem from SQL2005 caching query plans when it fills a screen and tries to re-use the query again. The problem is that SQL2005 ends up doing a table scan instead of using the keys. We are consulting with the SQL team to try to get you a workaround if possible.

    _____________
    There are no bugs - only undocumented features.
  • Waldo
    Waldo Member Posts: 3,412
    bbrown,

    what is the latest update regarding this issue?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • bbrown
    bbrown Member Posts: 3,268
    No solution at this point. This was escalated at Microsoft and they are working with us.
    There are no bugs - only undocumented features.
  • Waldo
    Waldo Member Posts: 3,412
    Bummer ... :cry:

    What is your opinion about the inpact of changing the value to 10000?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • bbrown
    bbrown Member Posts: 3,268
    In the long run, I don't think it resolved anything.
    There are no bugs - only undocumented features.
  • Sorcerer
    Sorcerer Member Posts: 107
    seems there is no change in sp3...

    the only one who could work normaly is the dbo. my user is set as the dbo and i have no perfomance issue when opening the forms hanging for the other users....
  • bbrown
    bbrown Member Posts: 3,268
    We see this issue as DBO, and even SA.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    Navision 4.02 SP2 (Build 23099)

    SQL Server 2005 SP1 (Enterprise)

    Hardware:

    CPU: 4 x 3.0 Ghz Dual-Core Xeon

    Memory: 24 GB

    Disk System:

    Controller 0 (Single Channel RAID)

    Drive C: (RAID 1 2x72 GB)
    O/S + Programs

    Drive D: (RAID 1 2x300 GB)
    Scratch space

    Controller 1 (4 Channel RAID)

    Drive E: (RAID 10 4x72 GB [channels 0+1])
    Base SQL databases (Master, MSDB, etc)
    Navision PRIMARY filegroup

    Drive F: (RAID 1 2x72 GB [channels 0+1])
    Drive G: (RAID 1 2x72 GB [channels 0+1])
    Navision Transaction Log (F:+G: )

    Drive H: (RAID 10 6x72 GB [channels 0+1])
    TempDB

    Controller 2 (4 Channel RAID)

    Drive I: (RAID 10 14x36 GB [channels 0+1])
    Navision "Data FIlegroup 1" filegroup -File 1

    Drive J: (RAID 10 14x36 GB [channels 3+4])
    Navision "Data FIlegroup 1" filegroup -File 2
    There are no bugs - only undocumented features.
  • Waldo
    Waldo Member Posts: 3,412
    Takes a whole room to be able to fit the hardware... 8-[

    Since I'm very interested in the effect of hardware on the performance of Dynamics NAV, I ask for you opinion:

    For controller 2:
    Don't you tink it can make a difference to split the two RAID10s to 4 RAID10s? RAID0 of 7 disks, or two times RAID0 of 3 and 4 disks can make a big difference in performance... . Small calculation: RAID0 of 7 disks makes performance 260% better then 1 disk. Splitted in RAID0 on 4 and 3 disks makes performance 390% better. I understand this will cost you an extra controler.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • bbrown
    bbrown Member Posts: 3,268
    Actually, this configuration fits in just 13U of rack space.

    Rule #1: There will never be a FAST ENOUGH computer

    Regardless of the configuration we settle on, we must get someone to pay for it. The goal is to work the balance between performance and cost. Your suggestion to add extra RAID sets would require not only an extra controller ($1300), but also 2 additional drive enclosures ($3000 each). This would be a better than 10% cost increase in the existing system, and since the number of physical drive remains the same, I would expect the performance increase to be minimal at best.

    Also spreading across uneven sized drives would waste space and is not recommended.
    There are no bugs - only undocumented features.
  • Waldo
    Waldo Member Posts: 3,412
    The extra controller I get, but not the 2 additional drive enclosures. :-k
    number of physical drive remains the same, I would expect the performance increase to be minimal at best
    I think not. Striping 2 disks = 100%performance gain (writing the data 2 times faster). Striping 3 disks ads an extra 50%=150%, ... . So, you can make the calculation for 7 disks... . I was wrong the first time: should be 254% and 333%. :oops:
    Also spreading across uneven sized drives would waste space and is not recommended.
    True.

    I was just asking for your opinion and I was definitally not saying this was a solution or a "must-do" for you. It is difficult to get people's opinions on things like this... . Thanks for yours.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • bbrown
    bbrown Member Posts: 3,268
    The extra controller I get, but not the 2 additional drive enclosures.

    The drive enclosures are dual bus 14 drive enclosures (2x7). The two existing enclosures are connected to the 4 channels of controller 2. If you add another controller then we must add extar enclosures and move drives to support it.
    I think not. Striping 2 disks = 100%performance gain (writing the data 2 times faster). Striping 3 disks ads an extra 50%=150%,

    These numbers refer to the physical drives being striped within a RAID set. In your suggested configuration you have not increased the number of drives. Adding the extra controller may result in a small increase but not 300%.
    There are no bugs - only undocumented features.
  • Waldo
    Waldo Member Posts: 3,412
    huh? :-k

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Sandra
    Sandra Member Posts: 52
    bbrown!

    Please be sure write when this phenomena is resolved. We have a similar or even the same problem with our G/L Entries. No changes made, but if someone in our accounting department wishes to see the G/L Entries they take up to about 20 min until the select is done. Not always just sometimes - so any news on this problem would be great.
    (we are also running 4.0 Sp 2 on SQL Server 2005).

    Regards
    Sandra

Categories