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