April 6, 2011 at 2:54 pm
I have a load process that creates a cluster index over a 10gb table with 160m rows. The create index runs off hours on a 16 core server. However looking at task manager on the server it is apparent that it is driving 1 core to 100% utilization, but not using any other cores. Disk utilization is 0% during the max cpu time. Are there any configuration options to look at to get it to utilize more of the processor during this index build?
thanks
April 6, 2011 at 3:16 pm
Hi,
what degree of parallelism is set on the server? was it changed?
Try to add the maxdop option on the create index command and analyse the differences in execution plans.
Greets
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 6, 2011 at 3:47 pm
maxdop is only available for index operations with enterprise edition
http://msdn.microsoft.com/en-us/library/ms189329(v=SQL.90).aspx
---------------------------------------------------------------------
April 7, 2011 at 9:12 am
Both processor affinity masks are Autoset and max worker threads is 0 (let server determine)
Max Degree of parallelism is 0 and cost threshold for parallelism is 5 (both defaults I think)
None of this has been changed. But I just became aware of the fact that it was only using one processor. That may have been how it has always worked. I'm trying to cut down the elapsed time.
I'm running 2008 standard edition 64 bit
Execution plan just shows one step "Create Index"
April 7, 2011 at 9:41 am
standard edition it will only ever use one processor.
---------------------------------------------------------------------
April 7, 2011 at 2:23 pm
Thanks for letting me know it not something I'm doing wrong
April 15, 2011 at 4:05 am
thought it was 4 and express supports only 1.. :unsure:
__________________________
Allzu viel ist ungesund...
April 15, 2011 at 4:12 am
Mr. Holio (4/15/2011)
thought it was 4 and express supports only 1.. :unsure:
You'r right. Here is the evidence from a "trusted" source ... 🙂
http://msdn.microsoft.com/en-us/library/ms143760%28v=SQL.100%29.aspx
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 15, 2011 at 4:15 am
read the whole thread, thats no of processors standard edition uses for index rebuilds, not how many it is licensed to use.
---------------------------------------------------------------------
April 15, 2011 at 5:13 am
george sibbald (4/15/2011)
read the whole thread, thats no of processors standard edition uses for index rebuilds, not how many it is licensed to use.
Oh yeah. By the way, I did read the thread but was not aware of the edition differences and thought it was not limited only to enterprise...learnt something new.
__________________________
Allzu viel ist ungesund...
April 15, 2011 at 5:45 am
But I can't imagine that microsoft limit the processors on index rebuilds but if you say so. However I would test it. But by the way, I have noticed that SQL Server 2008 R2 Developer support all cores during index creation. 🙂
But, George, sorry, you're right, that wasn't a evidence. 😎
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 15, 2011 at 5:52 am
as I do not seem to be trusted - from a 'trusted' source 🙂
http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
in the maxdop section -
'Parallel index operations are available only in SQL Server 2005 Enterprise Edition. '
- and developer edition would have enterprise features
---------------------------------------------------------------------
April 15, 2011 at 7:17 am
george sibbald (4/15/2011)
as I do not seem to be trusted - from a 'trusted' source 🙂http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
in the maxdop section -
'Parallel index operations are available only in SQL Server 2005 Enterprise Edition. '
- and developer edition would have enterprise features
Thanks, so I need not test this on weekend. 🙂
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 15, 2011 at 7:39 am
I've tested it - only 1 cpu was ever used.
---------------------------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply