Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Very High CPU Usage by CHECKDB Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 8:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 8:57 AM
Points: 417, Visits: 872
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?
Post #1434291
Posted Friday, March 22, 2013 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #1434322
Posted Friday, March 22, 2013 9:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 8:57 AM
Points: 417, Visits: 872
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

Post #1434328
Posted Friday, March 22, 2013 10:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #1434363
Posted Friday, March 22, 2013 11:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 8:57 AM
Points: 417, Visits: 872
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.
Post #1434419
Posted Friday, March 22, 2013 11:26 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 5,888, Visits: 13,062
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?


---------------------------------------------------------------------

Post #1434430
Posted Friday, March 22, 2013 11:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #1434440
Posted Friday, March 22, 2013 11:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 8:57 AM
Points: 417, Visits: 872
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.
Post #1434443
Posted Friday, March 22, 2013 11:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 5,888, Visits: 13,062
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


---------------------------------------------------------------------

Post #1434445
Posted Friday, March 22, 2013 11:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 8:57 AM
Points: 417, Visits: 872
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
Post #1434446
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse