1 / 10

Sybase ASE: DB Fragmentation

Sybase ASE: DB Fragmentation. Rev. 6.2012. Sybase Administration Tools available at: http://andrewmeph.wordpress.com mailto: andrew.me.ph@gmail.com. Note and disclaimer:. The tools posted on the blog will require the same setup:

lilly
Download Presentation

Sybase ASE: DB Fragmentation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sybase ASE: DB Fragmentation Rev. 6.2012 Sybase Administration Tools available at: http://andrewmeph.wordpress.com mailto: andrew.me.ph@gmail.com

  2. Note and disclaimer: • The tools posted on the blog will require the same setup: • Sybase Client installed (for tools connecting to ASE server). • Administrative login user (for tools doing ASE administration). • A set of two or more .dll files supplied with the installation – performing licensing and environment operability checks. • All the tools have been tested to run bug-free. Nonetheless, the tools are provided “as is.” Responsibility for running a tool lies with the user. Comments and bug reports are welcome. They will be addressed and rectified. Please send the comments either directly by mail or through the blog. • As with any tool, it is recommended to start using it in the development environment before trying it on the Production Server.

  3. DB fragmentation: Quick info Every DBA knows that most DB operations are performed in pages. But few really know how their tables are organized. How many pages a “hot” table occupies? How many extents? Allocation Units? Beyond the [data/index] cluster ratio reported by OPTDIAG, how dispersed are the pages within extents, allocation units? Does it matter at all? DB Fragmentation analyzer opens a window into DB internals: • It allows to check each table’s fragmentation beyond the page level – on the level of extents and allocation units. • It allows to walk through table page chains to see how ordered the chain is. • It allows to take a decision on selecting tables for maintenance on a level more sophisticated than data/index clustered ratio. • It allows to inspect what different lock schemes, partitioning options do with data in tables. • It is for those curious to see beyond the limitation of a database page and those who take IO (and optimizer) seriously.

  4. DB fragmentation: FAQ DB Fragmentation Assistant Pre-requisites: • Must be run as sa user. • ODBC and Open Client aliases must be the same (synchronize DSEDIT and DSN SRV names before running the tool). • For large tables, it is recommended to skip page linkage test since this will be too time-consuming and heavy. DB Fragmentation Assistant Features: • [Esc] keyboard key aborts operation at any step. • Information collected by the tool may be verified by DBCC TABLEALLOC and DBCC INDEXALLOC commands.

  5. DB fragmentation: startup [Fragmentation Assistant] tab is designed to specify tables one wants to analyze. • Filter tables by name or choose a single table. • Click “Load Tables”. • Double click on the selected table so that its OAM and FIRST pages are listed correctly. • Leave “OAM Only” checked for large tables. • Click on the Walk Selected button.

  6. DB fragmentation: WALK Single When you “walk” through table pages the following data is displayed: • Data Cluster Ratio • Index Cluster Ratio • Large IO Efficiency • Number of Allocation Pages a table occupies (PG/AU Ratio) • Number of Extents a table occupies (PG/EX Ratio) • If Page Chain is checked, fragmentation % is calculated (how many linkage leaps across extents are there). By the end of each check one gets a full picture of how the specified table is materialized in terms of database pages.

  7. DB fragmentation: WALK ALl It is possible to walk through a single table or a group of tables: • Write filter for table name. • Load Tables. • Delete unwanted tables from the list [DEL keyboard key]. • Click “Walk All” button. When all the tables will be walked through, you will get a report in the Operation Log tab listing all the information collected for all the tables (exportable to .csv).

  8. DB fragmentation: page details During the walk, you will get a list of pages that has been walked through. Double click on the row for a particular page will display that page details.

  9. DB fragmentation: LOG Operation Log looks like this:

  10. DB fragmentation: feedbacks • The tool is being reworked from time to time to address changes in the ASE and align it better with ASE administrators needs. • In order to make the software better and more convenient your feedback is encouraged. • Feedback and bug reports may be made either directly to andrew.me.ph@gmail.com or posted as comments to the tool page published on the wordpress.com blog. • The blog is available at http://andrewmeph.worpress.com. • More tools are available there, as well as general comments on Sybase ASE, RS (and in future IQ) products. • You are welcome to post your own ideas there which may be later transformed into customized tools and posted there for download.

More Related