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?
Sql-server – add “Included Columns” to an index without affecting performance
indexsql-server-2008-r2
Related Question
- Sql-server – computed columns, index, clustered index and covering index
- Sql-server – difference between having many non clustered index with single columns and with combination of many columns
- Sql-server – Finding exact row in Clustered index leaf page
- SQL Server – Does Index Intersection Work with Included Columns?
- Non-Clustered Index vs Clustered Index – Are They Equivalent?
- SQL Server Indexing – Reasons to Have Both Index with Included Columns and One Without
- SQL Server – Clustered Index Fragmentation vs Index with Included Columns Fragmentation
- Save Performance with Large Update on Index with Included Column in SQL Server
Best Answer
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).