Maintenance Tasks – Update Statistics

Update Statistics

It is going to generate a histogram one snapshot of a information of a table or index ,its going to generate a query plan .

• It stores information of a table(s) and its sizes.
• What all indexes it has for a table .

Tables Effected

Two system tables which are effected whenever running update statistics are :

• sysstatistics
• systabstats

Accurate statistics are essential for query optimization

Query Optimization

Generates a query plan that best suites to generate accurate statistics

Importance of statistics

ASE is cost based optimizer uses tables,indexes,columns named in a query to estimate query costs

• It chooses the access methods which determines the least cost.
• Some Statistics like no of pages which determines the least cost.
• Other statistics such as histogram on columns are updated only when we run update statistics.
• Update statistics command updates column related statistics such as histogram and densities.

Index

Faster way of retrieving data from a given table.

• It is a combination of distribution of keys in a sorted order.
• Update statistics needs to be updated on those columns where the distribution of keys in the index changes in the way that effect the use of indexes for query’s .
• Update statistics command helps ASE to make the best decision about which index to be used when it process a query by keeping it up to date about the distribution of key values in the indexes.
• It goes for a table scan to know which index to be used.
• When update statistics run in business hours

• Uuses max C.P.U utilization
• Uses of procedures and data cache
• Uses I/O condition as well
• So it is recommended in off business hours

• Update statistics is usually run on all user tables to increase query performance .
• When we run update statistics the statistics information is stored in sysstatistics and systabstats tables.

Syntax : update statistics <table name >

update statistics <table name > <index name> {sp_helpindex shows how many indexes are there for a particular table }

update statistics <table name ><column list>

delete statistics <table name >

Example :

Three ways to generate the update statistics

• update statistics runs on data pages
• update index generates statistics for loading index column of a table / index page .
• Update all statistics generates statistics for both data and index page .

Syntax : update index statistics <table name>

update all statistics <table name>

*** After running update statistics we need to run sp_recompile table name ,this will compile the objects to get the latest statistics information.

Maintenance Tasks – REORG

Reorg [Reorganizes]

The Reorg command reorganizes the use of table space and improves performance. It is garbage collector process which will run only on DOL(Data Only Locking) tables.

It performs the following tasks :

• Takes on exclusive table lock
• Copies data from Old to New pages
• De-allocates old data pages
• Rebuilds clustered and non-clustered indexes against new data pages
• Commits all open transactions
• Releases locks on system table

Types of reorg are :

• Reorg rebuild
• Reorg Forwarded_rows
• Reorg reclaim_space
• Reorg Compact

Reorg rebuild

• undos row forwarding and reclaims unused page space

Syntax : regorg rebuild tablename

Example :

• Before we run reorg rebuild the dboption “select into bulkcopy should be enabled ”
• There should be additional disk space equal to the size of the table and its indexes
• Change the locking scheme of a table to run the reorg rebuilt

Reorg Forwarded_rows

• will undo row forwarding

Syntax : reorg forwarde_rows<table name>

Example :

Reorg reclaim_space

• Try's to reclaim the space
• Reclaims unused space left on the page as a result of deletion and row shortening update

Syntax : reorg reclaim_space <tablename> <index no> with resume time = no of minutes

Here index no is optional

Example :

Reorg Compact

• It will do both reclaim the space and undo the row forwarding

Syntax : reorg compact <table name>

Example :

• The utility used to check what reorg is used is optdiag
• If clustered ratio>0.9 then we need to run reorg rebuild

Hotspots

Hot spots

Hot spots occur when all updates take place on a certain page, as in an allpages-locked heap table, where all inserts happen on the last page of the page chain.

For example, an unindexed history table that is updated by everyone always has lock contention on the last page. This sample output from sp_sysmon shows that 11.9% of the inserts on a heap table need to wait for the lock:

Last Page Locks on Heaps

Granted 3.0 0.4 185 88.1 %

Waited 0.4 0.0 25 11.9 %

Possible solutions are:

• Change the lock scheme to datapages or datarows locking.

• Since these locking schemes do not have chained data pages, they can allocate additional pages when blocking occurs for inserts.

• Partition the table. Partitioning a heap table creates multiple page chains in the table, and, therefore, multiple last pages for inserts.

• Concurrent inserts to the table are less likely to block one another, since multiple last pages are available. Partitioning provides a way to improve concurrency for heap tables without creating separate tables for different groups of users.

• Create a clustered index to distribute the updates across the data pages in the table.

• Like partitioning, this solution creates multiple insertion points for the table. However, it also introduces overhead for maintaining the physical order of the table’s rows.

Locks

There are 2 types of locking schemes

1. All page locking (APL)
2. Data only Locking (DOL)
   1. Data Page Locking (DPL)
   2. Data Row Locking (DRL)

All Page Locking

In this locking scheme server uses table level locks and page locks, but not row level locks and index page can be locked.

Data Page Locking

Serer uses table locks and page locks but no row level locks and index pages are never locked .

Data Row Locking

Index pages are not locked but server uses table and row level locks.

Lock Promotion Thresholds

In all page locking the lock promotion will be done from page level to table level

• In DPL the lock promotion is done from page level to table level
• In DRL the lock promotion is done row level to table level but not table level

Types of LOCKS

• Shared Lock
• Exclusive Lock
• Update Lock
• Dead Lock

Shared lock

Adaptive server applies shared lock for Read (select) operation .If a shared lock has been applied to a data page or a data row or to an index page other transactions can also acquire a shared lock even when the first transaction is active .

“ No Transactions can acquire Exclusive lock on a page / row / index until all the shared locks on the page / row are released ”

Exclusive Lock

ASE applies an exclusive lock for a DML operations like insert/delete when a transaction gets on a exclusive lock other transactions cannot acquire a lock of any kind on the page / row until the exclusive lock is released at the end of its transaction

“ The other transactions wait or block's until the exclusive lock is released ”

Update Lock

An update lock is applied during the initial phase of an update /delete fetch operation while the page /row is being read .

“It allows shared locks but not update or exclusive locks”

To change the locking scheme of a table

Syntax Alter table table_name lock {allpages | datapages | datarows}

Example Alter table EMP lock datarows

Dead Lock

A dead lock occurs when 2 or more process ,each have a lock on a separate data-page or index page /table and each want to acquire a lock on same page or table locked by other process this situation is called dead lock .

In ASE when a deadlock situation occurs the process which has the least CPU utilized will be killed .The configuration option “print deadlock info” is enabled then it will display the process information and the SQL's which theses process running will be displayed on the error log .

The deadlock situation is automatically dealt by ASE server itself

The Error message sent to error 1205

To know the deadlock info we need to enable the configuration parameters

sp_configuration “Print Deadlock Information”

• Locks can be on page or a table
• Lock promotion can be configured server wide or per object (table)
• Deadlocks are detected and cleared by SQL server after a default amount of time has elapsed
• Deadlock detection time is configurable
• dbcc traceon (3605) → Trace flag prints information what tasks involved in deadlocks

OAM [Object Allocation Mapping]

OAM [Object Allocation Mapping]

With this for load option db will be offline until a load has been done and then become online.

• When we use this option the database will be created and it will be in offline mode.
• Only the OAM pages will be initialized during the creation of this db and the actual page allocation will be done at the time of load
• with this option db creation is faster
• whenever we create a database with this option it will be in don't recover option and even when ever there is a termination of dump db transaction
• whenever creating a dbcheck whether the given size and size of the disk after creating are equal or not this may create the problem
• whenever you forget the compression option of dump db during loading db it takes the option if dumpdb .

Syntax

dumpdb <database name> “ compress::2::/physical path ” → option given here

dumpdb <database name> “ compress::/physical path ” → option not given here

