?
Solved

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

Posted on 2006-04-08
9
Medium Priority
?
647 Views
Last Modified: 2008-01-09
Hi,
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.
Thanks.

Maria

 


0
Comment
Question by:maria_rossi
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:bret
ID: 16417976
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.

-bret




0
 

Author Comment

by:maria_rossi
ID: 16420904
Bret,
 
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
go
dbcc traceon(3604)
go
dbcc pglinkage(16,1041,0,2,0,1)
go

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
window).

Everyday, I am learning something  :)..

Thanks.

Maria
0
 
LVL 10

Expert Comment

by:bret
ID: 16421105
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.

Cheers,
-bret


0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 16435027
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.
0
 
LVL 10

Expert Comment

by:bret
ID: 16436405
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).

Cheers,
-bret
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 16436615
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!
0
 
LVL 10

Accepted Solution

by:
bret earned 500 total points
ID: 16437370
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
0
 

Author Comment

by:maria_rossi
ID: 16438047
This is interesting reading.  And yes, both of my questions were answered.  Thanks a lot, both.  
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 16442016
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->
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Applications for our next round of the Experts Exchange Scholarship Contest are starting to roll in. It made us wonder what our past winners are up to these days. Here's a look at what four winners experienced with the contest and what they're doing…
Most folks would know the basics of how Dropbox works, so that’s not the purpose of this article. Security is what it’s all about, so here I’ll share how I choose to secure my Dropbox Account and the Data it contains.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question