Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griffso there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
--
(Please reply only to the newsgroup)