DisCopy


Friday 10 August 2012

Sybase Replication Server 15 Overview

Sybase Replication Server 15 Overview
Sybase Replication Server is database replication software that moves and synchronizes data across the Servers either From Sybase ASE to ASE or to/from Other RDBMS Servers. The data moves asynchronously. The time it takes to reach the destination or to replicate data depends on the size of the transaction, level of activity in that particular database/schema, the network performance, and last but not the least how your replication server is loaded etc., Usually, on a LAN, for small transactions, this is less than a second.

Replication Server 15.0 supports a number of new ASE 15.0 features:
ü Computed columns
ü Encrypted columns
ü Partitioned tables
ü New datatypes
ü Long identifiers

1)      Computed columns
Computed columns are columns that are defined by an expression, instead of a datatype
For example
create table sales
(order_num char(20), order_date date
order_day_of_week compute datename(dw, order_date))
ü  Replication Server can only replicate materialized computed columns.
·         A materialized computed column is stored in the table
·         Virtual computed columns are not replicated

2)      Encrypted columns
ü  ASE 15.0.1, 12.5.3a, and 12.5.4 support the definition of encrypted columns
For example:
create table cust_table
(cust_id char(15),
credit_card char(20) encrypt)
Replication Server 15.0 can replicate these encrypted columns.

3)      Partitioned tables
ü  ASE 15.0 introduced semantic data partitioning
·         Tables can be partitioned by lists or ranges of key values, or by a hashing algorithm
·         These partitioning methods are supported by Replication Server 15.0
·         In addition, truncate table has been extended to support partition-level truncation

4)      New datatypes
ü  Release 15.0 adds support for new datatypes:
·         Bigint – whole numbers between -263  and 263 –1
·         Unitext – variable-width, nullable Unicode datatype

5)      Long identifiers
ü  The limit of 30 bytes for selected database and replication object names (identifiers) is extended to 255 bytes for Replication Server version 15.0. Longer identifiers are supported for these objects:
·         Tables and columns
·         Stored procedures and parameters
·         Function strings
·         Replication definitions – including table replication definitions, function replication definitions, and database replication definitions
·         Publications and articles

Tuesday 24 July 2012

Sybase ASE Performance Tuning précis...


Performance tuning is a continuous process in any database environments. Many organizations spend more time and resources on tuning the database than on the original development because of data growth and usage on top of a poor database design. 80% of performance issues can be avoided with a proper database design. In addition, developing Transact-SQL code is very different from developing front-end applications code and middle-tier components. Transact-SQL code developed by front-end or middle-tier experts can often be optimized depending on the transparency of the code. Finally, there is a lot of requirement and necessity for optimizing databases when the volume of operations grows and the application that worked great with 5-10 users and 100-200GB no longer lives up to tera bytes of data and 1000s of users' expectations.  Yes as all of us aware scalability is the root cause.
A common place where most people like to start tuning databases is tuning the Server configuration. Generally, Most developers that are unhappy with the application performance will demand adding more memory to the server and reconfiguring the dynamic configuration parameters. But, extending the memory or keep on changing configuration parameters will only help optimizing the performance up to a certain point. In other words, if you keep adding memory and do not tune the application or t-sql code in any other way, you will reach the point where additional memory or re-configuration produces marginal or no performance improvement. 
It is also important to realize that improvement in one area often means compromising others. If you can optimize 90% of the critical queries by slowing down the performance of other 10% it might be well worth of your time. Sometimes you can improve the performance of online transactions by increasing the response time with the expense of reducing concurrency or throughput. Therefore it is important to determine the application performance requirements.  If we improve the performance of selects obviously we compromise DML statements/transactions (may be adding indexes).
It is common to spend more time identifying the cause of the problem then actually troubleshooting and fixing it. If all other areas of application are working properly and you can be sure there is a problem with the database code, then you need to investigate your code modules and decide which one is causing problems. Many times improvement in only one stored procedure or trigger can fix most of the issues.
The Optimizer in the Adaptive Server takes a query and finds the best way to execute it. The optimization is done based on the statistics for a database or table. The optimized plan stays in effect until the statistics are updated or the query changes. You can update the statistics on the entire table or by sampling on a percentage of the data.
Adaptive Server can generate an abstract plan for a query, and save the text and its associated abstract plan in the sysqueryplans system table. Abstract plans provide an alternative to options that must be specified in the batch or query in order to influence optimizer decisions. Using abstract plans, you can influence the optimization of a SQL statement without having to modify the statement syntax.
Adaptive Server locks the tables, data pages, or data rows currently used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data within and across transactions. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time.
Carefully considered indexes, built on top of a good database design, are the foundation of a high-performance Adaptive Server installation. However, adding indexes without proper analysis can reduce the overall performance of your system. Insert, update, and delete operations can take longer when a large number of indexes need to be updated.

What to observe - high level (We have many many :) ... but top 10)
  1. Server error logs
  2. OS error logs
  3. Configuration files
  4. sp_monitorconfig output
  5. sp_sysmon reports
  6. sp_object_stats reports
  7. sp_helpdb output
  8. options and traceflags used
  9. ER diagram or database model
  10. and all applications running on the database.

Monday 23 July 2012

Sybase Production DBA FAQ..

1.    How many devices are created during installation? Which system databases are stored on these devices?
Answer:  Three devices are created during installation: the sysprocsdev device, which stores the sybsystemprocs database, the systemdbdev device which stores the sybsystemdb, and the master device, which stores the master, model, and tempdb databases.

2.    How can you verify that a server is running?
Answer:  You can verify a server is running by using the showserver command in Unix/Linux or in a Windows Command Prompt window by using the command  ‘net start’.  You can also go to Windows Services in the control panel.

3.    What is the purpose of the interfaces/sql.ini file?
Answer:  The interfaces/sql.ini file is an “address book” that client and servers use to determine which address they should use when communicating with each other.

4.    Given a server named SYBASE, how is the SYBASE.bak file different from a file named SYBASE.001?
Answer:  The SYBASE.bak file is a backup made at startup, and it contains a copy of the configuration file at startup. The file named SYBASE.001 is a copy of an earlier version of the configuration file made when the configuration file was modified via sp_configure. It could be the same as the backup file, or it could be a much earlier copy of the configuration file.

5.    How should you configure a server that has two different processing requirements, such as one that is used for OLTP processing during the day and DSS processing at night?
Answer:  You should probably create two different configuration files, one for each period of time, and then restart the server with the given configuration file at the start of each period.

6.    What is the housekeeper?
Answer: The housekeeper is a task that becomes active when no other tasks are active. It writes dirty pages to disk, reclaims lost space, flushes statistics to systabstats, and checks license usage.

7.    Name three configuration parameters that use significant amounts of memory.
Answer:  number of user connections, number of open databases, number of open indexes, number of open objects, number of locks, number of devices.

8.    What two system databases are involved in user database creation?
Answer:  The master and model databases are involved in database creation. The contents of the model database are copied to form the contents of the new database, and information is recorded in master.

9.    Why is it a good practice to place the log on a separate device?
Answer:  Lets you back up the transaction log separately, decreases the likelihood that both the data and the log will be damaged at the same time, helps in monitoring space usage and managing the space, allows disk mirroring of the log for maximum uptime and up-to-the-minute recovery.

10.  What sorts of problems can occur if tempdb is too small?
Answer:  tempdb is used both by users creating temporary tables and by server processes that need to sort data, group data, store intermediate results, and so on. If tempdb is too small, users and server processes will have insufficient resources to process their queries rapidly, and their queries will fail.

11.  How can a threshold help you monitor space usage?
Answer:  A threshold can help monitor space usage by executing a stored procedure with a warning message when there is a certain amount of space left on a segment.

12.  What performance benefits can be gained by configuring multiple temporary databases?
Answer:
·         Distributes the workload across multiple tempdbs, Reduces system table contention
·         Reduces resource contention
·         Load balancing can be done automatically
·         Heavy users or applications can be bound to their own temporary database

13.  How is a user-defined role different from a group assignment?
Answer:  User-defined roles are system-wide. Multiple user-defined roles can be given to a single user. Once defined, roles can be dynamically turned off and on by the user, thereby supporting more flexibility than with groups. A user-defined role can also include other user-defined roles.
User-defined roles can take advantage of creating:
·         Hierarchical access
·         Access defined for a specific application.

14.  What is the primary purpose of a checkpoint?
Answer:  The primary purpose of a checkpoint is to reduce the amount of time needed for recovery by writing all dirty pages to disk.

15.   What is a “dirty page”?
Answer:  A dirty page is a page that has been modified in data cache, but the modifications have not yet been written to disk.

