Use of index hint

(0) Share

Report

Posted on by @rp@n

Dear All,

In earlier version AX 2009 i am using index hint with select statement. But in AX 2012 R3 i didn't see index hint with select statement. It's depreciated from AX 2012 R3?

Please give me more shed on this.

Thanks!

Arpan Sen

*This post is locked for comments

I have the same question (0)

All responses (21)

Answers (0)

Suggested answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

It still exists in X++ (even in AX 7), but it's ignored.

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Thanks Martin,

I go through the link. It's not clear for me why it's ignore actually in MS Dynamics AX 2012 version onwards.

Could you please give me some point's.

Thanks!

Arpan Sen

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Thanks Crispin,

That means in query suppose default Surrogate key is there then it will use index as hint with select statement for this query.

If i mentioned any index like StudentID then it will use index as hint for this query. Right?

Please confirm.

Thanks!

Arpan Sen

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

No. Index hint is completely ignored like Martin said. SQL Server decides in the Query optimization engine what to use.

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Thanks Vilmos,

But now Index hint is completely ignored right? Then what index will work as hint in AX 2012 onwards in place of index hint.

Thanks!

Arpan Sen

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Arpan,

Martin and Vilmos stated already that index himt is not used. SQL server determines which index will be chosen based on optimizations and statistics. You can actually monitor SQL to see the query plans used.

In the past index hint was often used to define a sort order. While having a sort order and filter on other fields which are not part of the index would decrease the performance. So now you only have to provide the order and think about a good index. SQL server can do selection of records and return them in the correct order. You can also monitor missing indexes in SQL for index suggestions to improve the performance.

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Thanks Andre,

I understood that index hint is not use more in AX 2012 onward. As you said that now SQL Server determines which index will be used for better performance.

For example :

Suppose i am creating a new table "Test" and add 10 columns.

And add Primary index in to this "StudentIdIdx". Then how SQL will identify which index will use for this table? when i use Test table in select statement. Because i already declare index "StudentIdIdx" in to "Test table". SQL will consider "StudentIdIdx". If no index then default index "Surrogate key" will use for Test table

Please give me more shed on this.

Arpan Sen

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

SQL Server query optimizer use indexes that are useful for the given query, therefore it depends which columns you use for joining, grouping, filtering and/or sorting.

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Thanks Martin,

As above table if i mentioned index "StudentIdIdx" index then SQL Server Query Optimizer will use "StudentIdIdx" with select statement.

Else

SQL Server Query Optimizer will use default index "Surrogate key"with Select statement right?

Thanks!

Arpan Sen

Was this reply helpful? Yes No