Archive

Archive for April, 2012

Tuning with Data Cache

April 26th, 2012 No comments

Source: Sybooks

 

Below Data Cache configuration can improve the performance:

• Configure named data caches to be large enough to hold critical tables and indexes. This keeps other server activity from contending for cache space and speeds queries using these tables, since the needed pages are always found in cache. Can configure these caches to use the relaxed LRU replacement policy, reducing the cache overhead.
• To increase concurrency, bind a hot table to one cache and the indexes on the table to other caches.

 

• Create a named data cache large enough to hold the hot pages of a table where a high percentage of the queries reference only a portion of the table.

For example, if a table contains data for a year, but 75% of the queries reference data from the most recent month (about 8% of the table), configuring a cache of about 10% of the table size provides room to keep the most frequently used pages in cache and leaves some space for the less frequently used pages

 

• Assign tables or databases used in decision-support systems (DSS) to specific caches with large I/O configured.
This keeps DSS applications from contending for cache space with OLTP applications. DSS applications typically access large numbers of sequential pages, and OLTP applications typically access relatively few random pages.

 

• Bind tempdb to its own cache to keep it from contending with other user processes. Proper sizing of the tempdb cache can keep most tempdb activity in memory for many applications. If this cache is large enough, tempdb
activity can avoid performing I/O.

 

• Bind text pages to named caches to improve the performance on text access.
• Bind a database’s log to a cache, again reducing contention for cache space and access to the cache.

Categories: ASE Tags:

Statement Cache and SQL Query Plan

April 26th, 2012 No comments

As procedure cache stored the query plan of stored procedures similarly statement cache saves SQL text and SQL query plan previously generated for ad hoc SQL statements, enables ASE to avoid recompiling incoming SQL that matches a previously cached statement.

 

Basically Statement Cache is a part of Procedure Cache, when enabled the statement cache reserves a portion of procedure cache.

Categories: ASE Tags:

Procedure Cache and Query Plan

April 26th, 2012 1 comment

ASE doesn’t contain any table which stores the Query Plans of stored procedure. Instead, Query Plans gets stored in procedure cache that is the part of max memory.

ASE maintains MRU/LRU (most recently used/least recently used) algorithm. Stored procedures generally preferred over separate SQL statements because when users execute stored procedure, Adaptive server search procedure cache for existing query plan. If it is available then execution begins.

If Query plan is not available or all copies are in use, if multiple users are executing same stored procedure at a time then multiple copies of query plan will be available in procedure cache until size of cache is supporting, then query tree for the procedure is read from the sysprocedures table  .  Then query tree is then optimized, based on the parameters passed to the procedures and converted into query plan and then execution begins.

 

Categories: ASE Tags:

DBA Sidekick(Sybase) Andriod App on Google Play

April 25th, 2012 No comments

Source : Link

Description
Key Features:

1. Search Sybase Error Code and Adaptive Server Anywhere SQLCODEs to find error description
2. Partial search available
3. Negative error code need not be supplied for Adaptive Server Anywhere SQLCODEs

Categories: ASE, News Tags: , ,

Sybase ASE Brainstorming Question Series

April 24th, 2012 No comments

Hi All,

We are preparing the Sybase ASE Brainstorming Question Series.

In this series we will put the questions topic wise as well as hands on (day-to-day troubleshooting) questions.

For all questions, We will also include the answers as well :)

Please forward your advices/suggestions on Link .

http://wp.me/P1RFmy-nI

We are waiting to hear from your side.

Thanks.

Team,sybaseblog.com

Categories: ASE Tags:

Database device and segment

April 21st, 2012 No comments

For Adaptive Server, devices provide a logical map of a database to physical storage, while segments provide a logical map of database objects to devices.

Adaptive Server keeps track of the various pieces of each database in master.dbo.sysusages. Each entry in sysusages describes one fragment of a database. Fragments are a contiguous group of logical pages, all on the same
device, that permit storage for the same group of segments. Fragments are also known as “disk pieces.”

Source: Sybooks.sybase.com

