Sql Server – Question about optimizing table copy from one database to another
Question for everyone:
What steps could we take to optimize table copy from one database to another?
A few days ago, I was running a large table copy from one database to another. Something like this:
1
2
| SELECT Col1, Col2 INTO OtherDB.dbo.TableCopyFROM ThisDB.dbo.Table |
This is a huge table, it has close to 6 billion records and the table is close to 300 GB in size. So, I wanted to find a way to optimize such a huge data transfer.
Some of the things considered:
- Table hints / Query hints to take exclusive lock on the table and use parallelism, but it wasn’t any faster.
- Data Compression to reduce the I/O. It was a bit faster, but still took a long time.
- Multiple data files on separate drives. Made it faster, but not fast enough.
- Made sure the MDF/NDF / LDF files have enough space so there is no AUTO_GROWTH
This is my second question: When back up of entire database can complete it 60 minutes, why does just one table copy take more than that? This is a lab environment, so there is no external traffic.