SQL 2005 Performance Issue

bbrownbbrown Posts: 3,097Member
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

  • 2tje2tje Posts: 80Member
    SQL?
    lots of flowfields in the list?
  • Alex_ChowAlex_Chow Posts: 5,063Member
    Can you tell us what version of Navision you're using? I know Navision 2.6 has some SQL performance issues.
  • bbrownbbrown Posts: 3,097Member
    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.
  • hedegaardhedegaard Posts: 25Member
    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
  • bbrownbbrown Posts: 3,097Member
    hedegaard,

    Thanks for the reply.

    No filters are set and the form is using primary key ("No.").
    There are no bugs - only undocumented features.
  • bbrownbbrown Posts: 3,097Member
    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.
  • hedegaardhedegaard Posts: 25Member
    I would use the client monitor and see what it going on.

    /Anders
  • bbrownbbrown Posts: 3,097Member
    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.
  • BlackTigerBlackTiger Posts: 1,227Member
    Show code from form's "OnGetRecord" and "OnGetCurrentRecord" triggers. Also remove all predefined filters and sorting orders from form.
    "You can’t just ask customers what they want and then try to give that to them.
    By the time you get it built, they’ll want something new.” Steve Jobs
  • frgustofrgusto Posts: 37Member
    I am experiencing similar behaviour in the same version.
    Did the MS rolldown solve the issue?
  • DenSterDenSter Posts: 8,130Member
    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.
  • bbrownbbrown Posts: 3,097Member
    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.
  • bbrownbbrown Posts: 3,097Member
    The cluster index is okay.
    There are no bugs - only undocumented features.
  • bbrownbbrown Posts: 3,097Member
    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.
  • BlackTigerBlackTiger Posts: 1,227Member
    Send me too, please. I want to see it, I'm unable to reproduce it. My mail in profile.
    "You can’t just ask customers what they want and then try to give that to them.
    By the time you get it built, they’ll want something new.” Steve Jobs
  • BlackTigerBlackTiger Posts: 1,227Member
    As I can see from your CM-log: this is not standard (clear W1) database, isn't it?

    You have "CurrForm.UPDATECONTROLS" with comment "//PR3.60"in OnAfterGetCurrRecord in form 38. This isn't standard functionality. By default this form is free from code.

    Try to reproduce it on STANDARD (W1) version of Navision.

    I still unable to reproduce your problem on my Navision (W1/LV).

    CurrForm.UPDATECONTROLS and CurrForm.UPDATE/CurrForm.UPDATE(TRUE) can cause alot of problems with performance on SQLServer bcos causing alot of data re-reads.
    "You can’t just ask customers what they want and then try to give that to them.
    By the time you get it built, they’ll want something new.” Steve Jobs
  • bbrownbbrown Posts: 3,097Member
    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.
  • bbrownbbrown Posts: 3,097Member
    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.
  • bbrownbbrown Posts: 3,097Member
    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.
  • bbrownbbrown Posts: 3,097Member
    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.
  • WaldoWaldo Posts: 3,412Member
    bbrown,

    what is the latest update regarding this issue?

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

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

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • bbrownbbrown Posts: 3,097Member
    In the long run, I don't think it resolved anything.
    There are no bugs - only undocumented features.
  • SorcererSorcerer GermanyPosts: 107Member
    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....
  • bbrownbbrown Posts: 3,097Member
    We see this issue as DBO, and even SA.
    There are no bugs - only undocumented features.
  • BlackTigerBlackTiger Posts: 1,227Member
    What is SQLServer version? 2000? Service Packs? 2005? 2005+SP1?

    What is server's hardware?

    I'm unable to reproduce it on Nav402+SQL2005SP1.
    "You can’t just ask customers what they want and then try to give that to them.
    By the time you get it built, they’ll want something new.” Steve Jobs
  • bbrownbbrown Posts: 3,097Member
    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.
  • WaldoWaldo Posts: 3,412Member
    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
  • bbrownbbrown Posts: 3,097Member
    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.
Sign In or Register to comment.