October 20, 2008 at 8:56 am
Hi all,
I have a table that has 20 million records and I need to create an non clustered index on one of the columns. I understand it's desirable to create the index offline but not in my case. The DB needs to be up and running 24-7 so I probably need to create an online index. Is this possible to create an online index? Furthermore, are there any disadvantages or risks while creating an index online? Would the database table be locked and inaccessible during the index creation process? Is there a way to estimate how long the whole process (index creation) will take?
By the way, what are the standard ways to create non clustered index on large tables?
Any suggestions or links are very welcome ! Thanks in advance.
Thomas.
October 20, 2008 at 9:04 am
Hi,
Which version and edition of SQL server are you using?
The online/offline option is only available in SQL Server 2005 Enterprise Edition.
Chris.
Chris.
October 20, 2008 at 9:10 am
I think it's SQL Server 2005 Enterprise Edition (have to double check the server again). Sounds like I can only create offline index if I am not using Enterprise edition. Thanks !
Thomas
October 20, 2008 at 9:26 am
Thomas (10/20/2008)
I think it's SQL Server 2005 Enterprise Edition (have to double check the server again). Sounds like I can only create offline index if I am not using Enterprise edition. Thanks !
It's the other way around: You can only create indexes onlin if you are using SQL Server Enterprise, Developer, or Evaluation editions.
If you are not using SQL Server Enterprise, Developer, or Evaluation editions then you can only create indexes offline.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 11:01 am
I think you're a bit confused by online & offline. Neither one requires access to the database to be stopped. The offline option only heavily, negatively, affects the creation of clustered indexes. When creating a nonclustered index with ONLINE = OFF (the default) you'll get a shared (S) lock on the table which prevents inserts & updates, but not reads, for the duration of the creation.
The main point I'd suggest for speeding up nonclustered index creation is to have a good clustered index on the table. That's going to be your biggest asset.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply