Sybase ASE table fragmentation issues

[DerekAsirvadem]

Guys & Dolls

Anyone watching this thread. I have published a major upgrade now V2.0 to my Sybase Data Storage Basics, Fragmentation, and Index Type doc this weekend. It answers some of the questions I have recvd. Most important, the three previous docs are now integrated into one doc, and normalised. 13 pages. Lots of links. Sybase should be paying me, but I won't hold my breath.

Visit again and enjoy

[DerekAsirvadem]

(09-14-2011 03:12 PM)sybdba Wrote:  Anyone care to comment how use of a SAN with volumes striped across disks ...

Where to begin ?

SAN Consideration

1. The SAN and ASE are totally independent of each other, you can change one without affecting the other. However., a poorly set up SAN is of course a problem to every user of the SAN, including ASE.
2. Sybase 'sees' the SAN Logical Volume as a contiguous series of disk blocks. More important, it treats it as such.
3. So you should be making every effort within ASE to maintain contiguity. The whole concept "fragmentation" is based on contiguity: fragmentation literally means non-contiguous.
4. On the SAN side:

• ensure the LVs used by ASE are separate from LVs used by anything else (file systems, Oracle, etc). Otherwise every access by every user affects every other user. Do not believe the marketing bulldust from EMC/HP/IBM, etc, the SAN is merely a computer with the specialised puirpose of managing disk arrays. It is governed by the laws of physics, not by magic.
• Set the LVs up for Raw Partitions, not ufs.
• a blocksize close to 16K (one Extent, 8 Pages)
• Mirrored, then striped. That means RAID1+0. You want mirrored for security and striped for speed. Yes, that means split across several physical disks, but that has nothing to do with ASE (where the LV remains a contiguous Device)
• Never use RAID5 or a part of a massive "one size fits all" RAID5 volume.
• Get a few small LVs (for master, tempdb, sybsystemprocs, log, etc Devices), plus many large LVs (for data Devices). Obviously the latter will depend on your db size.

Quote:changes or mitigates the fragmentation issues ...

SAN Mitigation ?!?


If some EMC/IBM/HP salesman told you that, just remember that it is technically impossible, he was lying, he sells boxes.

None whatsoever. What happens in the SAN is invisible to ASE; what happens in ASE is invisible to the SAN. ASE does not know if the LV is mirrored, striped, cross-hatched, slow, fast, or screwed, it just sees a RP. The SAN does not know if the Sybase Device is contiguous or fragmented, or fragmented at level I but not Level II, or fragmented at all levels.

• Anyone suggesting that the SAN is "fragmented" because it is striped or that therefore fragmentation on the Sybase side does not matter because "evything is stwiped anyways" should quit working in IT and go back to digging ditches or arranging flowers. This is plain avoidance of work by avoidance of thinking. "There is no use digging that ditch because the water flows downhill". Except the idiot does not know which direction downhill is. He has tricked you into believing a huge lie, by mixing in a tiny bit of truth.

If the RPs (well, in ASE, it only sees RPs, not LVs) are fragmented, then performance will suffer, regardless of whether the RPs are located on a SAN or in China, or whether they are striped or polka-dotted or not; if you have managed the space well, and they are not fragmented, performance will be optimal.

If you fix the fragmentation within Sybase, the SAN volumes do not get unstriped, they do not change, only the content changes.

Quote:or the solutions to those fragmentation issues

I can write a book on that subject. I have published a new version of the doc, please read. That tells you only the structures and considerations. You need to have some experience in (a) IT in general (b) Sybase in particular © Database design, Normalisation (d) physical Db design, table structures, lockschemes, fragmentation prevention (before it happens), fragmentation correction (after it happens).


Then there are considerations such as, is the Db a real Relational Db, or is it a bucket of fish, used merely as a storage location for records ? The reason this is important is, a real Db is highly Normalised; has real Relational Keys, which are multiple-column, and which lends itself to data distribution ... that means APL tables, and low maintenance. A record filing system has ID[iot] "keys"; poor Normalisation (or none); far more Indices; lots of duplication; far more locking and contention ... that means DPL/DRL tables (not that that helps), and high maintenance. I know which woman I would marry.


