SQL Server insert slowly

Question

I have two servers on which I installed SQL Server 2008

• Production: RAID 1 on SCSI drives
• Test: IDE Drive

When I try to execute a script with approximately 35,000 inserts, I need 30 seconds on the test server, and instead more than 2 minutes on the production server! Does anyone know why such a difference? I mean, the DBs are configured the same way, and the production server also has RAID configuration, better processor and memory ...

THANKS!

Answer 1

Remember that RAID 1 is designed for redundancy, and the speed depends on the scenario. 35k inserts can lead to heavy random write load, slowing down performance.

How do you insert these rows, they are wrapped in a SQL transaction? If this is not the case, then be aware that transaction cache requests and packet disk updates, significantly increasing speed.

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

Answer 2

What are the indexes and additions on the server? You may need to rebuild your large-volume indexes on your pages and / or consider which indexes you really need. If you want to run a quick test, try ALTER INDEX ALL ON dbname.dbo.tablename REORGANIZE.

Also consider using on a production server. In your test, you are probably (or very few) the only person to read / write. Get an idea of ​​what else is going on in db while you do this insert.

If both of them do not work, start some monitoring on the production server and check if they are blocked by other processes.

Answer 3

Perhaps some other programs run on a production server that consumes resources such as hard drive and processor.

Also on the production server, the script execution time at the same time is executed in the database and some queries are executed.

Answer 4

There are three (high-level) questions:

• Actions on the server compared to available resources: it sounds like (judging by your answers) that this is not a problem.
• Configuring your indexes: again, it doesn't seem like a problem if the development environment is really identical in configuration (as it should be).
• Amount of data compared to how thoroughly your indexes

I think the third question may be your problem. Understand that the more (non-clustered) indexes you put in your table (and the more complex they are), the slower your data manipulation will be. Indexes (in general) are a trade-off between query speed and modification speed. Obviously, this is a generalization, and tuning is always required, but overall it is true.

Compare the amount of data in the two environments; if your production environment has a significant amount more (or if your table is heavily indexed), then this may be very good for you.

Answer 5

It’s actually hard to say.

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

This will be my first idea.

Answer 6

If you also make choices in the database at the same time, make sure that they are executed using "with (nolock)"

Answer 7

What is the amount of existing data on two servers?

INSERT time will expand depending on the number of rows in the table and the number of indexes. If the test server table contains fewer rows preceding the INSERT than the production server, INSERT is expected to run faster.