Reviewing sp_sysmon Output: Cache Configuration

    By: Jeffrey Ross Garbus on Apr 01, 2014

    This article discusses cache configuration and examines the output of the sp_sysmon stored procedure.

    As we mentioned in our prior article, "Reviewing sp_sysmon Output: Task Context Switches", which was published in the January 2014 edition of the ISUG-TECH Journal, as part of a balanced tuning approach, or as part of a “What in the world is going on?” exercise,
    you will occasionally need to examine output from the sp_symon stored procedure. As part of this, the great DBA manages and tunes system resources to best utilize available memory. In this article, we’re going to discuss cache configuration, and validating it with sp_sysmon.

    Cache 101 – How SAP ASE Uses and Manages Cache
    SAP ASE, like an operating system, performs its own cache management. Because this is an article and not a book or manual (we highly recommend reading the SAP ASE documentation on this), we’re going to simplify a bit, but not a lot.

    Cache is an area of memory that is used as a buffer between memory and disk for the dual purpose of giving memory a dedicated area to manipulate the data it pulls off the disk for access and modification, and to reduce the chances that it needs to pull the data off the disk. ALL access to or modification of data by the DBMS occurs in cache.

    Cache organizes data in pages. While you may initially think of data as residing in tables (logically, that’s the case), physically the server puts those rows on a bite-sized section of disk. Historically, a page was 2k in size, though over time SAP ASE has allowed the person doing the installation to choose a page size. Your choices are 2k, 4k, 8k, or 16k. Most of the shops I’m aware of currently use a 4k page, as a lot of hardware optimizes towards that page size (based upon experimentation, not documentation), but keep in mind that rows will NOT span pages; as a result page size also limits row size; another discussion for another article.


    Login to read the article. Not a member? Create a free account!

    Released: April 1, 2014, 2:35 pm | Updated: July 9, 2014, 5:27 pm
    Keywords: ASE DBA Article | Technical Journal | Alvin Chang | Jeff Garbus | Lyle Fernquist | sp_sysmon




    Copyright © 2014 ISUG-TECH. All Rights Reserved
    All material, files, logos and trademarks within this site are copyright their respective organizations

    Terms of Service - Privacy Policy - Contact the Help Desk