13 Maret 2012 9:44
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.
13 Maret 2012 10:05Penjawab Pertanyaan
Is that index CI or NCI?
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
- Diedit oleh Uri DimantMVP, Editor 13 Maret 2012 10:05
13 Maret 2012 10:53This covers both types and also PK's as well.
13 Maret 2012 11:41
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
13 Maret 2012 12:04
It will definitively help. I do set MADXDOP for the indexes rebuild tasks
ALTER INDEX indexname ON schemaname.tablename REBUILD WITH (ONLINE,MAXDOP =0)
13 Maret 2012 14:09
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."