Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: High Cpu Usage During Logon

    MY PROBLEM IS SOME HOW STRANGE TO ME AS I AM NOT AN EXPERT...

    WE USE AN ERP PROGRAM ON A SQL7 DATABASE.....WHEN USERS LOGON AND TRY TO WORK I HAVE 100% CPU USAGE AND THE SERVER IS NOT RESPONDING UNTIL I KILL THE APP. IF THE USER HAS ADMINISTRATOR RIGHTS EVERYTHING WORKS SMOOTHLY....ADMINISTRATOR RIGHTS FOR THE DATABASE....

    WHAT I MANAGED TO FINDOUT IS THAT WHAT IS RUNNING DURING THE SERVER LOCKS IS SP_CURSOROPEN

    ANY SUGESTIONS???......

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Sounds like you have possible wrong level of access for the app - try adding it to the db_datareader & db_datawriter database roles & see what happens. If that doesn't make any difference, remove from all roles ( except public ) then only to db_owner database role & see what happens.

    Cheers,

    SG.

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    nothing.........

    some new facts on the matter are that the users eventualy log on and can work but the speed is very slow when they try to change menu options......the problem occured after 3 years of smoothly operation and that is the strang thing.....also when i say admin right i mean from the apps option since on the DB i have only one user (SA) who is used by the app.......

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Well, sa will give you all the access you need.

    How are you for disk space - especially for the TEMPDB database & the production db? Also, sounds possible you may be paging memory madly out to disk , hence the slow speed. Have you run perfmon to check the efficiency of the server?

    Sounds odd that after 3 years of smooth operation all goes to custard.....

    What else has changed recently? Any service pack updates etc?

    Cheers,

    SG

  5. #5
    Join Date
    Oct 2003
    Posts
    4
    nothing except a shrink and a reorganize......the second was running on a schedule for some time now.......
    as for space everything is ok.....
    performance has nothing to do also i believe it is something else.....something about the app... since the only user that logs on sql is sa nomatter if i give him rights using the app or not.....

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Common sense tells me SOMETHING has to have changed. If you havent run out of disk space, the server isnt under powered then someone, somewhere has to have altered something.

    Try running Profiler and see what activity is occurring in the database just as it starts to slow down. Is there a particular sequence of events that happen as this problem starts, or is it an ongoing problem? Has the number of users gone up? have new functionality been added to the system?

    It wouldnt be the first time someone has altered a system but not told anyone.....

    Cheers,

    SG

  7. #7
    Join Date
    Oct 2003
    Posts
    4
    at first i would like to say thanks for your help.....

    i managed to fix the problem but i dont know yet what coused it.....
    the solution was to reindex the tables that are involve to security for the app and manualy updatestats for the production db......
    know i have a small delay which is acceptable and everybody is working ok....

    thanks again....

  8. #8
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy,

    .....well without sounding a little comical...that would have been one of my next suggestions........

    Check the settings for the database - they should be ( by default ) set to update stats automatically. Its a bit scary that this was turned off....You can create a separate Maintenance Plan to do this - we do and sample 25% of the database. Also a reindex of each index in the database is useful - we do it weekly & it keeps the index fragmentation right down and speed up.

    You can also turn off autoshrink for the databse which will lift the speed a bit more but you will need to schedule a job to do this at least weekly as this will keep the databases from getting too big & slowing down.

    Also, turn off the autoclose for the database - this will remove a small overhead on performance.

    Cheers,

    SG.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •