Z
Z
zod ggs2018-01-26 16:30:38
SQL Server
zod ggs, 2018-01-26 16:30:38

Deleted an unused 65 GB Primary Key, how to return performance to DELETE/INSERT operations?

There is a table for 1,000,000,000 records (growing by 2-3 million per day), it has an ID field (int, autoincrement, PK).
Problem - the PK index weighs 65GB and this field is never used anywhere.
I removed the PK (on 20 cores and 256GB of memory it took 5 hours). The size of the database grew by 110GB(?), SELECT performance remained the same, but DELETE/INSERT performance dropped several times.
Colleagues say that without PK, the table turned into a "heap", and from this the performance of DELETE / INSERT will drop.
DELETE/INSERT performance is critical because the table fills through the trigger of another table.
There are indexes on other fields, but they cannot be made unique, and the only unique field is this unnecessary ID.
There are two more indexes, one for select and one for delete, both non-clustered.
Remove the ID field or change the type to a less gluttonous one? How to improve DELETE/INSERT performance? How to return a table from the "heap"?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question