We have a table in our MS SQL database that has billions of rows, and it is about 3.5 TB. Today in fact, the primary key which is INT reached its limit. I tried to change it to BIGINT using ALTER TABLE statement. However, this operation is taking over 7 hours, and it has not yet finished.

This table serves as historical rates for energy market. We do some run special queries on this table for showing rates over a time period for example. So, we have very special and specific indexes on this table to server what the user interface needs.

My questions to the community are:

1. The primary key on this table is never used. Does it make sense to remove it?
2. Whenever I add an index, it can take hours. Is this expected/normal?
3. I've done some research and I know that I can split this table into multiple file groups. Can this be done on an existing table? Will this improve performance? Will I need to write my queries differently to access data from different time periods?

Any other suggestions is very much appreciated.

CodePudding user response：

How to manage a huge SQL table?

With manage being the keyword, Stu is right that you may benefit by looking into table and index partitioning. It is helpful for improving the management of large tables for many DDL type of changes, and with a table your size, it's probably worth looking into. (It's not a tool meant for improving DQL and DML query performance, so much though.)

Aside from that, depending on the change you're trying to deploy, you may have to get crafty, and the solution may vary. For your specific issue of converting the primary key from an INT to a BIGINT, one possible solution would be the following:

1. Add a new nullable BIGINT column to your table. This should be rather immediate as it's only a meta-data change.

2. Incrementally set the new column to the value of the old column.

3. You can do this in smaller batches (e.g. 100,000 rows or less at a time).

4. These are one of the few times where an iterative approach is probably helpful. You can setup a loop that updates a batch of rows at a time, and then calls WAITFOR DELAY 'hh:mm:ss' for a desired amount of breathing time between iterations. This will allow other queries to be able to access the table in-between iterations, by reducing continuous lock contention.

5. Once the table gets close to finishing populating the new column, this is where it gets a little tricky. You'll want to schedule an outage window to ensure no new values are generated in the old column. Or you can add a trigger to the table to handle copying new values coming in, but the previous option is preferable, in my opinion.

6. Sync up the last batch of INT primary keys to the new BIGINT column.

7. Rename or drop the old primary key column.

8. Rename the new primary key column to the old column's old name. If you want to do this programmatically, you can use sp_rename.

Depending on how much runway you have left on your current INT primary key column vs how much downtime you can afford if it runs out, you may be better off just waiting on your current conversion process. Though unfortunately that can take many more hours or even days to finish with a table your size, depending on how many indexes are on the table too.

The benefit in doing the above process is you allow better concurrency your table, while other processes and queries try to access it. If you can't use it now, at least you can consider it next time. Though hopefully next time either the appropriate data type can be determined upfront or at least converted to much sooner, depending on the rate of your transactional data ingestion.

Additionally optimistic concurrency via Read Committed Snapshot Isolation would help with general concurrency in your database, if you can implement it. Especially for solutions like the above, that involve a series of writing a lot of data.

1. The primary key on this table is never used. Does it make sense to remove it?

Not sure what you mean by "never used". Even if you don't normally query with predicates referencing it, it should still be there to enforce your data integrity. If your data doesn't have a natural primary key, that's a sign of a data model issue.

2. Whenever I add an index, it can take hours. Is this expected/normal?

Yes, with data the size of your table, this is pretty typical. And changes to the clustered index (which I'm assuming is defined on your primary key column) can take longer than nonclustered indexes, because the clustered index key is stored in every nonclustered index on the table. It's also literally the representation of the table itself, sorted on those key fields.

If you have Enterprise Edition of SQL Server, then you can take advantage of online index operations to reduce contention on your index and table. The overall runtime will be slower because it runs the operations single threaded, but it won't actually block the index for majority of the time, minimizing contention.