I’ve recently been working with a company that has some relatively large tables stored as heaps. Adding clustered indexes to a few of these tables has been relatively difficult, as they’re very active and, for reasons that I don’t need to get into here, we have very limited drive space.
They have SQL Server Enterprise Edition, which allows us to add indexes online, which is awesome, but some tables have still been troublesome to add indexes to. Here’s a few of the tips that worked out for me to get this task done.
- If you can drop non-clustered indexes first and re-add them later, go for it. If you can’t, just understand that it will take additional time (and additional logging!) to rebuild those nonclustered indexes during the process of building the clustered index.
- If you’re on a machine with multiple cores (and who isn’t these days), consider including MAXDOP=1 (or 2, or 4, whatever works for you) in your “WITH” clause as part of the CREATE INDEX statement. In my case, not including this appeared to over-extend the SQL Sever to the task of building the index and other activity on the server suffered.
CREATE UNIQUE CLUSTERED INDEX [index_name] ON [dbo].[table_name] ( [row_id] ASC ) WITH (MAXDOP=2,ONLINE = ON) ON [PRIMARY]
- If blocking is still a problem for you, consider disabling lock escalation while you’re creating the index. SQL 2005 required a trace flag and other settings to turn on this functionality. With SQL 2008 or above, you can do this with the following code:
ALTER TABLE [table_name] SET (LOCK_ESCALATION=DISABLE) -- don't forget to re-enable lock escalation when you're done ALTER TABLE [table_name] SET (LOCK_ESCALATION=TABLE)