SQL Server Performance

Performance extremely slow after reboot

Discussion in 'ALL SQL SERVER QUESTIONS' started by foad reddahi, Mar 10, 2013.

  1. foad reddahi New Member

    Hi guys,

    weird thing happened to one of our DB production servers. Monday morning, one of our database servers was rebooted because of an DC [Domain Controller]issue.

    Reboot happened on monday during the day. The rest of the day, no issues.
    Next day, as soon as clients started connecting\working, they informed us that the application was reacting extremely slow.

    First thought was: well with the reboot, temp db is being flushed\recreated, buffer pool has been flushed so it takes a while before we get normal performance.
    By friday the performance issue was still existing. This server has 24 cores and 64 gb memory. Only thing running on this server are about 6 databases with a total of 500 gb. 2 majors, each 200gb and 4 small databases.

    Things I've done the past 48 hours
    - I started with hardware checks, no errors.
    - I 've rebuild the indexes
    - Altered parallelisme value. Since the reboot i saw that the latch was reaching values of 7000ms.
    I don't know what the value was last week when everything was running fine but this should be somewhere between 0 and 50.
    When i checked the settings, i saw that the Parallelisme value was 24 [as the server has 24 cores], i decided to lower this to 4. The latch value decreased immediately to 0 - 10m.

    I've spend the whole weekend looking for the problem but I can't figure out wether the reboot caused this issue or not. Anyone any ideas, any experience with this kind of problem?

    Thanks in advance!
  2. foad reddahi New Member

    Some new information. The performance is now relatively fine but one thing remains strange.

    If i take a query and i execute it multiple times, the execution time is always the same. 5 seconds.
    For some strange reason after several executions, the query suddenly takes up to 40 second to execute, when i then execute it again, it suddenly executes back within 5 second.

    Which is weird because the query should use the same explain plan everytime, so why should it take up to 40 seconds every 7-8 executions?
  3. Luis Martin Moderator

    May be some lock?
    foad reddahi likes this.
  4. foad reddahi New Member

    well that's what i thought, until i executed the same query on a copy of the database. As i'm the only one who was logged on to that database, locks were out of the question.
    So that means, the problem is not database linked, but probably due to either server or sql server properties.

    can;t figure it out
  5. Luis Martin Moderator

    I would like to suggesto to try using only one procesor for parallelims.
  6. Shehap MVP, MCTS, MCITP SQL Server

    Adding a little bit more for what Luis said , your query is not powerful enough coz it consumes huge volume of IO or CPU resources ending up with a culprit point of performance if no adequate resources are available for its execution just like you case, so it might be useful if you try the option sent by Luis but for this query specifically using option (maxdop 1) , option (maxdop 2) or option (maxdop 4)

    Please try any of then and If it is still not coming ,you can check also a quite similar case at this post :http://www.sql-server-performance.c...ue-with-several-statements.33995/#post-172391

    Also if you need more help regarding your query, please share it here with its query execution plans and IO statistics by using SET STATISTICS IO ON at the beginning of your query

Share This Page