SQL Server insert slow

Question

I have two servers where I installed SQL Server 2008

• Production: RAID 1 on SCSI disks
• Test: IDE disk

When I try to execute a script with about 35.000 inserts, on the test server I need 30 sec and instead on the production server more than 2 min! Does anybody know why such difference? I mean, the DB are configured in the same way and the production server has also a RAID config, a better processor and memory...

THANKS!

Answer 1

Be aware that RAID 1 is for redundancy, and speed varies depending on the scenario. 35k inserts could cause a heavy random write overhead, slowing down performance.

How are you inserting these rows, are they wrapped in a SQL Transaction? If not already, then know that transactions cache queries and does batch disk updates, greatly increasing speed.

If it's via a SQL script file, wrap the inserts in BEGIN TRANSACTION / END TRANSACTION. For so many records importing from file is probably better.