• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

Sybase Reserved Pages vs. Free Page

Hello,

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?

Thanks,
Garrett
0
garrettd79
Asked:
garrettd79
  • 4
  • 3
1 Solution
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
 
garrettd79Author Commented:
Thanks Joe.

So how could a segment (my index segment) have more reserved pages available than free pages?  
0
 
Joe WoodhousePrincipal ConsultantCommented:
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).
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
garrettd79Author Commented:
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.
0
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
 
garrettd79Author Commented:
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?
0
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
 
leonstrykerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now