SAP on Sybase ASE for Windows Server 2008 R2 - in a Failover cluster environment with RDM

[Former Member]

I have an SAP ERP EHP6 instance running on Windows 2008 R2 and Sybase ASE 15.7.0.101 This is a Production system - and users have reported long response times, further looking into the installed architecture,  we have noticed that disk reads have been very high always at 99% to 100%, therefore we have also noticed, that our Sap implementation partner has installed the Database and the transaction logs both on the same Partition(data store), we wish to move the transaction logs to another partition.

By doing this segregation, we intend to address the excessive disk reads that are happening on one single partition.

How can this be achieved ?

Our current production system is installed and running on windows 2008 R2 in a windows -  fail over cluster environment.

RDM luns are presented directly from Storage as partition disks to the clustered nodes.

Will need to understand, in this sort of environment what is the best practice that needs to be followed.

and how the transaction logs can be moved to another partition ?

Ahmed

[former_member182259]

I doubt it is the transaction log that is doing this - especially the reads.   I would use DBA Cockpit and look at the Performance --> Table Data and sort by PhysicalReads descending and take a look a the top 10.....and then sort by LogicalReads descending and do the same - if you merge the two lists, you will likely see ~5 main culprits.    The problem there is then identifying which queries are driving it.   Answer is also simple - go the Performance --> Statement Cache/Statements, choose to do a custom search and enter the first table name with wildcards (e.g. *VBAK* ) in the tablename area and do the search.   This will show all of the statements that have that table in it - again, you can sort by TotalCPU, TotalPIO, etc.      If you see a statement that has 1,000,000 UsedCount (or MetricsCount) and also has 1,000,000 LogicalReads - it is fine - that is 1 LIO per execution.    What you are looking for is a statement that has something like 10,000,000 LIO's for 12 executions.

Chances are, you will find that ATM has not been set up and stats are old and you are doing a lot of table scans....

[former_member182259]

BTW - yes, it would still be a good idea to move the transaction log to a different device.   There are several ways of doing this, but best bet would be to open a case with support and have them walk you through it.

[Former Member]

Hi jeff, thanks for your reply - following up with your comments, we have attached the screen shot from the performance --> Statement Cache/Statements.

here we see excessive.

For the transaction log movement will open a case with support.. thanks for your suggestion.

[former_member182259]

A few simple instructions to begin....there likely are SAP txns that will do this as well, but I am not familiar with them ....  

Log in to ASE as 'sapsa' using (replace SID with your SID database/servername):  isql -Usapsa -SSID -DSID -w999, and do the following:

1 - exec sp_helpindex 'SAPSR3.ZRFID_ASSIGNMENT'

2 - exec sp_helpindex 'SAPSR3.ZRFID_MAT'

...post the results.

It appears that ZRFID_ASSIGNMENT is doing a tablescan instead of using an index.   Given that the only predicates are MANDT and RFID2, then there should be an index on the pair {MANDT,RFID2} - or at least minimally {RFID2} and MANDT is frequently a single value and almost of no use in such cases.   If there is, then the next question is when was the last time update index statistics was run (e.g  the ATM jobs - should be set up - see the SAP Note on that), as another likely possibility is that stats are very old and so the optimizer is getting stale information and making bad costing choices as a result.

Similar situation with respect to ZHU_RFID and the column ZRFID_MAT.

[Former Member]

Hi jeff,

i have attached the log results for your reference.

C:\Users\sybjep>isql -SJEP -Usapsa -DJEP

Password:

1> exec sp_helpindex 'SAPSR3.ZRFID_ASSIGNMENT'

2> go

Object has the following indexes

index_name

         index_keys

         index_description

         index_max_rows_per_page

         index_fillfactor

         index_reservepagegap

         index_created

         index_local

------------------------------------------------------------------------

         -----------------------------------------------------------------------

--------------------------------------------------------------------------------

-----------------------------------------

         -----------------------------------------------------------------------

---------

         -----------------------------------------------------------------------

---------------------

         ----------------------------------------------------------------

         -----------------------------------------------------------------------

---------

         -----------------------------------------------------------------------

-----

         ------------------------------------------------

ZRFID_ASSIGNMENT~0

          MANDT, RFID1, EBELN, BEDAT, MBLNR, MJAHR, ZEILE

         nonclustered, unique

                               0

                        0

                            0

         Feb 23 2014  9:01AM

         Global Index

(1 row affected)

index_ptn_name

         index_ptn_seg

-------------------------------------------------------------------------------

---------------------------------

         ----------------------------------------------------

ZRFID_ASSIGNMENT~0_603067545

         default

(1 row affected)

(return status = 0)

1> exec sp_helpindex 'SAPSR3.ZHU_RFID'

2> go

Object has the following indexes

index_name

         index_keys

         index_description

         index_max_rows_per_page

         index_fillfactor

         index_reservepagegap

         index_created

         index_local

------------------------------------------------

         -----------------------------------------------------------------------

-----------------------------------------------------------------------------

         -----------------------------------------------------------------------

---------

         -----------------------------------------------------------------------

---------------------

         ----------------------------------------------------------------

         -----------------------------------------------------------------------

---------

         -----------------------------------------------------------------------

-----

         ------------------------------------------------

ZHU_RFID~0

          MANDT, HUNO, ZRFID_MAT, KDAUF, KDPOS

         nonclustered, unique

                               0

                        0

                            0

         Feb 23 2014  9:00AM

         Global Index

ZHU_RFID~WIP

          MANDT, KDAUF, TCODE

         nonclustered

                               0

                        0

                            0

         May 14 2014 10:10AM

         Global Index

(2 rows affected)

index_ptn_name

         index_ptn_seg

-------------------------------------------------------------------------------

-------------

         ----------------------------------------------------

ZHU_RFID~0_1491350638

         default

ZHU_RFID~WIP_1491350638

         default

(2 rows affected)

(return status = 0)

1>

[former_member182259]

Sort of what I expected.   All the physical IO and probably most of the CPU usage you are seeing is due to those queries.   With respect to your earlier concern and the log device, I would make it a very low priority right now.   Your #1 priority - and this should be treated as an *immediate* action is to add a couple of indexes.   I will explain why:

Take the top query in that output from earlier:

SELECT TOP1 FROM "ZRFID_ASSIGNMENT" WHERE "MANDT"= ? AND "RFID2" = ?

The only index on ZRFID_ASSIGNMENT is on the keys {MANDT, RFID1, EBELN, BEDAT, MBLNR, MJAHR, ZEILE}   ....note that the second column is RFID1 and not RFID2.   Consequently, the database has no choice other than to tablescan this table - which could drive a lot of physical reads, except DBA Cockpit is showing 0 for Total PhysicalReads.....but 17 billion logical reads.   All you need to do is:

create nonclustered index SAPSR3."ZRFID_ASSIGNMENT~Z0"

       on "ZRFID_ASSIGNMENT" ("MANDT","RFID2")

       with statistics hashing, statistics using 100 values

then

exec sp_recompile 'SAPSR3.ZRFID_ASSIGNMENT'

....and that query will be a ton faster and likely not consume as much data cache.

Similarly the second query:

SELECT * FROM "ZHU_RFID" WHERE "MANDT"=? and "ZRFID_MAT" IN (?, ?, ?, ?, ?, ?, ?, ?, ...)

That table has two indexes:

ZHU_RFID~0         {MANDT, HUNO, ZRFID_MAT, KDAUF, KDPOS}

ZHU_RFID~WIP    {MANDT, KDAUF, TCODE}

It is possible that the query shoud use ZHU_RFID~0 index as ZRFID_MAT is the third column - but since HUNO is not in the query, the optimizer has to look at how unique the HUNO column is and determine if it can simply do an OR strategy and use the index or if the HUNO column is too unique, then it has to consider an index leaf scan.   In this case, with the large IN() clause, it would have to make this determination for each of the IN() clause values and then aggregate the result to see if a table scan would be faster.   I suspect, it considered the latter....or an index leaf scan.    I would add an index:

create nonclustered index SAPSR3."ZHU_RFID~Z1"

       on "ZHU_RFID" ("MANDT","ZRFID_MAT")

       with statistics hashing, statistics using 100 values

As with the first query, this was not a major contributor to Physical Reads directly - OTHER than possibly that because of all the table scans, it was causing a lot of cache volatility resulting in tables being re-scanned as these table scans consumed memory.    If these tables are small, I would also consider adding them to the queue cache (see SAPNote on Best Practices).

However, if you look at the results you published, you will see a query that *DOES* do a lot of physical reads:

SELECT * FROM "ZMM_RFID_TRSPOST" WHERE "MANDT"=? AND "TOPLANT"=? AND "UBNUM" = ? OR "MANDT"=? AND "TOPLANT"=? AND "UBNUM" = ? ....

Which did 18 million physical reads.   If you do a sp_helpindex on that table, I suspect you will not find an index on the keys {MANDT,TOPLANT,UBNUM} which it needs.    Given that all of these are "Z" tables, I wonder if in the old system, no one sync'd the catalog with the indexes that were created on these tables and as a result, during migration, these indexes were not created as they were not in the catalog.   

What you also may want to do is do the same as before (use DBACockpit --> Performance --> Statement Cache) and this time click on the TotalPhysicalReads column to sort by Physical Reads.    Also, expand the query column or at least when posting, show enough that we can see the table names involved.    There is another query at line 5 of the original post that begins:

SELECT "EBELN", "EBELP", "ZEKKN", "VGABE", "GJAHR", "BELNR", ....

Which is also driving a large number of physical reads....but not enough of the query is visible to see even what table it is on - let alone the WHERE clause predicates to see what indexing should have been available.   However, with 14554 executions and 474096550 TotalLogicalReads + 3391082 TotalPhysicalReads, that is 32575 LogicalReads and 233 PhysicalReads per execution...that's a bit much unless the query is returning thousands of rows for every execution.  It is likely that either ASE is using a partial index that isn't very useful for the query as a whole or that there is a lot of data skew driving a table scan for one set of values but that cached plan is then used for others.   Impossible to tell without seeing the query, the indexes and some other data......but it also may not be one of the worst offenders as there may be even worse queries not visible in your output.   One option to consider is that most SAP screens (including DBACockpit) have the ability to output to a MS Excel spreadsheet.   If you get the results, you may want to do that as it is easier to work from vs. screen images and the full query text will likely be there as well.