SQLIndex property

re: SQLIndex property

stryk — Tue, 22 Apr 2008 15:40:23 GMT

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

re: SQLIndex property

Lohndorf — Tue, 22 Apr 2008 17:40:59 GMT

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

re: SQLIndex property

sguzek — Wed, 23 Apr 2008 00:52:26 GMT

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

re: SQLIndex property

Lohndorf — Wed, 23 Apr 2008 09:46:40 GMT

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

re: SQLIndex property

sguzek — Wed, 23 Apr 2008 15:30:32 GMT

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.

re: SQLIndex property

Lohndorf — Wed, 23 Apr 2008 16:11:28 GMT

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

re: SQLIndex property

Jan Stepanek — Tue, 06 May 2008 19:11:14 GMT

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

re: SQLIndex property

davmac1 — Wed, 04 Jun 2008 18:27:40 GMT

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.

re: SQLIndex property

Lohndorf — Wed, 11 Jun 2008 10:58:55 GMT

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.