Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

non clustered index maintenance

10 posts in Performance and Tuning Last posting was on 2012-12-19 17:44:38.0Z
vtpcnk Posted on 2012-12-11 12:08:31.0Z
Sender: d42.50c7219a.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: non clustered index maintenance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50c7223f.d5e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Dec 2012 04:08:31 -0800
X-Trace: forums-1-dub 1355227711 172.20.134.41 (11 Dec 2012 04:08:31 -0800)
X-Original-Trace: 11 Dec 2012 04:08:31 -0800, 172.20.134.41
Lines: 6
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13309
Article PK: 1158223

do non clustered indexes have to be periodically dropped and
recreated to keep them optimal (with regards performance) -
to prevent performance degradation due to
deletes/inserts/updates on the table.

appreciate the feedback.


Rob V Posted on 2012-12-11 21:06:48.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
References: <50c7223f.d5e.1681692777@sybase.com>
In-Reply-To: <50c7223f.d5e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50c7a068$1@forums-1-dub>
Date: 11 Dec 2012 13:06:48 -0800
X-Trace: forums-1-dub 1355260008 172.20.134.152 (11 Dec 2012 13:06:48 -0800)
X-Original-Trace: 11 Dec 2012 13:06:48 -0800, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13310
Article PK: 1158237


On 11-Dec-2012 13:08, vtpcnk wrote:
> do non clustered indexes have to be periodically dropped and
> recreated to keep them optimal (with regards performance) -
> to prevent performance degradation due to
> deletes/inserts/updates on the table.
>
> appreciate the feedback.
>

They may, they may not. it depends on what sort of operations happen: a
lot of deletes and inserts on increasing key values may cause more
fragmentation. You can use the derived_stat() function to look at the
index page cluster ratio (ipcr) and use that as rough guidance. But
there is no guarantee that rebuilding an index will make any difference
for the speed of your queries: you should really test this and if there
are no clear gains, best spend your attention on other things.

Instead of dropping and recreating you can also use "reorg rebuild
table-name index-name" or, in 15.7 ESD#2, "reorg rebuild table-name with
online".


HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


vtpcnk Posted on 2012-12-12 01:16:40.0Z
Sender: 32a9.50c7d4e6.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50c7daf8.33fb.1681692777@sybase.com>
References: <50c7a068$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Dec 2012 17:16:40 -0800
X-Trace: forums-1-dub 1355275000 172.20.134.41 (11 Dec 2012 17:16:40 -0800)
X-Original-Trace: 11 Dec 2012 17:16:40 -0800, 172.20.134.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13311
Article PK: 1158238

rob, thanks for the response.

it seems to me that non clustered indexes are by themselves
sorted according to key and in some ways index maintanance
seems similar to the way clustered indexes on a apl table
are maintained (here i am only talking about the non
clustered index pages and not the related table data).

the index contains sorted data and has to be maintained to
keep it that way.

if this is so, will not non clustered indexes suffer from
the same issues that cause performance issues with a
clustered index on a apl table - page splits, rows deleted,
page forwarding? again here i am only talking about the non
clustered index pages and not the related table data.

btw for deletes, the manual says that the corresponding
entry in the non-clustered index will be deleted. is this a
physical delete or 'marked for deletion'?

appreciate the insights.


Rob V Posted on 2012-12-12 10:49:23.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
References: <50c7a068$1@forums-1-dub> <50c7daf8.33fb.1681692777@sybase.com>
In-Reply-To: <50c7daf8.33fb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50c86133@forums-1-dub>
Date: 12 Dec 2012 02:49:23 -0800
X-Trace: forums-1-dub 1355309363 172.20.134.152 (12 Dec 2012 02:49:23 -0800)
X-Original-Trace: 12 Dec 2012 02:49:23 -0800, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13313
Article PK: 1158242


On 12-Dec-2012 02:16, vtpcnk wrote:
> rob, thanks for the response.
>
> it seems to me that non clustered indexes are by themselves
> sorted according to key and in some ways index maintanance
> seems similar to the way clustered indexes on a apl table
> are maintained (here i am only talking about the non
> clustered index pages and not the related table data).
>
> the index contains sorted data and has to be maintained to
> keep it that way.
>
> if this is so, will not non clustered indexes suffer from
> the same issues that cause performance issues with a
> clustered index on a apl table - page splits, rows deleted,
> page forwarding? again here i am only talking about the non
> clustered index pages and not the related table data.
>
> btw for deletes, the manual says that the corresponding
> entry in the non-clustered index will be deleted. is this a
> physical delete or 'marked for deletion'?
>
> appreciate the insights.
>

B-tree indexes are structures that rely on sorting. The difference
between a nonclustered index and a clustered index is that for a
clustered index on APL tables, the leaf level is actually the data (for
DOL tables, a clustered index is very similar to a nonclustered index).
In all cases, the leaf level of the index will be affected by
insert/update/delete activity, but to a certain extent the index tree is
rebalanced automatically. For APL clustered indexes however, the actual
data rows tend to get fragemented which affect I/O efficiency since
clustered index scans try to use large I/Os to read data sequentially.
When fragmentation occurs, this can affect query performance for queries
using such query plans.

As for deleting a row, an index row is really deleted. A data row is
reallyd eleted for APL tables, but it is marked as deleted for DOL
tables and cleaned up later.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


vtpcnk Posted on 2012-12-13 01:15:14.0Z
Sender: 798b.50c927d8.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50c92c22.7a65.1681692777@sybase.com>
References: <50c86133@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 12 Dec 2012 17:15:14 -0800
X-Trace: forums-1-dub 1355361314 172.20.134.41 (12 Dec 2012 17:15:14 -0800)
X-Original-Trace: 12 Dec 2012 17:15:14 -0800, 172.20.134.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13315
Article PK: 1158313


> As for deleting a row, an index row is really deleted. A
> data row is reallyd eleted for APL tables, but it is
> marked as deleted for DOL tables and cleaned up later.

but does the deletion of a index row (on a apl table)
necessarily mean that subsequent index rows are physically
'moved up' the index chain?

we have a table with 50 million rows. the table has only
non-clustered indexes. so many deletes happen on the table.
so does the deletion of a index row mean that subsequent
index rows (however many millions) are moved up the
row/page?

or is the space left blank? to be reclaimed later by reorg?

appreciate the insights.

thanks,
nanda


Rob V Posted on 2012-12-13 09:46:12.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
References: <50c86133@forums-1-dub> <50c92c22.7a65.1681692777@sybase.com>
In-Reply-To: <50c92c22.7a65.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50c9a3e4$1@forums-1-dub>
Date: 13 Dec 2012 01:46:12 -0800
X-Trace: forums-1-dub 1355391972 172.20.134.152 (13 Dec 2012 01:46:12 -0800)
X-Original-Trace: 13 Dec 2012 01:46:12 -0800, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13316
Article PK: 1158380

I'm not sure what you mean by 'moved up the index chain'. Index rows
live on index pages which can anything from almost empty to full.
The ASE index tree are always kept at the same height, i.e. for every
index row the number of steps bbetween the index root page and the leaf
page is the same.
If an index page get completely empty is is deallocated and can later be
reused by that index when a new page is needed for adding new rows.

You should check out the ASE Performance & Tuning guide which has a
volume dedicated to space allocation and topics like this. And you can
of course inspect index page yourself with dbcc page() so you can see
the effect of deleting a row.

HTH,

Rob V.

On 13-Dec-2012 02:15, vtpcnk wrote:
>> As for deleting a row, an index row is really deleted. A
>> data row is reallyd eleted for APL tables, but it is
>> marked as deleted for DOL tables and cleaned up later.
>
> but does the deletion of a index row (on a apl table)
> necessarily mean that subsequent index rows are physically
> 'moved up' the index chain?
>
> we have a table with 50 million rows. the table has only
> non-clustered indexes. so many deletes happen on the table.
> so does the deletion of a index row mean that subsequent
> index rows (however many millions) are moved up the
> row/page?
>
> or is the space left blank? to be reclaimed later by reorg?
>
> appreciate the insights.
>
> thanks,
> nanda
>


vtpcnk Posted on 2012-12-14 02:13:13.0Z
Sender: 3acd.50ca878d.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50ca8b39.3bc8.1681692777@sybase.com>
References: <50c9a3e4$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 13 Dec 2012 18:13:13 -0800
X-Trace: forums-1-dub 1355451193 172.20.134.41 (13 Dec 2012 18:13:13 -0800)
X-Original-Trace: 13 Dec 2012 18:13:13 -0800, 172.20.134.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13318
Article PK: 1158388

> I'm not sure what you mean by 'moved up the index chain'.

the leaf pages of a non clustered index contains the key
values and pointers to page and row - i think.

let us suppose in a leaf index page, there are 10 such above
index rows. then a delete statement deletes 4 rows out of
the 10 (say rows 4, 5, 6, 7). now you have rows 1 to 3 and
then 8 to 10 in the page - totally 6 rows.

so when i talk about 'rows moving up', i meant if the rows 8
to 10 are moved up in the page in the place of the deleted
rows. and then one more row from the subsequent page is
moved into the last remaining row space - that way the page
is full again.

i am asking this since optimal usage of page space seems to
be a criterion in performance - else when the page is read
only 6 rows are going to be fetched - so only 60% of the
fetch is optimal and the rest empty space (40%) is a wasted
fetch.

if this 'moving up' (where existing rows take up the space
of the deleted rows) doesn't happen, then over a period of
time, due to various deletes, will not many pages have a lot
of empty space everywhere? and that again will defeat the
ideal of optimal space usage in pages during fetches.

so if this 'moving up' doesn't happen, then how can
non-clustered indexes then not benefit by periodic
recreation or reorg rebuild - which i think will fill up
pages fully whereby fetches will be optimal.

btw i will check out 'dbcc page' - thanks.


