Archive for the ‘Start Sybase’ Category

dbcc checkdb vs dbcc checkstorage

June 25th, 2012 No comments

Checkstorage will detect allocation errors, it is a reasonable substitute for dbcc checkalloc (checkstorage will report a fair number of issues that checkalloc will not, many of them trivial things, and checkalloc may be able to detect a few odd conditions
checkstorage does not.

What checkstorage won’t catch are issues with index tree (keys out of order, index entries that point to missing rows, rows
that are not indexed).  So checkstorage is not a good substitute for checkdb.

Source :www & sybooks.

Performance Issue

June 24th, 2012 No comments

Few days back I have faced performance issue in one of our prod data server. I would like to share here.

User was running batch for pushing 90000 rows in a database and batch was not moved from last 1.5 hrs.

On login in the server I found response of the server was not good, it was taking more time to execute a simple query as usual. In first glance, it was looking like user job is hogging the resources, as user job spid was in syslogshold and not moved from long time.

We do some analysis and finally found the cpu usage for server was 100%. ( I used sp_monitor). I concluded that this high cpu usage is slowing down the server performance.

The next task was finding the query which was taking more cpu time. As server was on 15 version, I ran the below sql querry for mon tables for getting the high cpu usage.

select top 10  s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStatement s, master..monProcessSQLText t where s.SPID = t.SPID order by s.CpuTime DESC

We asked application team to check the reported spid and if possible, please abort the tran. There was select queries which were  taking maximum cpu . As they requested us to kill, we aborted/killed from data server.

After few seconds, data server cpu started fluctuating from 50 to 100% and finally it was below 50%.

Application batch of 10K inserts moved very quickly and finally issue resolved.

You can get full details on MDA queries @


SAP Becomes Fastest-Growing Vendor in Relational Database Market

June 8th, 2012 No comments

WALLDORF, Germany, June 7, 2012 /PRNewswire/ — SAP AG (NYSE: SAP) today announced it is the fastest-growing among the top vendors in the relational database management systems (RDBMS) market, according to a new report(1) from IDC.


Source & Complete  Report @

Categories: ASE, Database, News, SAP, Start Sybase Tags: , , ,

Inserting data into a data-only-locked heap table

January 5th, 2012 No comments

When users insert data into a data-only-locked heap table, Adaptive Server tracks page numbers where the inserts have recently occurred, and keeps the page number as a hint for future tasks that need space. Subsequent inserts to the table are directed to one of these pages. If the page is full, Adaptive Server allocates a new page and replaces the old hint with the new page number.

Blocking while many users are simultaneously inserting data is much less likely to occur during inserts to data-only-locked heap tables. When blocking occurs, Adaptive Server allocates a small number of empty pages
and directs new inserts to those pages using these newly allocated pages as hints.

For datarows-locked tables, blocking occurs only while the actual changes to the data page are being written; although row locks are held for the duration of the transaction, other rows can be inserted on the page. The row-level locks allow multiple transaction to hold locks on the page.

If conflicts occur during heap inserts
Conflicts during inserts to heap tables are greatly reduced for data-onlylocked tables, but can still take place. If these conflicts slow inserts, some workarounds can be used, including:

• Switching to datarows locking, if the table uses datapages locking
• Using a clustered index to spread data inserts
• Partitioning the table, which provides additional hints and allows new pages to be allocated on each partition when blocking takes place

Inserting data into an allpages-locked heap table

January 5th, 2012 No comments

When you insert data into an allpages-locked heap table, the data row is always added to the last page of the table. If there is no clustered index on a table, and the table is not partitioned, the sysindexes.root entry for the heap table stores a pointer to the last page of the heap to locate the page
where the data needs to be inserted.

If the last page is full, a new page is allocated in the current extent and linked onto the chain. If the extent is full, Adaptive Server looks for empty pages on other extents being used by the table. If no pages are available, a new extent is allocated to the table.

Conflicts during heap inserts
If many users are trying to insert into an allpages-locked heap table at the same time, each insert must
wait for the preceding transaction to complete.

This problem of last-page conflicts on heaps is true for:
• Single row inserts using insert
• Multiple row inserts using select into or insert…select, or several insert statements in a batch
• Bulk copy into the table

Some workarounds for last-page conflicts on heaps include:
• Switching to datapages or datarows locking
• Creating a clustered index that directs the inserts to different pages
• Partitioning the table, which creates multiple insert points for the table, giving you multiple “last pages” in an allpages-locked table

ASE database for SAP ERP

December 20th, 2011 No comments

Hello all,

These are copilataion for Sybase ASE on SAP from the Rob’s Blog :

Read Full Story :

  • SAP has released Business Suite on ASE version 15.7.
  • All SAP application data resides in a single ASE database. There is another small database for use by SAP tools.
  • The ASE database uses a 16KB page size.
  • For ERP only (i.e. not counting CRM and the other Business Suite modules), the database contains about 80,000 tables and 170,000 indexes. This is because SAP ERP has many features and functions, all with their own set of tables. SAP customers typically run only a subset of all those functions so in practice a large part of those 80,000 tables will always remain empty.
  • All SAP tables use datarowslocking (there is an interesting historical dimension.
  • All tables names are in uppercase; some table names contain special characters, like the slash character in “/BCV/C_QATTR” (I don’t have a clue what that name means, BTW)
  • Apart from the tables, there are also about 10,000 views. No stored procedures or triggers are used.
  • SAP makes heavy use of dynamic SQL (also known as “prepared statements”).
  • Many tables have a text or image column.
  • All tables are owned by one database user (and that’s not the dbouser).
  • The ASE database is accessed through ODBC.
  • SAP makes frequent use of the built-in ASE Job Scheduler (originally added in ASE 12.5.1).
  • The ASE server uses Unicode with the utf8 character set.

Guidelines for improving I/O performance

December 8th, 2011 No comments

The major guidelines for improving I/O performance in Adaptive Server are as follows:

• Spreading data across disks to avoid I/O contention.

• Isolating server-wide I/O from database I/O.

• Separating data storage and log storage for frequently updated databases.

• Keeping random disk I/O away from sequential disk I/O.

• Mirroring devices on separate physical disks.

• Partitioning tables to match the number of physical devices in a segment.

Logical or Database Devices and Physical Devices

December 7th, 2011 No comments

Distinctions between logical or database devices and physical devices:
• The physical disk or physical device is the actual hardware that stores the data.

• A database device or logical device is a piece of a physical disk that has been initialized (with the disk init command) for use by Adaptive Server. A database device can be an operating system file, an entire disk, or a disk partition.

• A segment is a named collection of database devices used by a database. The database devices that make up a segment can be located on separate physical devices.

• A partition is block of storage for a table. Partitioning a table splits it so that multiple tasks can access it simultaneously. When partitioned tables are placed on segments with a matching number of devices, each partition starts on a separate database device.

sp_helpdevice –> To get information about devices
sp_helpsegment –> To get information about segments
sp_helpartition –> To get information about partitions.

Sybase IQ 15 Best Practices Guide

December 4th, 2011 No comments

What’s in YOUR Architecture?

December 4th, 2011 No comments

What’s in your architecture?

Probably not enough if you’re using just data modeling to integrate all the components of your enterprise, especially your information. To lay the
foundation of this paper, let’s start from the same level of understanding: the need for a common approach to managing all the aspects of information to
enable rapid business performance in the 21st century economy.

Competition in the marketplace is always fierce. To stay abreast, organizations must constantly analyze their customer needs and expectations, enhance
or innovate their business processes, and deliver products and services that create exceptional customer value. Organizations also need to be customercentric to forge long-term relationships with clients and consumers. Only organizations that are agile enough to respond to volatile market conditions
with innovation, expedited time-to-market processes, and reduced costs can differentiate themselves from the competition. Such agility occurs when a
company’s IT operations are closely aligned with its business operations. IT needs to understand business to implement technologies and applications that
support the current and future business goals.

Read Full Article @