WITHOVERRIDE

option is used if we want to “create a database” or “alter a database” whose data and log is on same device .

Example:

Create database dbname on d001=40 log on d001=5 WITHOVERRIDE

!! Warning !!

• This can make recovery impossible if that disk fails and database is online
• When database is critical I.e mostly 95% of the space is used then the threshold action is fired .

Backup and Recovery

Backup & Recovery

Difference between Roles and Groups

• Roles have server level permission which are assigned to logins
• Groups have database level access ,permission ,users added to these groups

Backup & Recovery Strategies

Backup Procedure we use two types : dump database (or) dump transaction

Recovery Procedure we use two types : load database (or) load transaction

dumpdb (differential backup)

Makes a backup copy of the entire database, including the transaction log, in a form that can be read in with load database. Dumps and loads are performed through Backup Server.

The target platform of a load database operation need not be the same platform as the source platform where the dump database operation occurred. dump database and load database are performed from either a big endian platform to a little endian platform, or from a little endian platform to a big endian platform.

• A small company that has minimal transaction go for whole database dump here data and log can be mixed for database
• It is most important as a DBA to take db dumps at regular point of interval ,so that it will be helpful to recover the db at the maximum
• The difference points where there is failure condition like server has been shutdown improperly and db has got corrupted here we need to recover database
• When dump database is given it takes the full dbdump which is data and log mixed
• Whenever we take a full db dump the log will not be truncated

Syntax dump database <dbname> to <physical path >

Example dump database syb01 to “/$ Sybase/dumps/db_name.dat”

dump transaction

• Mostly used for large companies which has many transactions for example Banking sector → here data and log should be separated
• When we issue this command the dboption truncate log on check point should not be enabled then this command works
• Here only the transaction log dump happens when we take backup

Syntax dump transaction <dbname> to <physical path >

Example dump transaction syb01 to “/$ Sybase/dumps/db_name_transactiondump”

Failure conditions when these commands fail

1. Check whether backup servers may not be running
2. Need to check the permission on the directory where the dumps are taken place (i.e. write permission )
3. There might be done minimally logged operation
4. The file system may not have space to dump

• For 3^rd Failure 1^st we need to take full dbdump and the transaction dump
• When a full database dump is running we will not be able to take transactional dump

Compressed Backups

• When we use this option while taking dump the db size will be compressed and the backups has been taken
• Compression levels
  • 0 → No Compression
  • 1 → Default Compression
  • 9 → MAX Compression(Not recommended because leads to corruption of database )

When we use this option the performance of dump and load will be high because the database needs to be compressed and take the backups

When we use this option the server performance will be low while dumping/loading database because the server needs to compress database size and then take the backup.

Similarly the database dump needs to be uncompressed and load it back (here server performance will be low).

Syntax & Example

1. dump database <dbname> to “device name”

dump database pubs2 to “opt/sybase/dump/pubs2.dat”

2. dump database <dbname> to “compress::level::/..../.../.../.../.cmp”

dump database pubse2 to “compress::2:: /opt/sybase/dump/pubs.dat”

3. dump database <dbname> to “compress::level::/..../.../.../.../.cmp”

stripe on “compress::level::/..../.../.../.../.cmp2”

stripe on “compress::level::/..../.../.../.../.cmp3”

dump database pubse2 to “compress::2:: /opt/sybase/dump/pubs.dat”

stripe on “compress::2::/opt/sybase/dump/pubs.dat.cmp2”

stripe on “compress::2::/opt/sybase/dump/pubs.dat.cmp3”

Stripe on and Multiple Stripes

When we use this option the dbdump can be taken on multiple files and devices

• use default compression level
• Maximum no of stripes is 1024 stripes

Syntax

dump database dbname to “<physical path>”

stripe on “/path/s1”

stripe on “/path/s2”

go{For without Compression}

A full dbdump has 3 phases