Categories: ASE Tags:

we are also changed..

April 20th, 2012 No comments

Hi Folks,

As SAP disclosed its database plans on Apr 10 2012 press conf and If you go through the Sybase point of view for Sybase Technologies with SAP Real Time Data Platform, Its certainly a great move in technology front for ASE,IQ,Rep Server & ASA.

SAP HANA is superb innovation from SAP and it’s integration with Sybase Data Management Products will put these product miles ahead with others.

Definitely It is very good news for Sybase folks!

Now we are also changed our tag-line for showing our support  with this vision of SAP Sybase..& keep your blog in sync with same.

to
Happy Learning Sybase!
Team
sybaseblog.com
Categories: ASE Tags:

SAP Real Time Data Platform

April 20th, 2012 No comments

Guys,

 

Please see the SAP Real Time Data Platform from Sybase Point of View ::

 

Link ::http://www.sybase.com/detail?id=1098149

Slides : http://www.sybase.com/files/Product_Overviews/DM-Vision-and-Strategies-SAP-Next-Gen-DW.pdf

 

Categories: ASE, News Tags:

Stock Market Opening Timings With Reference To India Time

April 18th, 2012 1 comment

North and South America

S&P – US : 19:00 PM

Mexican Bolsa -Mexico : 18:00 PM

Brazil Bovespa : 19:30 PM

NASDAQ : 7:00 P.M.

NYSE: 8:00 P.M.
Europe
FTSE100 -UK : 21:10 PM
DAX – Germany : 21:00 PM
CAC 40 – France : 21:00 PM
Asia
Shanghai Composite – China : 07 :00 AM
Hang Seng – Hong Kong : 07 :00 AM
Nikkei – Japan : 07 :00 AM

Categories: ASE Tags:

Relation between sysdevices, sysdatabases and sysusages

April 15th, 2012 1 comment

Pre 15.x Version

===============

1. Virtual Page Number: Virtual Page number is unique across the server, always in 2k size. Does not depend upon server page size.

2. Logial Page number : Unique across a database. Depends upon the page size of server ( 2k, 4k, 8k and 16k)

3. vdevno from sysdevice as vedno(low/2power24)&255

4. The high Order four bits of sysusage.vstart stores the virtual device number and can be calculated as vdevno=(vstart/2pow24)&255

sysdevices.low : Starting vitual page for a devices and generally calculated as vedvno*2power24

sysdevices.high : Last vitual page of the device allocation

Device Size: (sysdevices.high – sysdevices.low+1)

sysdatabase.dbid : Database Id for a database.

sysusages.vstart : is the vitual page number from where the database fragment starts

sysusages.lstart : Is the start of the logical page number

sysusages.size : Number of contiguous database (logical) pages

sysusages.dbid : Database id for  fragment.

 vstart of a database fragment is lying between the low and high of device used.

 

Version 15.x

===============

1. Virtual Page Number: Virtual Page number is unique across the device, always in 2k size. It does not depend upon server page size.

2. Logial Page number : Unique across a database.

3. Now, device identification number is stored in the vdevno column and no longer as part of the high or low column.

and also no longer as part of the sysusage.vstart column.

sysdevices.low : Generally Zero , block offset of virtual page in 2K page if there is offset with vstart parameter of disk init.

sysdevices.high : Last virtual page of the device allocation

sysdevices.vdevno : Virtual device number for a device (for master device =0 and maximum value is defined by number of devices config parameter)

Device Size : (sysdevices.high – sysdevices.low+1)

sysdatabase.dbid : Database Id for a database.

sysusages.vstart : is the virtual page number from where the database fragment starts in a device specified by vdevno

sysusages.lstart : Is the start of the logical page number of the database.

sysusages.size : Number of contiguous database (logical) pages

sysusages.dbid : Database id for  fragment.

sysuages.vdevno : Vdevno of device on which database fragment is created.

These global variable always represents as

@@maxpagesize – logical page size

@@pagesize – virtual page size

 

Source : sybooks

Categories: ASE Tags: