SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Very High CPU Usage by CHECKDB


Very High CPU Usage by CHECKDB

Author
Message
apat
apat
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 894
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?
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39516 Visits: 32632
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
apat
apat
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 894
Thanks Grant. Here are the settings:

affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
awe enabled 0 1 0 0
max degree parallelism 0 1024 0 0
max server memory (MB) 16 2147483647 98304 98304
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 98304 98304
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62116 Visits: 19101
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
apat
apat
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 894
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.
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10286 Visits: 13687
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?

---------------------------------------------------------------------
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39516 Visits: 32632
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
apat
apat
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 894
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.
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10286 Visits: 13687
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

---------------------------------------------------------------------
apat
apat
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 894
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search