Hi all,
I have problem whit an SQL Server 2012 Enterprise Core Edition running in Windows Server 2008, the CPU is exceeding 60%, "System: Context Switches/sec" is exceeding 8000, "System: Processor queue length" is exceeding 2, I set Max
dop degree is in 8. What other set I have to ch
SQL Server
January 30th, 2015 11:42pm
Hello,
Please determine the optimum maxdop setting using the SELECT statement provided on the following post.
http://blogs.msdn.com/b/sqltips/archive/2005/09/14/466387.aspx
Hope
Regards,
Alberto Morillo
SQLCoffee.com
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2015 12:01am
Exceeding 60% is not a problem by itself, does anything else seem to be a problem? Are users seeing slow response?
How many cores in the instance? How many active connections? What are your top waits?
What kind of work is it doing?
What about IO queues? Seconds/read? Page life expectancy? Paging rate?
Josh
January 31st, 2015 2:32am
Your context switches/sec is within the normal threshold value of 5000 per processor (assuming you have at least two processors). Processor queue length applies to all processors - you got to divide the process queue length by number of processors
you have and if that value is greater than 3, then you might be encountering CPU pressure. Unless your users are complaining or there is sudden peak in CPU utilization (from 20 to 60%), there is no need to worry.
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2015 8:23am
Josh
The processor is quad-core Amd opteron (tm)processor 8354, 4 core physical,
The instance has 350 seccion, and there is only DB, that work for Voip product
My top 10 wait
wait_type waiting_tasks_count wait_time_ms
CHECKPOINT_QUEUE 1628 106201319
SP_SERVER_DIAGNOSTICS_SLEEP 7102 107102162
LOGMGR_QUEUE 6983003 107106487
XE_DISPATCHER_WAIT 895 107288539
REQUEST_FOR_DEADLOCK_SEARCH 21441 107352130
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 26779 107367213
HADR_FILESTREAM_IOMGR_IOCOMPLETION 209657 107369523
DIRTY_PAGE_POLL 980940 107370314
XE_TIMER_EVENT 24175 107370416
DISPATCHER_QUEUE_SEMAPHORE 34593 111473725
LAZYWRITER_SLEEP 860662 429383713
Counter:
Buffer cache hit ratio allways 100%
Page Life Expectancy always above 300 seconds
January 31st, 2015 6:35pm
Alberto,
Very Good tool, Monday in the morning I will monitoring this and send you the information.
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2015 6:37pm
I have 4 processors
The counter System: Context Switches/sec is always above 5000.
January 31st, 2015 6:45pm
The query returned me that the optimal is 4, will change it.
Thanks
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2015 6:47pm
I have 4 processors
The counter System: Context Switches/sec is always above 5000
January 31st, 2015 7:17pm
The range is between 8000 and 11000
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2015 7:35pm
This topic is archived. No further replies will be accepted.
Other recent topics
