CPU Usage reached 100%

  • Hi All,

    There is almost 5 to 6 Database is there in SERVER. and server configuration is

    RAM: 16GB

    OS: Windows Server 2008R2

    SQL Server: SqlServer 2008R2

    Processor: quad core processor

    When will be the heavy load on the server the CPU usage reached 100% and for sometime(approx 1-2 hr) it will not reduced. User will start compalning that they are not able to open application. We found some query which was taking more time and tuned that also. And apart from this I found wait type CXPACKET also and I have changed MAXDOP from 0 to 1 also. But issue is still same .

    Can anyone please help me, apart from this what i need to check to increase server performance.

  • Increase the Cost Threshold for Parallelism settings.

    If that also does not help then you will have to find the offending queries that are causing parallelism which maybe due to missing indexes,stale stats or a very very badly written query.

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • Thanks for the reply. by default cost threshhold of parallelism is 5. what will happen, if i changed the setting to 10.

    in which basis , we can say we need to change the cost threshhold of parallelism setting. what does 5 means here.

    Please help me, or send me any link where suffecient information is given.

  • http://technet.microsoft.com/en-us/library/ms188603%28v=SQL.105%29.aspx

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply