simple note

Mon table usuage for PT... 

Determine Procedure Cache Hit Ratio

select Requests, Loads,

"Ratio" = convert(numeric(5,2),(100 - (100 * ((1.0 * Loads)/ Requests))))

from mon_db..monProcedureCache

where timestamp between startdate and enddate

go

 

Process Consuming most CPU and Plan

 

  select ps.SPID, ps.CpuTime,

  pst.LineNumber, pst.SQLText

from master..monProcessSQLText pst,

master..monProcessStatement ps

where ps.SPID = pst.SPID

  and ps.CpuTime = (select max(CpuTime) from  master..monProcessStatement    

)

order by SPID, LineNumber

 

 

Engine Statistics

 

select EngineNumber, SystemCPUTime "CPU_TIME_IN_I/O", UserCPUTime "CPU_TIME_IN_USER_PROCESS", IdleCPUTime "IDLE_CPU_TIME", CPUTime "TOTAL_CPU_TIME" from mon_db..monEngine

where timestamp between startdate and enddate

Note: -   –UserCPUTime will reflect bad queries with table scans in memory, Cursors, etc.

  –SystemCPUTime will reflect physical and network IO

--

Other Useful SQLs used for performance monitoring:

 

CHECK BLOCKS

select spid,blocked from sysprocesses where blocked > 0

 

PHYSICAL_IO of blocked process

select spid,physical_io from sysprocesses where spid in (select blocked from sysprocesses where blocked > 0)

 

Deatils of processes which is blocked

select spid,suser_name(suid) Login,db_name(dbid),cmd,time_blocked from sysprocesses

where blocked > 0

order by time_blocked

 

select 'dbcc sqltext('+convert(varchar,spid)+')' from sysprocesses

where blocked > 0

 

select 'sp_showplan '+convert(varchar,spid)+',null,null,null' from sysprocesses

where blocked > 0

 

Details of blocked(Process which is blocking)

 

select spid,suser_name(suid) Login,db_name(dbid),cmd,cpu,loggedindatetime,physical_io from sysprocesses

where spid in (select blocked from sysprocesses where blocked > 0)

order by cpu desc

 

select spid,suser_name(suid) Login,db_name(dbid),cmd,cpu,loggedindatetime,physical_io from sysprocesses

where spid in (select blocked from sysprocesses where blocked > 0)

order by loggedindatetime

 

dbcc traceon(3604)

 

select 'dbcc sqltext('+convert(varchar,spid)+')' from sysprocesses

where spid in (select blocked from sysprocesses where blocked > 0)

 

select 'sp_showplan '+convert(varchar,spid)+',null,null,null' from sysprocesses

where spid in (select blocked from sysprocesses where blocked > 0)

 

 

CHECK PHYSICAL_IO OF LONG RUNNING TRANSACTIONS

 

select a.spid,a.physical_io,db_name(b.dbid) 'DATABASE NAME' from sysprocesses a,syslogshold b

where a.spid = b.spid

 

LARGEST TABLES IN DATABASE

select o.name,sum(s.pagecnt)*2 SIZE_KB from sysobjects o, systabstats s where o.id = s.id and o.type = 'U' group by o.name order by SIZE_KB desc

                 

PROD To DEV /TEST environment DUMP & LOAD ( refresh)

on  DEV_HOST

isql -U<sa login> -SSybase_DEV

select 'kill',spid from master..sysprocesses where dbid(db_name)

go

kill all processes in Sybase_DEV.dev_db databases

To preserve Dev environment's  access previledge  before PROD load

bcp dev_db..sysroles out dev_db .. sysroles -b1 -c - Usa - P***** - S Sybase_DEV

bcp dev_db .. sysprotects out dev_db .. sysprotects -b1 -c -Usa - P*** -S Sybase_DEV

bep dev_db .. sysattributes out dev_db .. sysattributes -bl -c - Udba -P** -S Sybase_DEV

bcp dev_db .. sysusers out dev_db .. sysusers -bl -c - Udba -P**  -SSybase_DEV

bcp dev_db .. sysalternates out dev_db .. sysalternates -bl -c - Udba -P** -S Sybase_DEV

load database dev_db from

"compress :: 4 :: /home2/sybase/PROD_DB1.dat" Stripe on

"compress :: 4 :: /home/sybase/PROD_DB2.dat" Stripe on

"compress :: 4 :: /home2/sybase/PROD_DB3.dat" Stripe on

"compress :: 4 :: /home2/sybase/PROD_DB4.dat"

go

delete dev_db .. sysusers where suid > 1

delete dev_db .. sysroles

delete dev_db .. sysattributes

delete dev_db .. sysprotects

delete dev_db, sysalternates

on server_host

