Possible overhead of adding columns in a table without index in SQL Server

Question

My question somehow relates to this one Performance considerations when adding a new column to a big table in Sql Server, but i still think there are a couple of differences so i'll ask anyways.

We've got a relatively large table (20m records, ~20 columns), which is the most frequently used table in our DB. We've got an index on that table which, judging by its name, was proposed to us by Azure advisor thing (or whatever it's called) and it has all the columns in this table as "included columns".

Now we need to add 2 more columns in this table. It is not planned to use them in the results we get in our most frequent query (that's the one our god-index is for). These columns will be nullable and contain 2 additional IDs which could be used to uniquely identify a record in this table.

I would like to know whether our ideas are correct or not. Just for reference we use Azure SQL server and our DB is on the P2 tier.

1. Does adding a NULL-able column in such a big table cause a noticable DB-load spike by itself? My guess it shouldn't.

2. Does making an index on such a column cause a noticable DB-load spike? My guess it shouldn't do that either.

3. Would including these 2 to our god-index might cause a DB-load spike? I guess it would because this will make index to rebuild?

4. Since we are using the Entity Framework which automatically queries all the properties of the entity, would it decrease the performance if we do not include these 2 columns on the index but they will be queried as a part of our "the most frequent" query?

5. Might the creation of a new 1-to-1 table be considered as a good alternative which would allow us to keep existing things as they are and use the new columns only for cases where we need them?

Answer 1

Does adding a NULL-able column in such a big table cause a noticeable DB-load spike by itself? My guess it shouldn't.

Sounds right. But restore a backup and test.

Does making an index on such a column cause a noticable DB-load spike? My guess it shouldn't do that either.

Sounds right. But restore a backup and test.

Would including these 2 to our god-index might cause a DB-load spike? I guess it would because this will make index to rebuild?

Sounds right. But restore a backup and test.

Since we are using the Entity Framework which automatically queries all the properties of the entity, would it decrease the performance if we do not include these 2 columns on the index but they will be queried as a part of our "the most frequent" query?

If your most frequent query needs that index, and you're adding two columns to the query then it will add some cost. You should test. If that query returns only a few rows, then it may not matter much.

Might the creation of a new 1-to-1 table be considered as a good alternative which would allow us to keep existing things as they are and use the new columns only for cases where we need them?

Perhaps, but I would probably just evaluate the cost and treat this as a maintence-window change if it's expensive. You can also use Table Splitting in EF to map the most-used columns to a main entity, and the less-used one to a related entity, while keeping all the columns on one table in SQL Server.