ansaurus

Question

Does creating a nonclustered index on a SQL Server 2005 table prevent selects?

Answer 1

+1 A:

There's more information on the msdn articles about CREATE INDEX and online index operations that has lots of information about it.

There should be no issues with it, if you're only doing SELECTs and UPDATEs. Not so sure about backups, maybe best to try it on a test system and see?

thecoop 2009-12-16 17:52:18

Answer 2

+4 A:

Online index creation and rebuild are available only on Enterprise Edition. See How Online Index Operations Work and Guidelines for Performing Online Index Operations.

There are some restrictions, most notable ones being:

clustered index must be created/rebuilt offline if they contain any BLOB fields (image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml).
intitial clustered index on a view must be created offline.

You must ensure your database have enough space to performa the online inde xoperation, as it requires about 1.5 times the size of the table in addition to the current size. During the online index creation the table exist twice in the database, hence the extra space needed.

Since your case falls in the excluded category (initial clustered index on a view) then you need not worry about online indexes. You must use an offline index operation.

BTW you must also be aware that indexed views are considered by the optimizer only in Enterprise Edition. On lower editions one must specify the NOEXPAND clause in the view to leverage a possible index on the view.

Remus Rusanu 2009-12-16 18:05:12

Thanks I didn't realize that. I plan on creating two indexes on this view, the initial clustered that you mentioned and a nonclustered after that. Do you know of anyway to keep the data in my view readable while I create the initial clustered?BTW I am running enterprise edition.

Abe Miessler 2009-12-16 18:36:27

The clustered index view will lock rows in the table, thus blocking reads of the locked rows (under default serialisation level). How big is the table? Are we talking few seconds or some hours of index creation time? You may consider enabling read commited snapshot, this will allow the SELECTs to go ahead unaffected. But this has a global impact on the system, specially on tempdb, so you should definetely test beforehand.

Remus Rusanu 2009-12-16 18:43:17

The table has over 9 million records. I'm thinking that the indexes would take about 15 mins to create. If people cannot read for 15 mins then i'd have a problem. Could you be more specific about the impact that enabling read commited would have on the system? How would you recommend testing before hand?

Abe Miessler 2009-12-16 18:47:55

You may also consider creating a database snapshot and temporarily redirect the connections to that. Depends on how many updates the database will receive during the operation and whether the application has to show these recent updates or not. If the app can live, for the duration of the operation, with an image of the database at the moment you start the operation (ie. the 'snapshot'), then you can definetly use this.

Remus Rusanu 2009-12-16 18:48:02

Read committed snapshot: http://msdn.microsoft.com/en-us/library/ms175095.aspx . Impact on tempdb: http://msdn.microsoft.com/en-us/library/ms175492.aspx

Remus Rusanu 2009-12-16 18:50:21

The database is used for reporting and is only updated once per night. I am working on getting a snapshot to work on but it's looking like that might take a couple of days and I'd like to try a few things in the mean time.

Abe Miessler 2009-12-16 18:50:44

`create database foo as snapshot of bar` is almost instantenous. There is no copy involved, the snapshot uses the OS sparse files API to make a copy-on-write on a page-by-page basis of the original database. Is perfect for reporting.

Remus Rusanu 2009-12-16 18:53:13

ansaurus

tags:

views:

answers:

Does creating a nonclustered index on a SQL Server 2005 table prevent selects?

related questions