SQL 2005 Performance Issue

245678

Comments

  • Waldo
    Waldo Member Posts: 3,412
    Small question: installed SP1 for SQL Server 2005?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Sorcerer
    Sorcerer Member Posts: 107
    small answer: yes even with sp1 for sql server 2005 this failure will occur.

    there is no hotfix available to solve this sql server problem at this time. you only have the choice between waiting or go down to sql server 2000.
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I am currently testing a private hotfix with waldo and bbrown. [-o<

    We'll keep you updated on the results...
    Marije Brummel | Freelance Business Central Specialist
  • Waldo
    Waldo Member Posts: 3,412
    know that I'm praying with you, my friend...
    [-o< [-o< [-o<

    Euhm ... Sorcerer = Sandra?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Sorcerer
    Sorcerer Member Posts: 107
    not really, germany has more than one user in this forum :D

    no, just another user sick of this bug going straight back to sql 2000
  • Sandra
    Sandra Member Posts: 52
    sorcerer!

    Would it be possible to down-size to SQL 2000. We have a 64 Bit Machine for the SQL Server. Is there a SQL 2000 server that fits?

    Regards
    Sandra
  • Waldo
    Waldo Member Posts: 3,412
    Like Mark said, we'll let you know...

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Sorcerer
    Sorcerer Member Posts: 107
    Sandra! (why ! ?)

    yes, we have a 64bit machine too.

    if you have itanium 2 processors you could use sql server 2000 (64 bit)

    otherwise you could use sql 2000 (32 bit) as WOW64 application. (as we will do)

    for detailled information send me a pm or mail!
  • Nobody
    Nobody Member Posts: 93
    This was a bug with SQL 2005 and has been fixed and the hotfix has been released under KB article number 926285. The article is not published yet but they can send the fix out to you.

    My 2 cents. I do a lot of SQL/NAV perf tuning and SQL 2005 is leaps and bounds better than SQL 2000 the query optimizer makes much better decisions and much cleaner query plans. It also makes much better use of statistics.
  • Sorcerer
    Sorcerer Member Posts: 107
    is there any description of the hotfix available?
  • Nobody
    Nobody Member Posts: 93
    The hotfix is complete but the KB article is not. The best description I can give you is "Problem with FFO cursors and execution plan caching".
  • Sandra
    Sandra Member Posts: 52
    Has anyone of you tried this patch and had real success with it. It seemed fine for 2 days in a run, but this morning we had another call from a user who waited 30 minutes for the G/L Entries to finally come up. This is not really satisfying. Has anyone been more successful with this hotfix?
  • Waldo
    Waldo Member Posts: 3,412
    Not tried yet.

    Our problems were mostly solved by playing around with the sql index, sift levels, MaintainSQLIndex, ... properties. I was a bit surprised with the impact.

    Because of this, we (Mark Brummel and me :wink: ) postponed installing the patch temporarily ... . Sorry to hear it didn't have much effect ...

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Sorcerer
    Sorcerer Member Posts: 107
    seems a little bit to me that you have not really ambitions to solve this problem. either you haven't any customers with this problem or this customers have good nerves...

    after no new impressions regarding to this problem from convergence, we will try this hotfix on this weekend.

    it makes me angry to see no reaction from microsoft but to leave their customers in the lurch and advertise their new deluxe support...
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    So where were you when we were discussing this with Microsoft at the Pavilion??

    We have actualy discussed this.
    Marije Brummel | Freelance Business Central Specialist
  • Sorcerer
    Sorcerer Member Posts: 107
    what time did you discuss this topic?
    would have been nice if those things would be announced...

    where there any solvings? haven't heard about them.
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I do not exactly remember when this was, I was on and off duty on the Pavilion during Convergence.

    Please let me find it out, and come back on this. It will take some days or maybe longer because of the many people involved.

    As for the issues Eric had, we focused on their biggest pain process and found out that we could solve them by using the normal SQL-Perform methodology.
    Marije Brummel | Freelance Business Central Specialist
  • Sandra
    Sandra Member Posts: 52
    is microsoft still working on the problem, or is it resolved for them? We are still having huge delays in the system. We updated on Nov. 1st and now we are doing a regular "update statistics". Things have got a bit better but we are definitly not satisfied. We still have waiting periods up to 15 min in the G/L Entries.
  • bbrown
    bbrown Member Posts: 3,268
    I have had an escalated support incident with Microsoft on this issue for some time. At this point, Microsoft is claiming they cannot duplicate the issue and have pushed it back to us.

    Our client has delayed their plans to go live on SQL.
    There are no bugs - only undocumented features.
  • Waldo
    Waldo Member Posts: 3,412
    Sorcerer wrote:
    seems a little bit to me that you have not really ambitions to solve this problem. either you haven't any customers with this problem or this customers have good nerves...

    Like Mark said, the performance issue at our customer was resolved. I can't reproduce the delays anymore, so how could I know if the hotfix would work? So ... enough ambitions , but too few opportunities ... .

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Sorcerer
    Sorcerer Member Posts: 107
    @mark and waldo: thanks for your response.
    @nobody: is this the right kb number? the article says something about xml? http://support.microsoft.com/?kbid=926285
  • Sandra
    Sandra Member Posts: 52
    Hi Mark,

    I admit I am not very firm with SQL. Maybe you could be a little more specific on the "normal SQL-Perform methodology". We are now running update statistics a couple of times a day which seems to help a little but not alway right away. It would be great if you could detail it just a bit.

    Thanks
    Sandra
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Sandra,

    SQL tuning is a very specific business. Every tuning is different depening on the customers needs and usage of Navision.

    I work together with www.sqlperform.com. We do on-site SQL tuning and workshops.

    We also provide a number of tools that helps you keep your systems run smoothly.

    Mark

    @Luc van Dyck: Sorry for the advertising...
    Marije Brummel | Freelance Business Central Specialist
  • Waldo
    Waldo Member Posts: 3,412
    Mark Brummel wrote:
    @Luc van Dyck: Sorry for the advertising...
    That's something I never do ... :wink:



    We're performing a SQL Tuning as we speak ... I'll let you know how it goes.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Sorcerer
    Sorcerer Member Posts: 107
    has anyone new information about this issue?
    we have opened another call by ms, no result yet.
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    Guys, I have an idea. The basic problem is that wasting weeks of unchargeable work at each project - because acceptable performance is not something customers would consider an "extra" - is not feasible.

    Therefore, what if we, in a bold move, turn off MaintainSQLIndex and MaintianSIFTIndex on every Entry table on every key, except for the primary one?

    I'm testing it on SQL2005. I've written a function I'm generating tens of thousands of Item Journal lines with, and then post them (with automatic cost posting so G/L entries are also created) with these above mentioned two fields turned off for every entry table. I can comfirm that the SIFT tables actually disappeared from SQL so the turning off worked.

    So far the results are strange. On one hand, reading data like f.e. Inventory flowfield on Item Card is NOT got any slower. On the other hand, posting the journals did NOT get any faster. It's weird.
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    edited 2006-11-21
    :shock:

    It is not weird. If you want to tune SQL you need to know how SQL thinks.

    Maybe http://www.sqlskills.com/ is a good place for you to start...
    Marije Brummel | Freelance Business Central Specialist
  • Waldo
    Waldo Member Posts: 3,412
    Did you use the benchmark toolkit?

    You should see some differences in the profiler...

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    Jesus, no, I don't want to "tune" SQL. In my vocabulary "tuning" means making something "even better performing, usually for special purposes", not making something "at least not scandalously bad for a very common application that should work out of the box" . I think we have to arrive to a general checklist that says turn this, this, and this off, run that, that, and that every day/week/whatever, because it's a huge waste of productive days.

    So, I just want to find a generalized solution that can be done quickly on each project and be done with it. I've checked the downloadable slides of Convergence and if I understood correctly, the guys said that these index-related and SIFT-related things are even more important than FINDSET. So I think turning them all off could help. (And later on turn on some other special ones.) But it does completely nothing. And now I totally don't understand what's going on. If there are no SIFT and indexes, then all the cost is just reading the writing the normal tables - that can't be so slow?
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    In basics you are right, but there is no general rule when it comes to SQL tuning and even posting this on a forum is very dangerous.

    We do not want everyone to think they can tune SQL because they can't. SQL is very very different when it comes to indexing from Navision and you really need to understand how it works in order to make proper changes.

    Tuning SQL is not something Navision specific, every SQL system needs tuning and mainentance.

    Please spend some time on www.sqlskills.com

    It is a website by Kimberly Tripp. She is very good in explaining how SQL works and willing to help. I met her last week in Barcelona.

    The slides you saw at Convergence were from Hynek Muhlbacher. His company www.sqlperform.com is specialised in tuning SQL for Navision. He also gives workshops and training.

    I would encourage you to enrole in one of the workshops. There is one next week in Scotland. We are also planning them in The Netherlands and Belgium but they will most likely be given in Dutch. (By me. :wink: )
    Marije Brummel | Freelance Business Central Specialist

Categories