Bret Halford Posted on 2012-12-19 17:44:38.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
References: <50c9a3e4$1@forums-1-dub> <50ca8b39.3bc8.1681692777@sybase.com>
In-Reply-To: <50ca8b39.3bc8.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50d1fd06$1@forums-1-dub>
Date: 19 Dec 2012 09:44:38 -0800
X-Trace: forums-1-dub 1355939078 172.20.134.152 (19 Dec 2012 09:44:38 -0800)
X-Original-Trace: 19 Dec 2012 09:44:38 -0800, vip152.sybase.com
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13326
Article PK: 1158492


On 12/13/2012 7:13 PM, vtpcnk wrote:>> I'm not sure what you mean by

'moved up the index chain'.
>
> the leaf pages of a non clustered index contains the key
> values and pointers to page and row - i think.
>
> let us suppose in a leaf index page, there are 10 such above
> index rows. then a delete statement deletes 4 rows out of
> the 10 (say rows 4, 5, 6, 7). now you have rows 1 to 3 and
> then 8 to 10 in the page - totally 6 rows.
>
> so when i talk about 'rows moving up', i meant if the rows 8
> to 10 are moved up in the page in the place of the deleted
> rows. and then one more row from the subsequent page is
> moved into the last remaining row space - that way the page
> is full again.
>
> i am asking this since optimal usage of page space seems to
> be a criterion in performance - else when the page is read
> only 6 rows are going to be fetched - so only 60% of the
> fetch is optimal and the rest empty space (40%) is a wasted
> fetch.
>
> if this 'moving up' (where existing rows take up the space
> of the deleted rows) doesn't happen, then over a period of
> time, due to various deletes, will not many pages have a lot
> of empty space everywhere? and that again will defeat the
> ideal of optimal space usage in pages during fetches.
>
> so if this 'moving up' doesn't happen, then how can
> non-clustered indexes then not benefit by periodic
> recreation or reorg rebuild - which i think will fill up
> pages fully whereby fetches will be optimal.
>
> btw i will check out 'dbcc page' - thanks.
>

Rows aren't "moved up" from adjoining pages during normal
processing. The locks required to do so would decrease
concurrency and increase deadlocks.

It is common for index pages to have a fair amount of empty
space, though the specifics vary depending on how the table
is used (load a lot of data and then whittle it down vs
start empty and keep inserting new vs frequent updates that
change the distribution of key values vs static vs ...).
In the case of growing tables (most cases, anyway), effort put
into compacting index pages will result in more work being
done splitting index pages as new data is inserted.

The use of data cache could be improved somewhat by putting
more effort into compacting the data, but in general the cost
of index access is more a function of how high the index is
(number of levels/pages read from root to leaf). It generally
takes a lot of compacting to reduce the index height.

Rebuilding indexes can help sometimes, if activity in the
database is causing them to become very fragmented. The
index page cluster ratio is one measure of that. Another
is to compare the current index size (sp_spaceused <table>, 1)
with the size estimated by sp_estspace.

-bret


vtpcnk Posted on 2012-12-12 01:28:20.0Z
Sender: 32a9.50c7d4e6.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50c7ddb4.3486.1681692777@sybase.com>
References: <50c7a068$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Dec 2012 17:28:20 -0800
X-Trace: forums-1-dub 1355275700 172.20.134.41 (11 Dec 2012 17:28:20 -0800)
X-Original-Trace: 11 Dec 2012 17:28:20 -0800, 172.20.134.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13312
Article PK: 1158239

regarding reorg rebuild index, if this is done, will the
index creation date show as the date the index was rebuilt?
or the date it was originally created.

btw if i import statistics for a table (table1) using opt
diag (from another similar table (table2) in another db),
then later if i export the statistics information from
table1, is there anyway to confirm that the statistics was
actually imported from another dataserver/database?

appreciate the feedback.


Rob V Posted on 2012-12-12 10:50:54.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: non clustered index maintenance
References: <50c7a068$1@forums-1-dub> <50c7ddb4.3486.1681692777@sybase.com>
In-Reply-To: <50c7ddb4.3486.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50c8618e@forums-1-dub>
Date: 12 Dec 2012 02:50:54 -0800
X-Trace: forums-1-dub 1355309454 172.20.134.152 (12 Dec 2012 02:50:54 -0800)
X-Original-Trace: 12 Dec 2012 02:50:54 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13314
Article PK: 1158243


On 12-Dec-2012 02:28, vtpcnk wrote:
> regarding reorg rebuild index, if this is done, will the
> index creation date show as the date the index was rebuilt?
> or the date it was originally created.
>
> btw if i import statistics for a table (table1) using opt
> diag (from another similar table (table2) in another db),
> then later if i export the statistics information from
> table1, is there anyway to confirm that the statistics was
> actually imported from another dataserver/database?
>
> appreciate the feedback.
>

I think the index creation date ramains unchanged, but you'd have to do
a quick test to be sure.

You cannot tell whether statistics were imported with optdiag or
generated locally (unless you have imported simulated statistics).

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------