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.

Comments

• Anonymous
  April 22, 2008
  Thank you for this advice. I also recommend to be careful with changing the "SQL Index" too much from the "Key" setting as the difference between the Index and the Sorting could be a problem. Anyway, I strongly recommend to USE the "SQL Index" property, by simply copying the content from "Key". This - I call it "basic streamlining" - creates the index with the same order of fields, but not UNIQUE and without the dispensable Primary Key fields which are normally added, hence the index gets remarkably smaller, performing better! (I don't know if this is still applicable with NAV 5.0 SP1) Then, in specific cases it might be feasible to do further tuning, e.g. removing more fields or changing the order by selectivity, but as you say: if done wrong, this could also harm. Best regards, Jörg

• Anonymous
  April 22, 2008
  Hi Jörg, Thanks for sharing your experiences here! Interesting point about "basic streamlining", and I don't see any reasons why this will be different in SP1. Any more comments, keep them coming, Lars

• Anonymous
  April 22, 2008
  Hi, We can change SQL query generated for table by using hints or query plans. Can we do the same for queries relating SIFT tables, or SIFT views ? How with(NOEXPAND)query hint can be removed from the query reading from SIFT view ? For SQL Standard Edition this is good, because forces Query Optimizer to use clustered index created on SIFT view, but what with SQL Enterprise Edition ?  Will Query Optimizer use any of extra indexes defined on SIFT views when with(NOEXPAND) hint is present in the SQL query ? Regards, Slawek

• Anonymous
  April 22, 2008
  Hi, It is not possible to control the SIFT queries (SELECT SUM) from Nav. The methods to add index hints only work for normal queries. But of course any SQL methods, like plan guides will work on any query. The reason for using NOEXPAND is, that otherwise Indexed Views are only supported on SQL Enterprise edition. Quote from this link( http://msdn2.microsoft.com/en-us/library/ms181151.aspx ): "In SQL Server 2005 Enterprise Edition, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used." Hope that explains? Best regards Lars

• Anonymous
  April 23, 2008
  Hi Lars, Thanks, that explains a lot. One more question is - I know that NOEXPAND hint forces query optimizer to use clustered index on indexed view. But when we are using Enterprise version does the NOEXPAND hint prevents to use any other than clustered index created on view ? My guess is yes, so in order to use other indexes I need to create plan guide to remove NOEXPAND hint from SELECT SUM query, and I have to create one plan for every indexed view if I want to force query optimizer to select and pick the best index Regards, Slawek.

• Anonymous
  April 23, 2008
  Hi Slawek, Yes, you can say we have to use lowest common denominator, rather than generating different queries depending on the SQL edition. So removing NOEXPAND would have to be done from the SQL side. Just out of curiosity, do you know how big an effect NOEXPAND has? Is removing it something that can create dramatic performance increases? Best regards Lars

• Anonymous
  May 06, 2008
  Hi Lars, I not agree that SQLindex property is a problem. By my opinion problem is index hinting - WITH (UPDLOCK, INDEX("$1")). SQLindex propery allowed NAV pragramers create better index than NAV index - with hight selectivity. If WITH (UPDLOCK, INDEX("$1")) did not in this exapmle, SQL server use better index. best regards, Jan Stepanek

• Anonymous
  June 04, 2008
  Is the problem really using hints in conjunction with SQLindex - have you tried SQLindex without hints on SQL Server 2005? It appears we need different tuning tips for each SQL Server release.

• Anonymous
  June 10, 2008
  Yes, the cases that I have seen, had default index hinting on. In theory, the problem could exist also without Index Hinting, since by definition you get an ORDER BY without a matching index. But that's the nature of the SQLIndex property anyway. And, of course default index hinting is not used in 5 SP1 any longer. So my advice would be to keep using the SQLIndex property as before. But just be aware that if used in combination with index hints, it can cause problems.