• Phase 1 → Flush all data pages
• Phase 2 → Scans the data pages
• Phase 3 → Flushes log segment pages

*** Transactional dump files only log pages

Transactional Dumps:

Dump Tran with truncate only

We can use with truncate only option inorder to clear the log without taking the transaction log dump .we will use thisoption when the transaction log is full and the db option truncate log on chhkpoint is enabled

Example Error 1105 → gives dbname /segment name ,throws an that log got filled

Checkpoint :: It writes what all transactions are been committed to physical devices

Dump tran with no log

This option is used in order to clear the transactional log .when we use this option it will clear both inactive and active transaction's.

It is not recommended by sybase to use this option multiple no of times because this may lead to corruption of the data .

Dump Tran with no truncate

This option is used for up-to the minute of recovery when a database is completed and needs to be recovered. In order to use this option it is must important that the data and the log should be on separate devices.

When option truncate_log & no_log are not working and if it is temporary database then go for

Select lct_admin(“abort”,0,2) → 0 represents All Transactions

→ 2 represents TEMPDB

Load Database :

When ever we want to recover a database or we want to load the refresh production data onto the development or the UAT servers

We will use the Load db command

Minimum servers required for a setup

• Production server
• Pre-production server
• Development server
• UAT

Database refresh

Taking the latest dumps from production and loading it onto the development / UAT / Pre production is called db refresh .

Precautions before loading the db

• check whether loading database is not production server.
• The database size should match with the production server database size.
• Take BCP of systables I.E sysusers and sysalterantes in order to retain the permission which is having before the load.
• No users are connected to the database.

Syntax

load database <dbname> from “path of the dump”

load database <dbname> from “path of the dump”

load transaction

After issuing those commands sybase server makes database offline so we need to change to online

Online database <dbname>

creating a database with for load option

Syntax

Create database <dbname>

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

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

for load

go

Defncopy

Defncopy [Definition & Copy]

Copies definitions for specified views, rules, defaults, triggers, or procedures from a database to an operating system file or from an operating system file to a database.

This utility is in the $SYBASE/$SYBASE_OCS/bin.

defncopy cannot copy table definitions or reports created with Report Workbench

• Invoke the defncopy program directly from the operating system. defncopy provides a non-interactive way of copying out definitions (create statements) for views, rules, defaults, triggers, or procedures from a database to an operating system file. Alternatively, it copies in all the definitions from a specified file.

• You must have select permission on the sysobjects and syscomments tables to copy out definitions; you do not need permission on the object itself.

• You must have the appropriate create permission for the type of object you are copying in. Objects copied in belong to the copier. A System Administrator copying in definitions on behalf of a user must log in as that user to give the user proper access to the reconstructed database objects.

• The in filename or out filename and the database name are required and must be unambiguously stated. For copying out, use file names that reflect both the object’s name and its owner.

• defncopy ends each definition that it copies out with the comment

/* ### DEFNCOPY: END OF DEFINITION */

• When assembling definitions in an operating system file to be copied into a database using defncopy, each definition must be terminated using the “END OF DEFINITION” string.

• Enclose values specified to defncopy in quotation marks if they contain characters that could be significant to the shell.

Syntax

defncopy

[-v] [-X]

[-a display_charset]

[-I interfaces_file]

[-J [client_charset]]

[-K keytab_file]

[-P password]

[-R remote_server_principal]

[-S [server]]

[-U username]

[-V [security_options]]

[-z language]

[-Z security_mechanism]

{in filename dbname | out filename dbname

[owner.]objectname [[owner.]objectname...] }

Parameters

• in | out specifies the direction of definition copy in relation to the database. For example, specifying “in” copies definitions into the database.

• filename specifies the name of the operating system file destination or source for the definition copy. The copy out overwrites any existing file.
• dbname specifies the name of the database to copy the definitions from or to.
• objectname specifies name(s) of database object(s) for defncopy to copy out. Objects should not be specified when copying definitions into a database.
• -a display_charset allows you to run defncopy from a terminal where the character set differs from that of the machine on which defncopy is running. -a in conjunction with -J specifies the character set translation file (.xlt file) required for the conversion. Use -a without -J only if the client character set is the same as the default character set.

• -I interfaces_file specifies the name and location of the interfaces file to search when connecting to Adaptive Server. If you do not specify -I, defncopy looks for an interfaces file located in the directory specified by the SYBASE environment variable.

• -J client_charset specifies the character set to use on the client. A filter converts input between client_charset and the Adaptive Server character set.

• -J client_charset requests that Adaptive Server convert to and from client_charset, the client’s character set.
  • -J with no argument sets character-set conversion to NULL. No conversion takes place. Use this if the client and server are using the same character set.
  • Omitting -J sets the character set to a default for the platform. The default may not necessarily be the character set that the client is using. (See the System Administration Guide and the System Administration Guide Supplement for more information about character sets and the associated flags.

• -K keytab_file can be used only with DCE security. It specifies a DCE keytab file that contains the security key for the user name specified with -U option. Keytab files can be created with the DCE dcecp utility. See your DCE documentation for more information.

• If the -K option is not supplied, the user of defncopy must be logged in to DCE with the same user name as specified with the -U option.

• -P password allows you to specify your password. This option is ignored if -V is used.

• -R remote_server_principal specifies the principal name for the server. By default, a server’s principal name matches the server’s network name (which is specified with the -S option or the DSQUERY environment variable). The -R option must be used when the server’s principal name and network name are not the same.

• -Sserver specifies the name of the Adaptive Server to connect to. Without -S, defncopy looks for the server specified by your DSQUERY environment variable.

• -U username allows you to specify a login name. Login names are case sensitive. If you do not specify username, defncopy uses the current user’s operating system login name.

• -V security_options specifies network-based user authentication. With this option, the user must log in to the network’s security system before running the utility. In this case, users must supply their network user name with the -U option; any password supplied with the -P option is ignored.

• -V can be followed by a security_options string of key-letter options to enable additional security services. These key letters are:
• c – Enable data confidentiality service
• i – Enable data integrity service
• m – Enable mutual authentication for connection establishment
• o – Enable data origin stamping service
• r – Enable data replay detection
• q – Enable out-of-sequence detection

• -v displays the version number and copyright message of defncopy and returns to the operating system.

• -X specifies that, in this connection to the server, the application initiate the login with client-side password encryption. defncopy (the client) specifies to the server that password encryption is desired. The server sends back an encryption key, which defncopy uses to encrypt your password, and the server uses the key to authenticate your password when it arrives.
  • If defncopy crashes, the system creates a core file which contains your password. If you did not use the encryption option, the password appears in plain text in the file. If you used the encryption option, your password is not readable.

• -z language specifies the official name of an alternate language that the server uses to display defncopy prompts and messages. Without the -z flag, defncopy uses the server’s default language. Add languages to an Adaptive Server at installation, or afterwards with the utility langinstall or the stored procedure sp_addlanguage.

• -Z security_mechanism specifies the name of a security mechanism to use on the connection.
  • Security mechanism names are defined in the $SYBASE/install/libtcl.cfg configuration file. If no security_mechanism name is supplied, the default mechanism is used. For more information on security mechanism names, see the description of the libtcl.cfg file in the Open Client and Open Server Configuration Guide for UNIX.

defncopy for out

defncopy -Usa -S<servername> out <filename>,<dbname>,<objectname>

defncopy -Usa -Ssybase123 out sp_helpdb.out,sysbsysteprocess,sp_helpdb

defncopy for in

defncopy -Usa -S<servername> in <filename>,<dbname>

defncopy -Usa -Ssybase123 out sp_helpdb.out,sysbsysteprocess

*** for doing this we need to drop the procedure sp_helpdb