SQL Server Performance Forum – Threads Archive
Parallel Index operations and MAX DOPHi all, I understand that Parallel Index Operations are available only in Enterprise Edition by setting the Max DOP to less than default. I think by default sql will use all CPUs available on the box, so this Parallel Index Operations would only apply on the servers not dedicated to SQL Server. Otherwise you’d want to stick with the default and use all CPUs available anyway. Just want to kinda clarify this to myself as well as provoke some thoughts. Thanks
the default is to use all processors, not sure about edition restrictions.
usually when building indexes, its during a maintenance window, so there is no need to limit parallelism.
i suppose if you had to build an index during normal business hours you might want to restrict parallelims
I think that is the intention, since Enterprise Edition supports on-line index rebuild. So what you’re saying is you might want to restrict parallelim during normal business hours because yu want other CPUs dedicated to normal sql processes during index rebuild?
always restrict to number of physical cores (dual core count as 2, 2 logical procs count as 1) for big systems, 4 procs & more, that would be my recommendation,
if some one wants to run a report during normal hours, ok, but the guys posting transactions are more important, let the guy who wants the report wait, ie, tell the boss to get coffee