bcp dev_db..sysroles in dev_db .. sysroles -b1 -c - Usa - P***** - S Sybase_DEV

bcp dev_db .. sysprotects in dev_db .. sysprotects -b1 -c -Usa - P*** -S Sybase_DEV

bep dev_db .. sysattributes in dev_db .. sysattributes -bl -c - Udba -P** -S Sybase_DEV

bcp dev_db .. sysusers in dev_db .. sysusers -bl -c - Udba -P**  -SSybase_DEV

bcp dev_db .. sysalternates in dev_db .. sysalternates -bl -c - Udba -P** -S Sybase_DEV

To recover SA password 

in run file 

-m sa ---> add this and resrt server you get lost password of sa in log then once login to server can change to new difficult password

If Server experience is slowness

@os level

1. vmstate  --- check for cpu , cache , memory , resourses

2.  check error log

3. sp_monitor --> % of time Adaptive Server uses the CPU during an elapsed time interval cpu usage should not > engine

4. sysprocesses ---> awaiting_cmd

How to do cross platform Dump Load 

@source

1. put db in single user mode

2.dump tran with truncate only

3sp_flushstats

4.Dump db

@Target

1.Load 

2.online db

3.sp_post_xpload ---> this rebuild the currupted index

 

 

Thread down due to Duplicate rows in replicate database table

@ RS :

sysadmin log_first_tran, data_server, database

@ DS :

use rs_RSSD
go
rs_helpexception - displayed xact no 107
rs_helpexception 107, v

set

where you will see the transaction causing the problem.

Apply this transaction manually.

set autocorrection on
 for publishers_rep
 with replicate at SYDNEY_DS.pubs2

How to clear the log from the RSSD db

 

1. dbcc settrunc(ltm,ignore).
2. sp_stop_rep_agent <dbname> or sp_config_rep_agent 'disable'.

To enable when you are ready. P.S. The order is very important.

If you have used sp_stop_rep_agent

1. dump tran with truncate_only.
2. use db
dbcc settrunc(ltm,valid)
3. On the RSSD.
rs_zeroltm DBSERVER,DBNAME
This step will tell the RepServer to look at the secondary marker from the start of the transaction log.
4. sp_start_rep_agent <dbname>

If you have used sp_config_rep_agent 'disable'

1. dump tran with truncate_only.
2. sp_config_rep_agent 'enable'..... This will also set the valid trunc marker.
3. On the RSSD.
rs_zeroltm DBSERVER,DBNAME
This step will tell the RepServer to look at the secondary marker from the start of the transaction log.
4. sp_start_rep_agent <dbname>

 

 

How to find the whether specified index used by the query :  check showplan output

1> use pubs2

1> set showplan on

1> select stores.stor_name, sales.ord_num

2> from stores, sales, salesdetail

3> where salesdetail.stor_id = sales.stor_id

4> and stores.stor_id = sales.stor_id

5> plan " ( m_join ( i_scan salesdetailind salesdetail)

6> ( m_join ( i_scan salesind sales ) ( sort ( t_scan stores ) ) ) )"

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using the Abstract Plan in the PLAN clause.

6 operator(s) under root

The type of query is SELECT.

ROOT:EMIT Operator

    |MERGE JOIN Operator (Join Type: Inner Join)

    | Using Worktable3 for internal storage.

    |  Key Count: 1

    |  Key Ordering: ASC

    |

    |   |SCAN Operator

    |   |  FROM TABLE

    |   |  salesdetail

    |   |  Index : salesdetailind

    |   |  Forward Scan.

    |   |  Positioning at index start.

    |   |  Index contains all needed columns. Base table will not be read.

    |   |  Using I/O Size 2 Kbytes for index leaf pages.

    |   |  With LRU Buffer Replacement Strategy for index leaf pages.

    |

    |   |MERGE JOIN Operator (Join Type: Inner Join)

    |   | Using Worktable2 for internal storage.

    |   |  Key Count: 1

    |   |  Key Ordering: ASC

    |   |

    |   |   |SCAN Operator

    |   |   |  FROM TABLE

    |   |   |  sales

    |   |   |  Table Scan.

    |   |   |  Forward Scan.

    |   |   |  Positioning at start of table.

    |   |   |  Using I/O Size 2 Kbytes for data pages.

    |   |   |  With LRU Buffer Replacement Strategy for data pages.

    |   |

    |   |   |SORT Operator

    |   |   | Using Worktable1 for internal storage.

    |   |   |

    |   |   |   |SCAN Operator

    |   |   |   |  FROM TABLE

    |   |   |   |  stores

    |   |   |   |  Table Scan.

    |   |   |   |  Forward Scan.

    |   |   |   |  Positioning at start of table.

    |   |   |   |  Using I/O Size 2 Kbytes for data pages.

    |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

