We help IT Professionals succeed at work.
Get Started

Sybase Performance

chriseebh
chriseebh asked
on
1,228 Views
Last Modified: 2012-05-04
Have a deadlock situation for almost every two weeks, and application is moving but very slow, but could not verify.  
No access to the box.  At this time, the transaction log was filled up, it was now set as 1 GB in size but increasing the space to 2GB will not resolve the situation which I feel.

The sybase recover by itself after the peak hours.  The system was set in default.

The only maitenance tasks are
1.      Update statistics followed by sp_recompile on all tables in apps database
2.      DBCC Checkstorage done on SSR database
3.      Normal DBCCs done on other databases e.g. master
4.      reorg compact run on all DOL tables that meet criteria (1% or 1000 rows can be reclaimed)
5.      As of next weekend reorg reindex will be run on specified DOL tables E.G. tables etc.

The database has no scheduled restarts.

The Sybase configuration for deadlocks is the default configuration that you can see below.
Configuration option is not unique.
 
 Parameter Name                            Default     Memory  Used Config Value Run Value  
 ------------------------------ ----------- ----------- ------------ -----------
 deadlock checking period               500           0            500                         500
 deadlock pipe active                           0           0            0                              0
 deadlock pipe max messages             0           0            0                              0
 deadlock retries                                  5           0            5                              5
 print deadlock information                  0           0            0                              0
 
(1 row affected)
(return status = 1)

CPU was average at 20-30% but at peak hours 30-40%

Number of 'open databases' configured:  12
Actual number of databases in server:  9
'OpenDbRemaining' parameter's average:  9.98

Average connections used: 49.236486 (9%)
Average connections free: 550.763514 (91%)
Total 'number of user connections' configured: 600
       
Maximum connections remaining: 596
Minimum connections remaining: 0

During that the transaction logs was fill and in the end it was crashed due to db option "abort on log full

Anything which I might have missed??  Any suggestion are welcome.
The last option which I have not tried is to turn on the sp_sysmon?  The only time it happens was every two weeks no fix day, so if when the problem occur, turning the sp_sysmon will it help?  For 3 incidents, the deadlock encounter and my application hang.
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE