Sybase Reserved Pages vs. Free Page

Posted on 2011-10-18
Last Modified: 2012-08-13

I am running sybase 12.5.4.  We have a stored procedure that will calculate the free space percentage for each segment.  It does its calculation based on reserved pages.  There have recently been some issues where one of the segments is off.  It is showing about 10% more free space than is available.  

If I do a calculation using free pages and total pages, I get the actual free space (free pages / totals pages) * 100

So what is the difference between reserved pages and free pages in Sybase ASE?

Question by:garrettd79
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Ok, this isn't actually a simple question to answer. :)

    Sybase doesn't actually allocate page by page. A group of 8 contiguous pages is called an extent, and extents can't be shared between different data objects (tables and indexes). That means every table is always allocated a minimum of one extent (for the table itself) plus one extent per index. This is even when the table has zero rows! Try it - create an empty table, create an index on it, and run sp_spaceused. You'll see "reserved pages" equal to two extents = 16 pages = 32Kb on a 2Kb page server.

    So the minimum allocation size is in chunks of one extent at a time. This is what "reserved pages" reports on - how many extents have been used, whether or not the pages themselves actually have anything on them.

    In my example above, the empty table with one index has allocated 16 pages. "Free pages" are pages that have not been allocated to any table or index.

    The other thing to consider is that reserved page counts are not necessarily maintained transactionally, depending on the method you use to count them. sp_spaceused is pretty accurate, and you can pull apart its source code in sybsystemprocs to see how it does it.

    Author Comment

    Thanks Joe.

    So how could a segment (my index segment) have more reserved pages available than free pages?  
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Reserved = allocated. More reserved than free means more than half of the segment has been allocated (even if not actually used yet).

    eg. create enough tables with enough indexes on each and you could fill a data segment even if none of the tables ever have any rows in them.

    (Trivia for you - this is one of the enhancements necessary for SAP to run on ASE 15.7, because SAP installs 10,000+ tables with many indexes each, and the standard ASE rule of "preallocate everything first" chewed up a lot of space even those most of those tables might be for SAP modules that weren't installed.)

    Reserved > free is not an alarming condition. "In Use" > reserved is impossible since any page in use had to first be allocated. In fact you will almost always see reserved > in use, and the only time they'll be equal is when every allocated extent has eight pages actually in use.

    We're possibly being distracted by the tech. :) The real issue was how can numbers be out by 10%? It depends on the method you're using to arrive at that page count. In most modern versions of ASE (12.5 and up) they will always be exactly correct following an update statistics (on every table on that segment).

    Author Comment

    We use this stored procedure at all of our installations.  At 99% of the sites, the stored procedure is dead-on accurate.  However, at the one site, it is off by 10%.  The issue is, our service personnel use this stored procedure to gauge when they need to expand the database.  

    We had an issue where the service person ran the SP and it showed 8% free space in the index segment, however the site was down because actual free pages were 7.  

    Something different at this site compared to others is we have implemented DOL on 3 tables since there was a lot of deadlock contention on those tables.  But we do run reorg compacts frequently, so I would think I would reclaim the space used by row-forwarding from the DOL tables.
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Reorg compact is only a partial reorg. DOL definitely uses up more space and also messes up some measures of space accounting there.

    You could try a full reorg rebuild instead, but this needs the "select into" database option enabled, and will prevent you from taking transaction dumps until you do a full dump database. It also locks the tables more intrusively.

    I am now 90% confident the DOL tables is what's causing you problems at that one site.

    Without knowing exactly what SQL is in your proc I can't give you more specific advice, other than run reorg compact more often, and/or switch to running a full reorg rebuild if you can make that fit into your windows of availability (effectively requires an outage on each table while running - although you could probably run it on all three tables at the same time provided you have enough CPU and memory).

    Or for that one site just assume a 10% worst case error and be more aggressive in adding space - something you probably need to do with DOL tables anyway.

    Sorry I can't be more specific but I think we've found why your one problem site behaves differently.

    Author Comment

    Ok, I think I'm getting it :)

    So how can ASE reserve more pages than the segment actually has available for writing?

    I'm guessing, but does an extend just allocate 8 pages whether or not they actually exist?  So if you have 2 extents totaling 16 pages, but only 10 pages free, your reserved pages would actually be greater than free pages correct?
    LVL 24

    Accepted Solution

    ASE cannot reserve more pages than the segment actually has. Although strictly speaking a segment is not a container, it's just a label for one or more devices. Multiple segments can label or point to the same extent, by the way, which is why if you use user-defined segments it won't all add up properly.

    If ASE is reporting it has reserved more pages than actually exist, your stats are bad. Remember ASE doesn't keep track of these numbers transactionally, and can get confused if (just one example, there are more) many pages are allocated during a transaction which is later rolled back, and there hasn't been anything to force it to refresh its page counts.

    The only way to guarantee correct page counts is to run your code immediately after a full set of "update statistics" on every table in the database.

    I think the moral of the story is that 10% overhead is maybe not enough for databases without DOL tables, and (at your site at least) definitely not enough with DOL tables. Eh, storage is cheap these days, why not over-provision by 20-25%? :)

    For your last question, only pages that exist can be allocated. Remember the "pages free" count is only what is left over after all reserved pages have been allocated.

    This is confusing. :) Perhaps the real summary is that it is very difficult to get very reliable numbers for these things, so your tolerances need to be greater.
    LVL 29

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Outlook Free & Paid Tools
    I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now