After the statement level output, the query plan is displayed. The showplan output of the query plan

consists of two components:

·         The names of the operators (some provide additional information) to show which operations

 are being executed in the query plan.

·         Vertical bars (the “|” symbol) with indentation to show the shape of the query plan operator tree.

Task context switches by engine

“Task Context Switches by Engine” reports the number of times each Adaptive Server engine switched context from one user task to another. “% of total” reports the percentage of engine task switches for each Adaptive Server engine as a percentage of the total number of task switches for all Adaptive Server engines combined.

1) Meaning of Spinlock :-  "spinning" (repeatedly trying to acquire the lock)

2) T-SQL Query to get all the tables and lock scheme info.

The following Query gives list of all the User Tables and locking scheme of the Table.

select "Table"=left(name,32), "lock_scheme"= case 
                            when (sysstat2 & 57344) < 8193 then 'APL' 
                            when (sysstat2 & 57344) = 16384 then 'DPL' 
                            when (sysstat2 & 57344) = 32768 then 'DRL' 
                            end
 from sysobjects
  where type='U'
        order by sysstat2

4) How to clear data from cache memory

How to clear data from cache memory

data cache

as of 15.0.3 - dbcc cachedataremove(dbid | dbname, objid | objname, partitionid | partitionname, indid | indexname)
you need sa_role for that as well

pre-15.0.2 ESD6 - try sp_unbindcache_all 'default data cache'
for example to clear "default data cache".  For named data caches, you should do this, followed by a sp_bindcache again for each object bound to a named cache
statement cache

set statement_cache off    -- session level
<your sql statements here>
or
sp_configure 'statement cache size',0  -- server level

procedure cache

In 12.5.4 ESD5 and 15.0.2 :   dbcc proc_cache (free_unused)

pre 12.5.4 ESD5:    dbcc proc_cacherm(type, dbname, objname)

where: type is V,P,T,R,D,C,F, or S (must be uppercase) corresponds to View, Proc, Trigger, Rule, Default, Cursor, SQLJ Function, SQL function

5)

Getting the process ID for the oldest open transaction.

Use the following query to find the spid of the oldest open transaction in a transaction log that has reached its last-chance threshold:

use master

go

select dbid, spid from syslogshold

where dbid = db_id("name_of_database")

For example, to find the oldest running transaction on the pubs2 database:

select dbid, spid from syslogshold

where dbid = db_id ("pubs2")

dbid   spid

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

    7      1

6) How do you troubleshoot if your tempdb gets filled

i) Try to find out the Active process that is filling up the temp db space.

ii)  If the transaction log of tempdb is full then you call login through sa and type following command.

> dump tran tempdb with trancate_only
> go

iii) If the database is full then you can increase the size of the database on a free device.

> alter database tempdb on device_name = size
> go

iv) use following command It will abort all open transactions.

    But be sure the task by confirming with the concern users.

>select lct_admin(0,2)
>go

Restarting the server is not recommanded.lct_admin (0,2) would abort all open transactions, or you

can go for altering the tempdb space. Multiple tempdb's is a feature which can be implemented to 

minimize such issues of tempdb getting full.

How to force a Table Scan in Sybase?

Are indexes always useful and mandatory on a table or Is Table scan on a table

always a bad news for us? To understand what is good for us we need to know the

exact purpose of indexes and what is meant by a Table scan? When a user executes

a query and the Sybase server has to iterate over each row of each page in a Sybase

table then the scan that Sybase server is currently performing is called a Table scan.

In case we have created indexes and used proper SARGs then the Sybase server will

pickup an appropriate index and fetch our results more quickly than any Table scan.

However, this is just one part of the story and is applicable only when tables are of

very large size. So, always create indexes on a table which is of very large size

otherwise there are lots of overheads to maintain and keep the indexes up to date in

Sybase. In case of smaller tables it is better to be without Indexes than using an

Index. So, what to do in case we have some data in the table and have some indexes

created on the table and still want to force Sybase to perform a table scan?

This is usually required when the data in a table is serialized or sequential and

we are sure that first few records is all that we need to iterate on.

So, now knowing that we need a Table scan in Sybase we need to make sure that

Sybase server does not use any index while executing the query.

This is called forcing a table scan. To force a table scan in Sybase we can

use a simple statement like this -

select * from my_table (index 0) where col1 = @col1

As we can see here that we have suggested (index 0) to be used in the

above mentioned query? Wondering why and what does (index 0) specify? Well, 0

here refers to the 0th entry in sysindexes table which is actually representing

a table scan. Hence by suggesting the index we can force a table scan in Sybase.