APL, not alone, but in concert with the other aspects of a real RDb, results in far less fragmentation than DPL/DRL. Level III is a peculiar form of fragmentation that only DPL/DRL tables have. So I have to worry about Levels I and II only, never III. I have 1TB RDbs that need de-fragmentation once every two years. No REORG. Ever.


Identification


Fragmentation is identified by:

1. DERIVED_STAT()
2. Asynch Pre-Fetch and Large I/O (stats to be found in sysmon) being lower than the resources set up for it, or zero. High APF & LIO stats means no fragmentation at Level I & II.

Prevention


First we will handle the prevention. This affects Levels I and II, there is no prevention for Level III:

1. Application and Logical Db design
2. Physical Db design
3. Use of Segments. This distributes that load, and separates competing tables; separates tables and their NCIs; etc. That is a serious performance boost. In the fragmentation dept, because fewer DataStructures are located on the Devices, specifically AllocationUnits, they are far less fragmented. therefore they need de-fragmentation far less often.
4. Fixed Length rows. Row Forwarding (expansion) and migration and some PageSpiits (due to expansion) can be eliminated by fixing the row length. That means fixing the column length. That means no var length or Nullable columns. That is why Logical Db design is at the top of the list: a good design has no Nulls; a bad one is full of Nulls. If not, set EXP_ROW_SIZE, and cross your fingers.

• Never, ever, Index a variable length (Nullable is var length) column. These columns have to be unpacked/packed on every access to the B-Tree, not just every access to the row. This results in, not a slow, but a very slow index.

1. APL with multiple-column Keys over DPL/DRL with ID "keys". Because multi-column Keys are already distributed, and easy to manage via [6]. DPL/DRL are, well, difficult. IDENTITY columns are an abomination, in case you have not found out yet. The "keys" which are not supposed to be seen by the user or to be ordained any meaning, get seen, get used, have meaning thrust upon them, and before you know it, IDENTITIES get "fixed'. Difficult indeed.

Now there is a big gap before we get to number six ... ie. this point has little effect, compared with the five points above, each of which has substantial effect:

1. FILLFACTOR and RESERVEPAGEGAP. Useful for all DataStructures that will have interspersed INSERTS. Of course, the DataStructures containing data are most affected. And it has more effect on stable DataStructures (ie. APL) than on unstable ones (DPL/DRL).

• As someone else posted, make these settings permanent via sp_chgattribute or temporary via options on the CREATE command.

Correction of Fragmentation


Now we will handle the de-fragmentation (after it happens) operations.

Level I


You need Segments and experience to correct Level I. However for purposes of explanation re the differences between Level I and Levels II & III, an overview, a simple method is:
- Analyse your table usage, and table competition (within transactions)
- That will produce a Segment Plan
- Create a new target db on the number of Devices required for the Segment Plan
- Create the Segments
- Create all db objects ON the relevant Segments, except the indices
- Either bcp-out source db/bcp-in target db or SELECT INTO target FROM source
- Create Indices ON the relevant Segment
- script and test the entire process
- then divide the scripts by the number of engines, so that they can run in parallel without interfering with each other
- execute all

Levels II & III

This needs to be planned as part of your regular maintenance scripts, your downtime window, just like UPDATE STATS.

1. For DPL/DRL, Regular REORG REBUILD. You have probably figured out that RECLAIM_SPACE and FORWARDED_ROWS do not do much. Minimum once a fortnight (most people do half their tables once a week, and switch). If not, you can get used to very slow tables. this interferes with the log sequence, so it is effectively OFFLINE maintenance.
2. An alternative is DROP/CREATE CLUSTERED INDEX (without SORTED_DATA option). Often it is faster than REORG REBUILD. DROP/CREATE CI is good for APL as well.

• With 15.5, we are supposed to be able to REORG REBUILD a Clustered Index, but I have not had need to try that yet. Because. I don't. Have. Fragmentation. In my tables. they are all APL and I use Segemnts.

--


If there is anything not completely clear, please post a specific question.

[ketan7484]

Hi Derek,
Can you please tell us how to defragment a trillion record table and not having space in database, equal to the table size.