MaintainSQLIndex

Remco
Remco Member Posts: 81
edited 2008-01-24 in SQL Performance
Hi,

I'm working on a performance issue. I'm looking at the MaintainSQLIndex property. If the key is only used for sorting and not indexing I can disable this property to gain performance. Is with sorting meant that the key is used to sort reports and forms. What if this key is used in a SETCURRENTKEY. Can I still disable the MaintainSQLIndex?

O:)

Comments

  • DenSter
    DenSter Member Posts: 8,307
    SETCURRENTKEY translates to an ORDER BY clause in the SQL statement. SQL Server then decides which index to use, and it is not always the one with the same fields. For that decision, the WHERE clause is more important to SQL Server, which are the filters in the SQL statement.

    It's more complicated when you have update 6 installed and left the index hinting on, in which case it DOES become important to have the right SETCURRENTKEY, and to not just disable SQLIndexes.

    You can safely disable MaintainSQLIndex without breaking the application, but you will need to monitor the system to make sure you get the expected results.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Remco
    Remco Member Posts: 81
    So, if there is a filter on every field within the key, SQL will automatically choose that specific key. I have a key in Navision in table 21 Cust. Ledger Entry of "Document No.","Document Type","Customer No.". I I disable the field MaintainSQLIndex. In the code I set a filter on all 3 fields of this key, SQL chooses the key "Document No.","Document Type","Customer No.".
    If I filter on one or two field the possibility is there that SQL chooses another key?
  • DenSter
    DenSter Member Posts: 8,307
    If you uncheck MaintainSQLIndex, there is no index on SQL Server to choose from, so setting fitlers on those fields cannot possibly result in SQL Server using that index, because it doesn't exist there, and it will find another index to use, or will resort to a full table scan.

    SQL Server has a mechanism that determines how 'expensive' using certain indexes is, and it will select the 'correct' index accordingly. Even though you have that index, and you set filters on all three fields, it might select yet another index that starts with Customer number for instance. It depends on the query.

    The only way to really force SQL Server to use a certain index is to use index hints, which you should be able to find on this forum. Don't use it as a standard though, SQL Server in general is quite good at deciding which index to use.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • kriki
    kriki 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!

  • Waldo
    Waldo Member Posts: 3,412
    May be a few tips.
    1.
    You can use some DM views on SQL 2005 to see whether indexes are used or not. If indexes are never use on SQL Server, you can disable them by MaintainSQLIndex.

    2.
    You can disable similar keys... . E.g. If you have two keys like:
    - Item No, Location Code, SomeField
    - Item No, Location Code, SomeOtherField
    you could choose for disabling one of the two as maintaining both is a bit overkill... .

    Keep in mind that when indexhinting is turned on by default, using the MaintainSQLIndex for disabling indexes is dangerous!

    And like Denster already putted: "you will need to monitor the system to make sure you get the expected results."

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Waldo
    Waldo Member Posts: 3,412
    By the way, I mentioned some of the interesting dmv's in this article.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • NaviDeveloper_NL
    NaviDeveloper_NL Member Posts: 42
    Waldo wrote:
    Keep in mind that when indexhinting is turned on by default, using the MaintainSQLIndex for disabling indexes is dangerous!"

    Why is this dangerous?
  • Waldo
    Waldo Member Posts: 3,412
    Indexhinting by default means for every SELECT, NAV is going to pick the index. i.e. the index that you put in "SETCURRENTKEY" ... just the one you're sorting on.

    If that key does not exist ... NAV is going to hint the clustered key (if I'm not mistaken). That is not always a good thing.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • garak
    garak Member Posts: 3,263
    waldos right.
    I've also tested the indexhintig = Yes.
    And for our application, its an bad adjustment (tested with Profiler and show the Plans).
    In my meaning, indexhinting will only use when starts an query from an form or report. If you make setcurentkey from an Codeunit, the SQL Server will not use this index. Thats right or im wrong ?

    Also, if u use HF 6 read this article
    http://www.mibuso.com/forum/viewtopic.php?t=20831
    http://www.mibuso.com/forum/viewtopic.p ... highlight=
    Do you make it right, it works too!
  • Waldo
    Waldo Member Posts: 3,412
    I thought NAV also hints from codeunits ... .
    I did some tests in one of my blogs... : http://dynamicsuser.net/blogs/waldo/archive/2007/09/19/indexhint-in-4-0-sp3-update-6-review-amp-suggestion.aspx

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog

Categories