SQL Server insert slow

Question

Hi,

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

Perhaps some other programs are running on the production server that is consuming the resources like hard-disk and processor.

Also on the production server the time when you are executing the scripts at the same time operations are being performed on the DB and some queries are executing.

Answer 2

What are the indexes and padding on the server? You may need to rebuild your indexes with more space in your pages and/or consider which indexes you really need. If you want a quick test try ALTER INDEX ALL ON dbname.dbo.tablename REORGANIZE.

Also consider the usage on the production server. In your test it's likely you (or very few) are the only person read/writing. Get an idea for what else is going on in the db while you are doing this insert.

If both of those fail, run some monitoring on the production server and see if it's being slammed by other processes.

Answer 3

Really hard to say.

First - yes, production may have more resources, but are they AVAILABLE? Or is production already running close to limit with the other stuff when you hit it? It is, after all, production.

This would be like my first idea.

Answer 4

There are three (high-level) issues to consider:

1. Activity on the server vs. available resources: it sounds like (judging from your responses) that this isn't an issue
2. Configuration of your indexes: again, it sounds like this isn't an issue if the development environment truly is identical in configuration (as it should be)
3. The volume of data vs. how thorough your indexes are

I think the third point might be your issue. Realize that the more (non-clustered) indexes you place on your table (and the more complex they are), the slower your data manipulation is going to be. Indexes are (in general) a tradeoff between query speed and modification speed. Obviously, this is a generalization and tuning is always required, but it's true in general.

Compare how much data is in the two environments; if the production environment has a substantial amount more (or if your table is highly indexed), then this could very well be your issue.

Answer 5

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.

Answer 6

If you also do selects on the database at the same time, make sure they are done with "with(nolock)"

Answer 7

What is the volume of existing data in the two servers?

Time to INSERT will expand depending on the number of rows already in the table and the number of indexes. If your test server table contains fewer rows pre-INSERT than the production server, the INSERT would be expected to execute more quickly there.