16.  Why does the server need to execute the automatic recovery process?
Answer:  The server needs to execute the automatic recovery process because it must ensure that all transactions in process during or shortly before the last shutdown are either rolled forward or rolled back as a unit.

17.  Why are Adaptive Server transaction logs known as “write-ahead logs”?
Answer:  Adaptive Server transaction logs are known as write-ahead logs because log pages are written to disk before the corresponding data and index pages.

18.  During recovery, when does the server “roll forward” a transaction? When does it “roll back” a transaction?
Answer:  The server rolls forward transactions that are committed in the log but are not fully reflected on data pages. The server rolls back a transaction that is incomplete or is rolled back in the log but is partially or fully reflected in the data pages.

19.  There are four circumstances under which pages in cache are written to disk. Name them?
Answer:  Pages in cache are written to disk:
1) when the pages must be aged out,
2) when a checkpoint occurs,
3) when the housekeeper writes pages to disk,  or
4) when a commit tran is executed. Note that the first three circumstances write log and data pages to disk, but the fourth circumstance writes only log pages to disk.

20.  What is the last-chance threshold?
Answer:  The last-chance threshold is a point in a database’s log segment determined by the server. When the number of used pages crosses this point, the server executes sp_thresholdaction and suspends or aborts all transactions in progress.

21.  What happens if the last-chance threshold is crossed and there is no sp_thresholdaction procedure?
Answer:  The server sends an error to the error log if a database has no sp_thresholdaction procedure and the last-chance threshold is crossed.

22.  How can a long-running transaction limit the effectiveness of log truncation?
Answer:  Truncation can remove only inactive pages from the log. If a transaction is long-running, it extends the active portion, thereby limiting the number of inactive pages that can be removed from the log.

23.  What is the name of the system table that stores system error messages?
Answer:  master..sysmessages

24.  How can you identify that a process is a system process?
Answer:  A system process can be identified by using the sp_who output. System processes have a NULL login name and no hostname.

25.  What kinds of processes cannot be killed? What kinds of processes should be killed with extreme caution?
Answer:  You cannot kill a system process, and you also cannot kill your own process. Processes that are “infected” or “background” should be killed with extreme caution, if at all.

Sybase DBA Roles and Responcibilities...Monitoring the System

A database administrator (short form DBA) is a person responsible for the installation, configuration, upgrade, administration, monitoring and maintenance of databases and make sure all the databases are up and running, users/applications can connect to the databases and access the relevant data. The DBA role is very responsible, respectful and stressful.
The role includes the development and design of database strategies, planning recoveries of system databases, sound backup schedules, users-logins control, monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, co-ordinate and implement security measures to safeguard the database.

System Administrator tasks include:
1.             Installing and configuring  Sybase servers and application tools
2.             Managing disk storage and capacity planning i.e. Allocating system storage and planning future storage requirements for the database system.
3.             Enrolling users and maintaining system security and Controlling and monitoring user access to the database i.e. Creating logins in ASE, adding users to databases, granting permissions on objects to SQL Server users
4.             Creating user databases on demand, expand/alter databases as and when data grows, and granting ownership of databases
5.             Monitoring SQL Server's automatic recovery procedure
6.             Planning for backup and recovery of databases, Backing up and restoring the databases.
7.             Diagnosing system problems i.e. monitoring error logs, processes, locks, blocking processes, resolving and reporting them as appropriate
8.             Fine-tuning SQL Server by changing the configurable system parameters and dboptions
9.             Monitoring and optimizing the performance of the database i.e. Fine-tuning T-SQL queries by adding/modifying indexes or modifying the code.
10.         Scheduling database consistency checks
11.         Scheduling statistics update for dynamic/hot tables
12.         Generating optdiag reports and scheduling defragmentation of tables
13.         Transferring bulk data between SQL Server and other software programs
14.         Contacting database vendor for technical support
15.         Documenting the process, known errors, resolving techniques and contact info.

Monitoring the System
I would like to concise about monitoring the system which is the most vital task of any DBA, as most of other tasks need to be resolved seldom and monitoring is the task to be often need to perform.
 
1.      Monitoring Errors
a)      Error Log
The error log is a text file containing error messages. Its default location is $SYBASE/$SYBASE_ASE/install directory.
Messages are appended to the error log: Each time the server is started, whenever there is a fatal error or a kernel error.
Information in the error log appears in this format:
engine fid spid datetime sender message
Example:
00:00000:00000:2006/04/12 20:45:12.95 server Configuration Error: Configuration file, 'C:\sybase\SYBASE.cfg', does not exist.

