Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Seibel Support Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 2:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:18 AM
Points: 30, Visits: 307
Hello,

I've got users using an application called contract works that is written on Seibel. I have had reports of it timing out and slowing down.

The application is on the same server as the databases (which is SQL 2000).

There is 4 gb of RAM of which 2.5 GB is assigned to the Datababse however SQL is only ever using around 1.8GB.

The application is written using dynamic cursors, there are next to no Stored procedures called. Beyond that i've seen as many as 20 databases called in queries with left outer joins and the execution plans are showing 100% utilisation at page scans, however no recomendations were made to add any indexes. It looks like the required indexes already exist.

I've checked to see if there are any locks, which there were none. Checked IO/memory/CPU and could see spikes and the heavy usage queries being returned. I guess returning data going as far back as 2003 using cursors is extremely inefficient and I just cant see how I could improve performance beyond recommending better hardware and more memory. I believe the drives are also old and probably around 5400 rpm adding to the problem.

I was hoping for further recommendations or advise.

please see the presentation:

https://docs.google.com/viewer?a=v&q=cache:BtrmSjg8wZMJ:download.microsoft.com/download/4/d/f/4df96113-8fee-40b0-8000-794202296569/SiebelTuningonSQLServer.ppt+&hl=en&gl=uk&pid=bl&srcid=ADGEESiXTLE6wInCTbJTpNp2SvQDS1H7OxtqYxBL2jlwLXRqGAWOHlLP-gBMUkLlFkUiWbCAu5CeKvF6izxMPP3aWDnAO6ZPJWzcYKvKivUPdedHpPf7_xQ7NS2uCDk-oLxM-EQ1W_tm&sig=AHIEtbS36d5fhNrEIaqWjj9YPQ7PszkjMA

beyond that current things i've tried.

Daily job:

EXEC sp_configure 'max server memory', 2048
RECONFIGURE;
GO

maintenence plan to rebuild stats daily, and reorganise indexes weekly(using sql 2000 maintenence plans). Although I'm not sure if I should rebuild the indexes once a week.

Beyond that I was running checkpoint and DBCC DropCleanBuffers daily too but noticed at the start of each day performance is hit as plans are reritten.

Wait times are high often when theres a large query running.

any recommendations are much appreciated.
Post #1384478
Posted Wednesday, November 14, 2012 2:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:18 AM
Points: 30, Visits: 307
to follow up the data file for the database is 8.6GB but the log file is 2 MB as it is in Bulk-Logged mode and the Torn page detection and auto update stats are enabled.
Post #1384479
Posted Tuesday, December 4, 2012 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:18 AM
Points: 30, Visits: 307
after monitoring the above server for several weeks and with the addition of the monitoring i placed, this problem wasnt as easy or as straight forward as simply saying upgrade hard ware on the database server.

The problems were beyond the scope of the database, and we realised there were other IIS related issues also. So as it stands the occassional restart of services for seibel and also the database and IIS resolves issues temporarily.

IIS is 5.0 and the way that the app is written querying data via cursors it is just so badly written that i'm lost for words.

I've suggested the company consider migrating data to a new CRM package. but as they are using a custom made bespoke application management are not supporting this call.

I dont think anyone out there can advise. but its certainly been an interesting road for beefing up my perfmon skills.
Post #1392528
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse