June 20, 2014 at 6:20 am
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.
June 20, 2014 at 6:49 am
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
June 20, 2014 at 7:47 am
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.
June 20, 2014 at 8:22 am
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