Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a non-clustered index with 63 Million leaf level rows. Currently it does not have any included columns. I would like to add one included column while the site is online. Will this significantly affect performance if I do it through the SSMS GUI?

share|improve this question
There was some badly written nonsense in my answer Joe, hence removed. If no-one else has responded with an answer tomorrow, I'll try again! – Mark Storey-Smith Jan 23 '12 at 23:57
Is your question specific to doing it in the GUI or just in general about adding the included field? – JNK♦ Jan 24 '12 at 0:15
@JNK Not specific to GUI (that was more curiosity than anything) – Joe Philllips Jan 24 '12 at 17:17

2 Answers

up vote 7 down vote accepted

Why would you do anything through the SSMS GUI???

Try doing it as a pro, by a script. Your best option is to create the index online with drop existing, changing the definition to match the new desired schema in the process:

create index ... on ... include ... with (online = on, drop_existing = on);

This, obviously, is subject to the usual restrictions for ONLINE operations, and will cause the normal overhead caused by an ONLINE index create/rebuild operation. But the good news is that the operation will be online, will not block existing queries and the old index will be available for queries while the new one is being built. There will be a query drain stage at the end when all queries will block while the indexes are being swapped, but that should be a very fast operation (as long as there are not long running user transactions blocking the swap itself).

share|improve this answer
1  
btw, in an ideal world you should be able to test the impact in a staging/test environment that matches the production one, under a realistic workload... – Remus Rusanu Jan 24 '12 at 0:29
2  
+1 for doing it as a pro, by a script. Reproducability is always a good thing. – JNK♦ Jan 24 '12 at 0:31
Thanks for the great response. I'll try this out on a test server. – Joe Philllips Jan 24 '12 at 13:35
up vote 4 down vote

It won't really matter if you do it through the GUI or through a script. I believe it will take into account the options already on the index unless you specify ONLINE either off or on.

No matter how you do it, though, it will definitely affect performance. Mostly through IO and file growth, but it will build a new version of the index beside the old one, then drop the old one once complete. This will take resources away from other operations. It won't BLOCK access to the current index if ONLINE = ON but will affect other things on the server.

Also bear in mind ONLINE is only available with Developer or Enterprise versions of SQL Server.

Updated: ONLINE is available in DataCenter Edition as well

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.