SQLIndex

Belias

Hi everyone, i tried to search mibuso and google, too for a post of stryk, about a little trick to reduce index size:
if i remember well, he told that for large tables sometimes he copy the field list of the key in nav in the SQLIndex field, too.
in this way he can cut out the trailing PK fields that are automatically attached to the secondary key.
I'd like to read the whole post, but i couldn't find it and i can't access his blog (they ask me userid and pwrd :? )
Can anyone suggest me about this property, or link me back to the original post?
Thanks in advance

*EDIT: found it: i searched google for "stryk sql index copy" (without "") and i found it...lucky shot, indeed...i couldn't expect to find it like this if anyone has something to add, then it's really welcome!

garak

That is his BLOG

stryk

Hi!

Please feel free to come up with further questions- if there are any - about this issue.

Best regards,
Jörg

Belias

stryk wrote:

Hi!

Please feel free to come up with further questions- if there are any - about this issue.

Best regards,
Jörg

well then, what is "bookmark lookup" (or "key lookup")?Is it dependent in some way to Clustered Index?
as far as i understood, it is the operation that sql does if you are executing a query using key (1,2,3) but you want to retrieve fields 1,2,3,4.
because of this, sql has to "lookup" the value of field 4.
Am i right?
this explanation would also explain "as with NAV there's nearly always a "Bookmark Lookup" (or "Keylookup") there is no problem." (quoted from here http://blogs.msdn.com/nav_developer/archive/2009/04/10/beware-the-sql-index-property-on-nav-5-0-sp1.aspx) because as all of you know, nav always do SELECT * and there almost never are all the fields of the table in the PK
Thanks in advance
P.S.: i am only doing experiments (on dev db), as i'm not a sql expert

stryk

The "Key Lookup" (or "Bookmark Lookup") is actually this:

Example:
SELECT * FROM "G_L Entry" WHERE "Account No_" = 'XYZ' ORDER BY "Account No_", "Posting Date"

So, in NAV there is a "Key" of "Account No.", "Posting Date" and a corresponding "Index" on SQL Server site ($1).
To retrive the result-set, the SQL Server will probably use this index $1 as this should enable it to look up the records optimally & quick.
The B-Tree of index $1 exists only of field-values of "Account No." and "Posting Date".

But NAV does not only query those two fields - the "*" asks for all fields - the whole table data!
This table data is only available in the "Leaf Node" level of the "Clustered Index" - with "G/L Entry" this is Key "Entry No." (index $0).
The B-Tree of this CI exists only of field-values of "Entry No.", but the "Leaf Nodes" of this index also include all other fields!

Thus, when the records are found via index $1, the SQL Server is forced to look up all other fields from the CI $0 - this operation is called "Key Lookup" or "Bookmark Lookup".

Hence, whenever data is queried by not using the "Clustered Index" itself - which is mostly the case in NAV - a Lookup is required.

To perform such a Lookup optimally, each "Non-Clustered Index" - for example our $1 - includes the Clustered Index information! Means, the Leaf Nodes of $1 include the "Entry No.". This info is added automatically - IT IS NOT NECESSARY to explicitly add "Entry No." to the NCI!
Unfortunately, NAV adds the PK fields usually to the NCI. As PK and CI are mostly (99.9%) the same, this information is redundant. In this case, "Entry No." is not only available in the Leaf Nodes (implicit) of $1 but also in the whole B-Tree (explicit). This could remarkable increase the index size, causing degraded performance.

When copying in NAV the "Key" content into the "SQL Index" property 1:1 (aka "Basic Streamlining") the index - e.g. $1 - is build without the added PK fields. Hence, the index gets smaller, performing better.
As long as the PK equals the CI this should work wihout any problems, as the CI is still implicit part of all NCI - e.g. $1 - Leaf Nodes.

So "Basic Steamlining" is a pretty much failsafe way to make indexes performing little better. All other fiddling with the "SQL Index" property - changing the order of fields (selectivity, etc.), removing fields is critically and could raise sever problems (as shown with NAV 5.0).

Hope this helps a little.

Best regards,
Jörg

Belias

Outstanding... =D> thanks a lot!but...only one thing to be sure that i understood correctly:

HYPOTHESIS:
I have a CI which is not PK(let's call it $1): this index is composed by fields a,b,c.
I have a NCI (let's call it $2) on the same table which is composed by fields e,f.

THESIS:
To have my $2 index perform optimally (for NAV queries), I should recreate it like this: e,f,a,b,c.
Because only clustered indexes have all the table data
!!!!!!NO!!!!! just reread your post: the index is already created by sql like e,f,INCLUDE(a,b,c) so i don't have to recreate $2 index, but leave it as "e,f"...Right?

Another thing: let's say that $0 is composed only by "z" field, the $2 index will be automatically created like this by nav, am i right?
e,f,z,INCLUDE(a,b,c)

Thank you so much for your time

*EDIT: i just test my post, and i noticed that there is no explicit inclusion (no included fields for the index). the clustered key fields are implicitly added to the leaf nodes of each NCI (as stryk said) anyway, I'm waiting for a confirmation, thanks in advance!

stryk

Just to avoid a misunderstanding: I was not talking about the INCLUDE feature of SQL Server ("Included Columns") - as this is not used by NAV.

Again, your example:
Key 0 (Non-Clustered, PK): "z"
Key 1 (Clustered, non-PK): "a,b,c"
Key 2 (Non-Clustered): "e,f"

Out of the box, NAV would create the indexes like this:
$0: z
$1: a,b,c, z
$2: e,f, z

For the NCI $2 the SQL Server would internally maintain the CI fields; thus $2 actually is designed like this:
$2: e,f, z (a,b,c)
> This should work optimally for filters on e, f, z and mostly a, b, c.

When copying the NAV "Key" a,b,c and e,f to "SQL Index" 1:1 the indexes will be like this:
$1: a,b,c
$2: e,f (a,b,c)
> $1 should perform optimally for filters on a, b and c.
> $2 should perform optimally for filters on e, f and mostly on a, b, c.

If you are filtering on z the indexes could perform sub-optimal; SQL Server would probably use a combination of $0 with $1 or $2.

IF $0 would be PK AND CI the "streamlined" Indexes would look like this:
$1: a,b,c (z)
$2: e,f (z)
Then ...
> $1 should perform optimally for filters on a, b, c and z.
> $2 should perform optimally for filters on e, f and z.

I hope this clarifies a little this somewhat complex issue ... :-k

Best regards,
Jörg

Belias

Yes, this definitively clears all for me: i've misunderstood you when you were talking about inclusion, but after some testing i understood that you were not talking about the feature of SQL #-o
Now everything is clear!great explanation, thanks!

stryk

You're welcome - I'm always glad if I could help :thumbsup:

kriki

[Topic moved from 'SQL General' forum to 'SQL Performance' forum]

NaviDeveloper_NL

stryk wrote:

To perform such a Lookup optimally, each "Non-Clustered Index" - for example our $1 - includes the Clustered Index information! Means, the Leaf Nodes of $1 include the "Entry No.". This info is added automatically - IT IS NOT NECESSARY to explicitly add "Entry No." to the NCI!
Unfortunately, NAV adds the PK fields usually to the NCI. As PK and CI are mostly (99.9%) the same, this information is redundant. In this case, "Entry No." is not only available in the Leaf Nodes (implicit) of $1 but also in the whole B-Tree (explicit). This could remarkable increase the index size, causing degraded performance.

When copying in NAV the "Key" content into the "SQL Index" property 1:1 (aka "Basic Streamlining") the index - e.g. $1 - is build without the added PK fields. Hence, the index gets smaller, performing better.
As long as the PK equals the CI this should work wihout any problems, as the CI is still implicit part of all NCI - e.g. $1 - Leaf Nodes.

If you use the property SQLIndex of a NAV key, the index on SQL Server will be defined as nonunique. By default NAV creates indexes as unique.
In the case of a nonunique nonclustered index the non-leaf level rows contain the nonclustered index key values, the child-page pointer, and the bookmark (clustered index) value.
This is because a bookmark must be unique by definition.
Because of this I do not see a reason to set the property SQLIndex equal to Key if the PK and clustered index are the same (as normally is the case).

The book 'SQL Server 2008 Internals' explains this in detail.
This can be checked by using DBCC IND and DBCC PAGE.

stryk

NaviDeveloper NL wrote:

If you use the property SQLIndex of a NAV key, the index on SQL Server will be defined as nonunique. By default NAV creates indexes as unique.
In the case of a nonunique nonclustered index the non-leaf level rows contain the nonclustered index key values, the child-page pointer, and the bookmark (clustered index) value.
This is because a bookmark must be unique by definition.
Because of this I do not see a reason to set the property SQLIndex equal to Key if the PK and clustered index are the same (as normally is the case).

Well, I guess this thread is somewhat confusing, but actually it as you said, but my conclusion is different:

- Every Non-Clustered Index contains a bookmark information at leaf node level.
- The bookmark info is actually the Clustered Index information.
- As the Clustered Index is usually based on the Primary Key - which grants uniqueness - the bookmark information is unique anyway.
- The bookmark data is ALWAYS unique - a must have to identify records - so if the CI is not unique by it self, SQL Server will internally maintain IDs to grant that uniqueness.
This also refers to Non-Clustered Indexes which are not flagged with UNIQUE - the Bookmark Data IS ALWAYS there and it is unique (the one way due to Primary Key, or the other by internal IDs).
In other terms: every Clustered Index IS unique from internal SQL perspective (again: this is a must); NAV further "grants" this uniqueness by creating the CI from the PK.

Hence, adding the PK field explicitly to an Index is pointless with Standard NAV.
The way how NAV is doing this has nothing to do with the internal bookmarking etc. (again: this is done automatically by SQL Server); it is just redundant.

So again: as long as the Primary Key is also the Clustered Index, the relevant unique bookmark info is automatically (from SQL Server) added to the Leaf Nodes of all NCI.

In such a case (and only in such a case!), the PK fields, which are explicitly added to the Non-Clustered Indexes by NAV are irrelevant. Hence -IMHO -, those basaically could be removed by "Basic Streamlining"; means copying from "Key" to "SQLIndex" 1:1.

But caution: there are other aspects which have to be regarded:
NAV adds the PK fields to the ORDER BY clause, we have Dynamic Cursors and C/SIDE might add filters on Key-Fields!
This all involves the risk, that if Indexes do not match the Standard NAV defaults it could result in performance issues.

So even though this "Basic Streamlining" is pretty failsafe for 90% of the NAV Indexes it is nowadays not worth the effort. Technically the indexes created by NAV are sub-optimal, but necessary for the NAV application.
IMHO it is better to leave the Standard NAV Indexes alone - at first - and only start fixing problems when they arise. In this case Re-Action is better than Pro-Action ...

P.S.: I think this is a little complicated issue and hard to discuss in this forum without giving examples ...

NaviDeveloper_NL

stryk wrote:

P.S.: I think this is a little complicated issue and hard to discuss in this forum without giving examples ...

That's true. I will give an example.
I use table G/L Entry (Dutch localisation).

Information about space used by index with ID 2 (name $1, columns defined by user G/L Account No.,Posting Date)

SELECT si.[name] AS iname
    , index_depth AS D
    , index_level AS L
    , record_count AS 'Count'
    , page_count AS PgCnt
    , avg_page_space_used_in_percent AS 'PgPercentFull'
    , min_record_size_in_bytes AS 'MinLen'
    , max_record_size_in_bytes AS 'MaxLen'
    , avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats
    (DB_ID ('DEMO_NAV_60')
    , OBJECT_ID ('CRONUS Nederland BV$G_L Entry')
    , NULL
    , NULL
    , 'DETAILED') AS ps
    INNER JOIN sys.indexes AS si
        ON ps.[object_id] = si.[object_id]
            AND ps.[index_id] = si.[index_id]
WHERE ps.[index_id] = 2;
go

iname  D    L    Count   PgCnt  PgPercentFull          MinLen      MaxLen      AvgLen
------ ---- ---- ------  ------ ---------------------- ----------- ----------- -------
$1     2    0    2806    10     90.1111934766494       24          24          24
$1     2    1    10      1      3.55819125277984       27          27          27

The index consists of two levels, a root page with 10 child pages.

After setting SQLIndex property to the same value as the Key value, the result of the query is the same.
So in this case, no space is saved by setting this property (as stated ealier in this post).

Also: Entry No. is in the leaf level of the index (in both cases).
And Entry No. is also in the non leaf-level in both cases (it would not be possible to navigate through the nonclustered index if this key was not unique).

stryk wrote:

Hence, adding the PK field explicitly to an Index is pointless with Standard NAV.

Otherwise it would not be possible to define all indexes as unique. Maybe they wanted the indexes to be defined as unique.

stryk wrote:

But caution: there are other aspects which have to be regarded:
NAV adds the PK fields to the ORDER BY clause, we have Dynamic Cursors and C/SIDE might add filters on Key-Fields!
This all involves the risk, that if Indexes do not match the Standard NAV defaults it could result in performance issues.

Another reason for not doing this "Basic Steamlining".

stryk

Exactly!

I have to admit that my investigations about this were several years ago with SQL Server 2000, so I ran another test like you did, getting the same result.

Means, the physical structures of a streamlined and a non-streamlined index are identical!
Hence, we're both right: Streamlining is pointless, still, it's not necessary to expicitly add the PK field - as the results are the same in any case.

With SQL Server 2000 we've seen that after streamlining the indexes performed better and the CPU load was decreased. Today, with SQL 2005/2008 I don't see this impact anymore; that's what I meant with "streamlining it's not worth the effort".

Now you gave evidence it is indeed pointless, so the advise to the NAV developers should probably be:
Don't fiddle with the SQLIndex property
(Except if you have to fix a specific index-problem, but then there are also better ways to optimize)

Cheers,
Jörg

Belias

phew...i did it on a sql 2000...at least my work is not wasted :whistle:
and yes, the streamlining is just a part (the last part) of the optimization we've done...
anyway, thanks both NaviDeveloper NL and stryk for sharing your knowledge :thumbsup:

stryk

Yeah, all this Indexing and stuff is really tricky ... many "best practices" (?) were born in times where is was more "experimental experience" than fact knowledge, as neither NAV nor SQL Server were capable to provide the relevant info properly ...
(Some would say it was more "alchemy" than "science" )
Since we have all these DMV we have much deeper insight into those SQL structures, learning to do it better, so based on improved knowledge, we need to change these "best practices" - that's the challenge we have to face every day

And exchanging these best practices, experiences and opinions is actually the reason why we meet here at MIBUSO, isn't it 8)

sc00by

This thread leaves me thinking the SQLIndex property is maybe more akin to legacy issues from a while back and should not be used due to the potential problems its use may bring with it ...

I get this and totally understand the thinking and examples given ... however ...

I attended a SQL Perform workshop a while back - "Optimizing Microsoft Dynamics Nav on SQL Server" where Hynek specifically was encouraging the use of this property in reducing the number of indexes required at SQL level and optimizing the index choice based on SQL performance rather than what Navision required to filter a table.

Are we saying that things have moved on or this only applies to certain versions?

Just seems like two opposing views from equally competant and experienced individuals.

Thoughts?

Rob.

stryk

The "SQL Index" property was introduced with NAV 4 to give developers a chance to optimize the "translation" from NAV "Keys" to SQL "Indexes" - out of the box this is actually sub-optimal, e.g. the native C/SIDE Server has opposite requirements than SQL Server.

Hence, the "SQL Index" property could be used to streamline Indexes, providing better performance. This was working as NAV was using "Fast Forward Cursors".

Since MS has changed to "Dynamic Cursors" this does not work anymore. Overruling the standard Key/Index translation could result in degraded performance - this has been discussed here.

So it's not a matter of opinions, it's a matter of fact - a fact generated by MS. MS is officially advising to not use the SQL Index property anymore - just refer to the team Blogs about details.
All "SQLIndexes" introduced with NAV 5 have been completely removed with NAV 5 SP1; the property is not used anymore.

sc00by

stryk wrote:

All "SQLIndexes" introduced with NAV 5 have been completely removed with NAV 5 SP1; the property is not used anymore.

So for anybody still on 4, is it still worth looking at then?

stryk

Not really. Since a certain build number (I don't recall the details) also NAV 4 changed from Fast Forward to Dynamic. Thus, all recent NAV versions - from 4 to 6 - are using Dynamic Cursors ...