Tuesday, March 13, 2012 9:44 AM
I currently run reindexing for a compressed index, and what I have noticed is the occasional CPU spikes reaching 80 - 100%. Now I do know that compression does use CPU power quite a lot, however the server has a maxdop setting of 6 and actually has 32 cores on the box. I would like to know whether specifying maxdrop = 0 would make any difference.
Tuesday, March 13, 2012 10:05 AMAnswerer
Is that index CI or NCI?
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
- Edited by Uri DimantMVP, Editor Tuesday, March 13, 2012 10:05 AM
Tuesday, March 13, 2012 10:53 AMThis covers both types and also PK's as well.
Tuesday, March 13, 2012 11:41 AM
It should help. Instead of changing the maxdop at server level, please try this :- http://technet.microsoft.com/en-us/library/ms189329.aspx
To avoid high CPU , you can also try resource governor :- http://blogs.msdn.com/b/sqlserverfaq/archive/2011/06/15/whats-the-backup-compression-in-sql-server-2008-and-how-it-can-help-to-optimize-the-speed-size-of-the-backup.aspx
Tuesday, March 13, 2012 12:04 PM
It will definitively help. I do set MADXDOP for the indexes rebuild tasks
ALTER INDEX indexname ON schemaname.tablename REBUILD WITH (ONLINE,MAXDOP =0)
Tuesday, March 13, 2012 2:09 PM
MAXDOP = 0 , means that if SQL Server chooses to use parallelism with a query it will use all available processors.
What processor(s) do you have installed? as your maxdop setting "recommended value" will change depending on various things. (e.g. hyperthreading, numa etc)
General guidelines to use to configure the MAXDOP option
If I link Cindy Gross's example:
"As an example, say you have 4 quad cores with hyperthreading on. That comes to 4x4x2=32 visible schedulers available for non-DAC SQL Server queries."