b)      Error Messages
A error message is a message sent to the user and/or the error log when an error has occurred Error messages are stored in master..sysmessages
Examples:
delete titles from where type = "psychology"
-
Server Message: Msg 156, Level 15, State 2: Line 1: Incorrect syntax near the keyword ‘where’.

c)      Where do error messages go?
                                i.            User error messages (severity level 10 to 16) go to the user’s screen (or the application). In some cases, these messages also go to the error log.
                              ii.            Hardware and software error messages (severity level 17 and 18) may go to the user’s screen, or the console screen (of the machine where Adaptive Server was started), and the error log, depending on the specific nature of the error.
                            iii.            Fatal error messages (level 19 to 26) go to the error log.

d)     Monitoring the Error Log
                                i.            Look for error messages of severity level 17 and above. Users may not report errors of severity 17 and 18 if their work is not interrupted. Set up a routine that browses the error log searching for specific error numbers or levels, When a message does not indicate what to do about the problem, refer to the Troubleshooting and Error Messages Guide or call Sybase Technical Support.
                              ii.            The error log grows constantly and needs to be pruned regularly
·  Shut down the server first
·  Make a copy of the error log before you alter it


2.      Monitoring Processes
a)      Process
A process is a task that is being carried out by Adaptive Server Initiated by a user giving a command or by the server itself Each process is given a unique server process ID number, also known as a “spid”.
A process can belong to a “family of processes”, In that case, it is also given a family ID, also known as a “fid”
There are 3 types of processes
                                i.            System
                              ii.            User
                            iii.            Worker

b)      View Process Information
Syntax:
sp_who
Example:
sp_who
-
fid spid status                                                    loginame  hostname   blk_spid   cmd
--- ---- ------                                                        --------    --------      ------         ---
0                                                    1     recv sleep  bird      jazzy             0        AWAITING CMND
0                                                    2     sleeping    NULL                           0        NETWORK HANDR
0                                                    3     sleeping    NULL                           0        MIRROR HANDLR
0                                                    4     sleeping    NULL                           0        CHKPT SLEEP

c)      Killing a Process
A process can be killed, or terminated, by the System Administrator
            The most frequent reason to kill a process is because it is interfering with other users and the person responsible for running it is not available or The process may be holding locks or otherwise occupying needed system resources.
The server lets you kill a process only when it can:
·         Cleanly roll back any incomplete transactions
·         Release all system resources
*  If the process is part of a family, all processes in the family are killed simultaneously.

Syntax:
kill spid
Example:
kill 29
go
Immediate and Relatively Safe Kills

Status         
Condition
recv sleep     
Waiting on a network read
send sleep     
Waiting on a network send
alarm sleep    
Waiting on an alarm (such as waitfor delay “5:00”)
lock sleep     
Waiting on a lock acquisition
sync sleep     
Waiting on a synchronization message from another process in the family
Runnable       
In the queue of runnable processes
Running        
Actively running on one of the server engines
log suspend    
Process suspended by reaching the last-chance threshold on the log
wait sleep     
Waiting for disk I/O



Delayed or Not Recommended Kills

Status
Condition
What happens If you Kill?
sleeping
Waiting on a disk I/O or some other resource
Killed when it “wakes up” reboot probably required
infected
Server has detected serious error condition (extremely rare)
kill not recommended; server bouncing is required
background
A process, such as a threshold  process, run by the server rather than by a user process
Immediate, but sysprocesses should be checked to determine what the process is doing.


3.      Monitoring Licenses
a)      License Use Monitor
License Use Monitor allows System Administrators to monitor the number of licenses in use on the server
Ø  Used to ensure the number of licenses in use does not violate the license agreement
Ø  License Use Monitor does not enforce any aspect of the license agreement
Ø  It merely sends a message to the error log
Example:
  Exceeded license usage limit. Contact Sybase Sales for additional licenses.

b)      License Use Monitor and the Housekeeper
Ø  License Use Monitor is part of the housekeeper chores task
- Recall that the housekeeper is a task that the server activates automatically when there are no other tasks to process.
Ø  The housekeeper does three tasks in a cyclical fashion
                                i.            Monitoring license use is one of the three housekeeper tasks. When the housekeeper executes the license monitor task, it:
                              ii.            Checks to see how many licenses are in use
                            iii.            Compares the value to the last maximum value recorded
If the value is the same or less, the housekeeper does nothing. If the current value is greater, the housekeeper:
Ø  Sets the current value as the new maximum value
Ø  Sends a message to the error log if the value is in excess of the number of licenses in the license agreement.


4.      Monitoring System Performance
a)      Generating Statistics
Ø  Statistics must be updated on a regular basis to ensure the best performance from the ASE.
Ø  Initial statistics are generated when indexes are created. Create index only generates leading column statistics. Additional statistics may need to be added for minor index columns and un-indexed join columns.
Ø  To update statistics or create additional statistics use the update statistics command.

Syntax and functionality:
update statistics table_name [[index_name |[(column_list)]]
This command creates or updates statistics on:
                                i.            Leading column of the index if index_name specified
                              ii.            First column in the column list if column_list specified
                            iii.            Leading columns of all indexes if only table_name specified

update index statistics table_name [index_name]
This command creates or updates statistics for all columns in index_name or all columns in all indexes if only table_name specified.

b)     Flushing Statistics from Memory
ü  Statistics found in systabstats are maintained in memory structures for each table/index.
ü  Reduces the overhead and contention on systabstats.
ü  Statistics are flushed periodically by the housekeeper task
ü  optdiag flushes statistics before displaying output.
ü  Flush statistics with sp_flushstats prior to querying systabstats to ensure that the statistics are accurate.
Syntax:
sp_flushstats object_name


c)      Monitoring Lock Contention
Syntax:
sp_object_stats "HH:MM:SS",  N [, database_name]
Displays top 'N' tables with the highest contention in the databases.
Example:
sp_objectstats "00:02:00",10, pubs2_db

Prints lock statistics for tables and indexes, and indicates the potential need to change lock scheme.

sp_object_stats
Output for titles table:
Sample output for titles, uses datapages locking: Object Name: pubs2_db..titles
(dbid=7, objid=208003772,lockscheme=Datapages)

Page Locks                                              SH_PAGE          UP_PAGE           EX_PAGE
----------                                                    --------                 --------                 -------
Grants:                                                          94488                   4052                 4828
Waits:                                                                532                     500                   776
Deadlocks:                                                            4                          0                      24
Wait-time:                                              20603764 ms   14265708 ms   2831556 ms
Contention:                                                     0.56%              10.98%            13.79%


d)     sp_monitor
Displays high level server statistics
Example:
sp_monitor

last_run                                                                                current_run       seconds
-------------------------- -------------------------- -----------
Aug 15 2005                                                                        2:43PM              Aug 15 2005              2:43PM  1

(1 row affected)
cpu_busy                                                                              io_busy              idle
----------------- ------------------------- -------------------------
390(0)-0%                                                                           0(0)-0%             1816061(0)-0%
packets_received                                                                packets_sent     packet_errors
------------------------- ------------------------- ------------------
219518(0)                                                                           427695(0)         0(0)
total_read                                                                             total_write         total_errors               connections
------------------- ------------------- ------------------- ---------
14597(0)                                                                              38290(0)           0(0)             86(0)
(return status = 0)


e)      MDA Tables
Monitoring and Data Analysis Tables
              Allow for real time monitoring including information on
ü  IO activity
ü  CPU usage
ü  What's currently in memory
ü  Evaluating currently running code and stored procedure
ü  Since startup statistics
ü  Requires a separate install
ü  Minimal resource requirements
ü  Read only tables


f)       Monitoring System Performance
The sp_sysmon system procedure provides:
ü  Snapshot of server activity for an interval of time
ü  Statistics on a wide range of system activities 
               Run sp_sysmon:
ü  When performance is sluggish
ü  Before and after any tuning activity
ü  If possible, on a daily basis
            sp_sysmon is a useful benchmarking tool, but output is voluminous and requires some interpretation.

Partial syntax:
sp_sysmon interval [, section [, applmon] ]
Example:
sp_sysmon "00:00:30", pcache
Parameters:
            interval: Specifies the time period for the sample in "HH:MM:SS"
            section : The abbreviation for one of the output sections
            applmon : Application CPU, I/O priority changes, resource limit violations.
            Can report by application (appl_only), login (no_appl), or both (app_and_login).