Data Page Cluster Ratio did not improve after a drop/create clustered index

Question on DPCR and dbcc pglinkage.

First on DPCR:
Optdiag showed DPCR for a table = 0.020057.    This is extremely low, so we dropped and recreated the clustered index.  However,  after the defrag, the DPCR is still low, at 0.02021.     Why did the DPCR not improve?    BTW, the tables has allpages locking.

Second on DBCC pglinkage:
After a few days we ran at dbcc pglinkage on the same table,  the pages are all over the place.  I was told that if the table was not fragmented, the pages would be in ascending, though, not necessariy sequential.    At the end, it showed:

Page : 506697
End of chain reached.
784441 pages scanned.  Object ID = 53575229.  Last page in scan = 506697.

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Now, tonight, I was about to do a drop/recreate the clustered index again (thinking, that perhaps the first attempt did not really run and we just did not notice it then).   I decided to run the following first:

1.  optdiag (still showed DPCR = 0.02)
2.  Update stats on table
3.  optdiatg again (still showed DPCR = 0.02)
4.  dbcc pglinkage, showed this at the end:

Page : 2259628
Page : 2266579
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Compare the 2 runs of the DBCC pglinkage,  the second run did not have the "End of Chain"  and the number of pages scanned.

I decided to not continue with the drop/recreate.  Makes me wonder if the table is corrupt or something.

What problems does the 2 DBCC show?  Can I safely drop and recreate the clustered index ?   If not, how can the DPCR be improved?

If you need the optdiag output, pls let me know.



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Maria,

Do you still have the exact pglinkage command you ran?

Also, check the errorlog for any messages around that time -
one possibility is that someone else ran a "traceoff(3604)" while
your pglinkage was running.

When you create the clustered index, what syntax are you using?
In particular, are you using the "with sorted_data" option?  That
causes ASE to just rebuild the index tree on top of the existing location
of the data, rather than reorganizing all the data.


maria_rossiAuthor Commented:
Thanks for the response.

Yes, i have the pglinkage command:

select first from sysindexes where indid < 2
and id = object_id('table1')    <====  returned '1041' which I used at the command below
dbcc traceon(3604)
dbcc pglinkage(16,1041,0,2,0,1)

Also checked the errorlog,   indeed, some other process did a traceoff on 3604 while my pglinkage was running.   Is there a way to keep it on for my process?

And at the clustered index, yes, we used the 'with sorted_data' option to recreate the index..  

I will try on a weekend to do the defrag again (this is a prod server, so have to be scheduled during a maintenance

Everyday, I am learning something  :)..


Hi Maria,

There currently isn't anyway to prevent another user from turning off a traceflag.  It isn't well known, but traceflags are currently implemented as one big global array that everyone shares - but the general assumption is that "traceon" is session specific.  It currently isn't.

We are developing truly session-specific traceflags, they are expected to be available starting in the 12.5.4 ESD #1 rollup.


Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Joe WoodhousePrincipal ConsultantCommented:
I think Bret's solved both your questions - drop & recreate with sorted_data isn't a full rebuild of the table.

Another matter that could affect DPCR is if the clustered index isn't unique. Duplicates in a clustered index on allpages tables are stored in "overflow" pages which will definitely break a sequential pglinkage and could well look like a poor DPCR.

That being said I think the key here was the sorted_data option.

Be aware that create clustered index without sorted_data requires ~120% the size of the table in free space. It will be quite a bit slower, too.
Hi Joe,

I believe you are mistaken about pglinkage breaking on overflow pages.

It is true that if you were to scan one level up in the clustered index, there
are no pointers to the overflow pages (i.e. looking at the pages through dbcc ptripage).
But they are linkedto their neighbors in the data page chain.  (not that all this applies only to ALLPAGES tables - DOL pages aren't linked and the clustered index has a pointer to each and every row including duplicates).

Joe WoodhousePrincipal ConsultantCommented:
Maria, sorry for getting side-tracked here but I'm very interested in what bret's saying as this is something I've never had a clear answer on before! 8->

Bret - I had always understood that overflow pages were kinda like text/image/java pages. Well, not so much in their specific implementation, but that if I asked a data page (index leaf) what it's "next page" was, it would be another index leaf. ie. the overflow pages weren't index leaves and weren't considered part of the clustered page chain.

Just to make sure I've understood you correctly, you're saying its next page would actually be the first overflow page, which we'd traverse in order until the last one, and it's next page would be the very next non-overflow data page (ie. a leaf of the clustered index)?

I have to admit I've never actually gone to the trouble of walking the page chains to find out. How cool to finally understand this after more than a decade! 8-> Thank you!
Yes, you understand me correctly.   If you use DBCC PAGE to look at the
first page with the value, it has a "HAS_OVERFLOW" status bit set.  Overflow
pages have the "IS_OVERFLOW" bit set, and, if there is a following overflow page,
also has the "HAS_OVERFLOW" bit set.

So - trying some ASCII art here (cut and paste, use fixed-length font like courier):

index page 999
value: 5     value 6   value 10------------------------------
  |             \__________________________                  |
  |                                        |                 |
 \/                                       \/                \/
data page 444  <--> data page 445<--> data page 446 <--> data page 447
(has_overflow)        (is_overflow)    (data page)        (data page)
5,5,5,5                 5,5,5,5          6,7,8,9           10,11,12

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maria_rossiAuthor Commented:
This is interesting reading.  And yes, both of my questions were answered.  Thanks a lot, both.  
Joe WoodhousePrincipal ConsultantCommented:
Excellent, thank you bret!

I've sometimes said the fastest way to get an answer is not to ask a question, but to assert something incorrect. 8->
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.