SQLIndex property

In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance. In this blog I

describe what to be careful about, and why the use of this property has been removed in the NAV 5 SP1 application.

The property is still available and it is still a valuable tool in performance tuning. But from SP1, it is not used anymore

in the standard application. The document "Changes in NAV 5 0 SP1.doc" on the SP1 product CD lists the 72 tables where the

SQLIndex has been removed (Change A222).

Background:
The key-property SQLIndex was introduced in NAV version 4 SP1. The idea of the property is to make it possible to change

the index on the SQL Server while maintaining the same application (same sorting) in NAV.

The main use of the property is to make the SQL index more selective. In the NAV application there are many keys that begin

with an option which is not very selective, for example the primary key "Document Type","No." on the Sales Header table.

"Document Type" - having only 6 possible options - is not very selective, and SQL Server might choose not to use it. If the

index was changed to be the other way around ("No.","Document Type"), it would be much more selective and more effecient

for SQL Server to use in SELECT statements.

The other benefit of this property is to enable "covering indexes", so that you can have a few indexes to cover for most

searches, and then disable the maintenance of other indexes. For example an index on the "Sales Header" table beginning

with "No." can be used effeciently with many different filters, reducing the need to have one key for every possible exact filter.

Problems:
When you have a query which includes an "ORDER BY"-clause, SQL Server has to return the reords in the order specified by

that clause. If SQL Server doesn't have a matching index, it has to retrieve data using a different index and then do some

internal sorting to return data in the correct order. If there are no good indexes, then SQL Server may choose to

use the clustered index which can be bad enough. But when the query also has an index hint, then SQL Server is forced to use the

index specified by the hint, and this can lead to large amounts of reads.

Example:
In a recent support case, the customer had generally bad performance. In this case, the "SELECT TOP 30"-query from the post "Simple query to check the recent performance history" showed that out of the top 30 "worst" queries, 26 were similar to this one

SELECT TOP 501 * FROM "Reservation Entry" WITH (UPDLOCK, INDEX("$1"))   WHERE (("Source ID"=@P1)) AND (("Source Ref_

No_"=@P2)) AND (("Source Type"=@P3)) AND (("Source Subtype"=@P4)) AND (("Source Batch Name"=@P5)) AND (("Source Prod_ Order

Line"=@P6)) AND (("Reservation Status"=@P7)) ORDER BY "Source ID","Source Ref_ No_","Source Type","Source Subtype","Source

Batch Name","Source Prod_ Order Line","Reservation Status","Shipment Date","Expected Receipt Date","Entry No_","Positive"

The query itself looks good enough: WHERE-clause and "ORDER BY"-clause match each other, and there were no immediate

reasons why this query should cause more reads than the number of records in the table. But in the standard application, the SQLIndex property for this key was:

"Source ID","Entry No.",Positive

The idea with this is SQLIndex is to have an index which can cover more situations, and in that way reduce the number of indexes that

need to be maintained on SQLServer. And the SQLindex is fine for the SELECT-part of the query. The problem is, that the

index can't be used for the "ORDER BY"-part of the query.

So what happens, is:

SQL Server may have planned to use the clustered index to read all data and then do some internal sorting. But in this

case, the Index Hint forces SQL Server away from that plan. The result is that SQL Server is forced into doing a very

difficult task while being restricted by the index speicifed by the index hint.

In this case we designed the "Reservation Entry"-table and removed the SQLIndex property from the key, and performance went

up.

Conclusion:
When you see a query which causes many reads, even if SQL Server has a good index, then also consider if the index is

good for the "ORDER BY"-part of the query. The "ORDER BY"-part of the query depends on the key in NAV. But if the SQLIndex

property has been set for this key, then by definition, the "ORDER BY" and the SQL index will not be matching.

You should still consider the use of the SQLIndex property as part of tuning performance of a system. But just be aware that it can also cause problems as described here.

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.