March 22, 2013 at 8:14 am
We just build new 2 node active passive cluster windows 2008 R2. SQL 2008 R2 Enterprise edition on 64 CPU and 128 GB RAM. Database migration under testing now.
No one else is using this server. Now when I run DBCC CHECKDB on a 20GB db, CPU spikes to 70-80 constantly. No other processes are running at all. As soon as I cancel dbcc command, CPU comes down to 0. Also when I run the same on existing PROD servers (with many other processes running) it doesnt affect CPU performance.
I know dbcc checkdb is an resource intensive operation but nothing else running at all, why is it taking too much CPU. Any idea?
March 22, 2013 at 9:14 am
Nope. That sounds pretty abnormal. I'd be concerned that there was something wrong with the server or the SQL Server instance. Check the configuration to ensure that you have memory configured correctly and that it's getting seen by the instance as expected, that you don't have an setting for processor affinity or something in that area. Not sure exactly, but I'd go through the whole thing to validate it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 22, 2013 at 9:20 am
Thanks Grant. Here are the settings:
affinity I/O mask -214748364821474836470 0
affinity mask -214748364821474836470 0
affinity64 I/O mask -214748364821474836470 0
affinity64 mask -214748364821474836470 0
awe enabled 0 1 0 0
max degree parallelism 0 1024 0 0
max server memory (MB)16 214748364798304 98304
min memory per query (KB)512 21474836471024 1024
min server memory (MB)0 214748364798304 98304
March 22, 2013 at 10:07 am
If no other processes are running, do you care? You want the server to use the resources it has, not skimp. CHECKDB isn't set to use 10% CPU regardless of load. SQL Server uses the resources it can to get things done quickly.
March 22, 2013 at 11:12 am
True, but what I was worried about, when the server goes live and there is a lot of user load we dont want to run into CPU issues.
March 22, 2013 at 11:26 am
let the dbcc run through to completion, is it faster than on the old server?
look at the parallelism setting, does overall CPU come down if that is scaled back from use all processors.
whats the thinking behind min and max memory set the same? Is LPIM set?
---------------------------------------------------------------------
March 22, 2013 at 11:30 am
I don't quite get how those numbers are laying out. Sorry.
You have max degree of parallelism set to 0, 1024, 0, 0? It's just a single value.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 22, 2013 at 11:32 am
I did run dbcc through completion and not really faster compared to old server (SQL 2005, less hardware).
Parallelism is set to 0. I can try changing to see the effect on CPU performance but its default to 0 now.
LPIM is not set.
March 22, 2013 at 11:35 am
Grant Fritchey (3/22/2013)
I don't quite get how those numbers are laying out. Sorry.You have max degree of parallelism set to 0, 1024, 0, 0? It's just a single value.
its a textual representation of sp_configure results
name minimum maximum config_value run_value
---------------------------------------------------------------------
March 22, 2013 at 11:35 am
I copied the whole output from sp_configure. Parallelism is set to 0.
name- max degree of parallelism
minimum - 0
maximum - 1024
config_value- 0
run_value - 0
March 25, 2013 at 1:29 pm
Any more suggestions anyone?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply