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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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).
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
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
Also bear in mind
Updated: ONLINE is available in DataCenter Edition as well