'CREATE INDEX' Statement...Faster?

I am indexing a 32 millon row table with a unqine clustered index and
the run time for the create index statement is over 4 hours long. This
runs is too slow and may cost me a job If i cant find a faster way to
create index on large table.
Can anyone help.Hi
What is the version of SQL Server?
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||i don't think it would be faster. Clustered Index is the way data is stored
physically in the hard disk.
bye!
"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
>|||Setting the database to simple recovery should improve compared to full recovery. At least it will
cut down on the logging. But the data shuffling still has to occur of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:15C6F565-D67E-4912-8424-BFBDF0EC88A2@.microsoft.com...
>i don't think it would be faster. Clustered Index is the way data is stored
> physically in the hard disk.
> bye!
> "GreenHillCourt@.gmail.com" wrote:
>> I am indexing a 32 millon row table with a unqine clustered index and
>> the run time for the create index statement is over 4 hours long. This
>> runs is too slow and may cost me a job If i cant find a faster way to
>> create index on large table.
>> Can anyone help.|||<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
As others have said, if you're creating the clustered index, not much you
can do other than perhaps look at a faster disk subsystem (say raid 10 vs
RAID 5, etc.)
If you mean you're creating a non-clustered index on a table that has an
existing clustered index, one thing that can certainly help is putting the
non-clustered index on its own set of disks.
> Can anyone help.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Have you tried creating the index without clustering? If I'm not mistaken,
it should then not have to physically re-order the data.
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
- drop all unnecessary nonclustered indexes
- if possible, connect in single user mode
- add extra memory to the server, don't "pin" tables
- make sure you have enough I/O and bandwidth, both for your table, the
TempDB and the log file
- make sure you have enough free space before you start, at least 1.5
times the table size
- set the database recovery mode to simple
- choose an appropriate fillfactor. Any value below 70 is probably a bad
idea
4 hours seems excessive. How much space does the table occupy (before
you start)? What is your I/O system (number of disks, etc.)?
--
Gert-Jan
SQL Server MVP