mibuso.com

Microsoft Business Solutions online community
It is currently Sat Aug 23, 2014 5:34 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 20 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: [Good Posting!] SQLIndex
PostPosted: Tue Aug 11, 2009 6:54 pm 
Offline

Joined: Mon Dec 11, 2006 10:34 am
Posts: 2883
Location: Bergamo
Country: Italy (it)
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 :mrgreen: if anyone has something to add, then it's really welcome!


Last edited by Belias on Wed Aug 12, 2009 5:38 pm, edited 2 times in total.

Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Tue Aug 11, 2009 8:04 pm 
Offline

Joined: Mon Mar 08, 2004 2:42 pm
Posts: 3255
Location: Hannover
Country: Germany (de)
That is his BLOG

_________________
Do you make it right, it works too!


Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Tue Aug 11, 2009 8:35 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
Hi! :wink:

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

Best regards,
Jörg

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: SQLIndex
PostPosted: Wed Aug 12, 2009 8:35 am 
Offline

Joined: Mon Dec 11, 2006 10:34 am
Posts: 2883
Location: Bergamo
Country: Italy (it)
stryk wrote:
Hi! :wink:

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


Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Wed Aug 12, 2009 9:56 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: SQLIndex
PostPosted: Wed Aug 12, 2009 10:26 am 
Offline

Joined: Mon Dec 11, 2006 10:34 am
Posts: 2883
Location: Bergamo
Country: Italy (it)
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!


Last edited by Belias on Thu Aug 20, 2009 9:26 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Wed Aug 12, 2009 5:20 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: SQLIndex
PostPosted: Wed Aug 12, 2009 5:37 pm 
Offline

Joined: Mon Dec 11, 2006 10:34 am
Posts: 2883
Location: Bergamo
Country: Italy (it)
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!


Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Wed Aug 12, 2009 6:31 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
You're welcome - I'm always glad if I could help :thumbsup:

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: SQLIndex
PostPosted: Mon Aug 17, 2009 2:52 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7686
Location: Milan
Country: Italy (it)
[Topic moved from 'SQL General' forum to 'SQL Performance' forum]

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Thu May 05, 2011 7:44 pm 
Offline

Joined: Fri May 06, 2005 3:26 pm
Posts: 37
Location: Netherlands
Country: Netherlands (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.


Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Thu May 05, 2011 8:16 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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 ...

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: SQLIndex
PostPosted: Thu May 05, 2011 11:49 pm 
Offline

Joined: Fri May 06, 2005 3:26 pm
Posts: 37
Location: Netherlands
Country: Netherlands (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)

Code: Select all
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


Code: Select all
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".


Top
 Profile  
 
 Post subject: Re: SQLIndex
PostPosted: Fri May 06, 2011 7:47 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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. :wink:

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 :mrgreen:
(Except if you have to fix a specific index-problem, but then there are also better ways to optimize)

Cheers,
Jörg

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: SQLIndex
PostPosted: Fri May 06, 2011 8:53 am 
Offline

Joined: Mon Dec 11, 2006 10:34 am
Posts: 2883
Location: Bergamo
Country: Italy (it)
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:

_________________
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 20 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to:  
cron