SQL Server Performance

Sql server 2005 db slow down suddenly - URGENt

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by masqazi, Dec 21, 2006.

  1. masqazi New Member

    i cannot say why but one of my db which is running on sql server 2005 has slow down drastically. while other db on the same server are fine. what can be the reason. this victim db was running fine for last 6 months.

    another thing i noticed is that CPU consumtion by sql server on this server is fluctuating drastically, sometime its going to 97 -98 and then comes down to 6-7. what is causing this.

    we are using window server 2003 and memory is 2 GB.

    Best Regards
  2. satya Moderator

    What kind of scheduled jobs, optimization jobs are set against this database?
    What is the volume of the database and user base too?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. MohammedU New Member

    Run the sql trace and filter on duration and CPU usage...
    Check if any of the frequently running procedures are recompiling or executing with bad query plan.

    Mohammed U.
  4. masqazi New Member

    I have backup schedules at nights and transactional replication which is replicated to subscriber at 12pm and 8 pm.

    The complete db is slow so i dont see any importance in checking specific query in profiler. I assume there is something in general which went wrong for this db. what can be that.

    Same db was fine till last week. on weekend the networking guys remove the VLAN setup after that this problem occur. amazing thing is that other db on the same server are fine and when i restore this particular db on test machine, still i am facing the same problem.

  5. madhuottapalam New Member

    Is it slow when u access from the application ?
    when u run a query from QA how it perform ?
    is any query to any tables are slow on the db or few SPs are slow ?
    r u getting any time out error?

    just post the whole scenario.

  6. masqazi New Member

    its slow when running from application, its fine when running from QA.

    i dont see only specific queries are having problem. almost all the operatioinal queries / stored procedures are having this problem. only queries with very small tables are fine.

    for example: the SQL Batch for the below query has taken 34589 duration

    i suspect something general with db not specific to any query, becuase we are using the same queries / application for last 6 months without any change and it was fine.

    what are the alternative solutions to apply on db as whole. even trying to restore from a full backup is of no use.

  7. thomas New Member

    Are you running regular update statistics?
  8. madhuottapalam New Member

    we faced same problem in same scenario. (ie. transactional replicated database). the application was running slow , but the QA was fine. All other DBs were ok on the same server. We ran Profiler but nothing come out. We ran DBCC ReindexDB and no result. Somehow, we missed to Update the Statistics. Finally we did that (Sp_updateStats, ) and the problem got sorted out.

    so try
    sp_updatestats or UPDATE STATISTICS

    and also i would request u to share the result.

  9. masqazi New Member

    i dont get any improvement by reindexing and sp_updatestats. still its the same.
  10. satya Moderator

  11. masqazi New Member

    is there anything like database corrupting or sql server corrupting. because almost all my results are fine and there is nothing significant happen to it.
  12. masqazi New Member

    Thanks god and thanks everybody. finally, i could make my db back to normal. how ???

    i just drop existing indexes and create them back. its funny, earlier i rebuild them but could not get any success, but after deleting and recreating them its fine. It seems sometime logic / sense takes back seat.

  13. satya Moderator

    True and in general the DBCC DBREINDEX would do the same, as you say it helps fewtimes if you perform such process. But you have to keep in schedule the database optimization tasks in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  14. joechang New Member

    once you have found queries that are slow,
    and confirm the cpu for the queries are also high,
    look at the execution plan,
    save the plan in xml format if on 2005, copy and paste into excel if 2000
    if you have an old backup of the db,
    restore it somewhere, and look at the execution there too

    understand that just because a queries runs fine at one point in time does not mean it will continue to, this is because your data distribution is changing

    for the query above, what are the indexes on RETURN_TRAN ?
    is it just on SM_CODE, or a compound index on SM_CODE followed by GPNO

Share This Page