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?

  • 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! Jan 23, 2012 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, 2012 at 0:15
  • @JNK Not specific to GUI (that was more curiosity than anything) Jan 24, 2012 at 17:17

2 Answers 2


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).

  • 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... Jan 24, 2012 at 0:29
  • 2
    +1 for doing it as a pro, by a script. Reproducability is always a good thing.
    – JNK
    Jan 24, 2012 at 0:31
  • Thanks for the great response. I'll try this out on a test server. Jan 24, 2012 at 13:35

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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