Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  Create Index Performance

Author  Topic  puremike Starting Member 1 Post Posted - 2012-05-04 : 01:05:08 Hey Guys, I have a question about Create Index operation. 1. Sql Server 2008 Enterprise 64-bit 2. HP dl580G7 quad 10 core with Hyperthreading (Task Manager sees 80 Processors). 256 GB Ram 3. COnnected to EMC Vmax via two 8GB HBA Cards. Using Powerpath for Multipathing (See sustained transfer rates of up to 500 Mb/Sec) 4. Max Degree parallelsim = 0 5. Cost threshold for parallelism = 5 6. 850 GB DB 7. Table sig_events has 75 million rows When running the following statement, 64 of the processors will spike to 100% for about 10 seconds. (I know why all 80 aren't used.) After about 10 seconds all but 1 proc will go idle. That 1 proc will sit at 100% for a while, then drop to 0% and another proc will sit at 100% for a while. CREATE NONCLUSTERED INDEX [inx_sig_events9] ON [sig_events] (enterprise_id, practice_id, source1_id, source2_id, source3_id, source4_id, event_source_type) WITH FILLFACTOR = 90 ON NEXTGEN_INDEX_1 THis index will take about 10 minutes to create. My question is why doesn't it use all of the processors that it can for the duration of the process? Yes, I know 10 minutes is pretty quick. On my old Box it takes about 35 minutes to create, and that server is a 16-way Xeon 3.2 with 64 GB of RAM. data files on a 24 drive RAID 10, Indexes on a 10 drive RAID 10, Just for comparrison purposes. Any insight would be greatly appreciated. Thanks