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).