MaintainSQLIndex

Remco

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:)

DenSter

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.

Remco

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

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.

kriki

[Topic moved from Navision forum to SQL Performance forum]

Waldo

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."

Waldo

By the way, I mentioned some of the interesting dmv's in this article.

NaviDeveloper_NL

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

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.

garak

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=

Waldo

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