Maxdop and index rebuild on compressed indexes

Question

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.

Thanks.

Answer 1

Is that index CI or NCI?

http://msdn.microsoft.com/en-us/library/cc280449.aspx

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

This covers both types and also PK's as well.

Answer 3

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

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 4

Hi

It will definitively help. I do set MADXDOP for the indexes rebuild tasks

 ALTER INDEX indexname ON schemaname.tablename REBUILD WITH (ONLINE,MAXDOP =0)

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 5

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
http://support.microsoft.com/kb/329204

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."

Configuring MAXDOP
http://blogs.msdn.com/b/cindygross/archive/2011/01/28/the-ins-and-outs-of-maxdop.aspx

---

Jon