CODEWITHSUNDEEP
sqlsql-serverinsertperformance
andrew0007
andrew0007

Reputation: 1265

SQL Server insert slow

I have two servers where I installed SQL Server 2008

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!

Upvotes: 6

Views: 6585

Answers (7)

Larry Lustig
Larry Lustig

Reputation: 50970

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.

Upvotes: 0

Ivo
Ivo

Reputation: 3436

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

Upvotes: 0

invert
invert

Reputation: 2076

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.

Upvotes: 3

Adam Robinson
Adam Robinson

Reputation: 185643

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.

Upvotes: 2

Russell Steen
Russell Steen

Reputation: 6612

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.

Upvotes: 3

TomTom
TomTom

Reputation: 62093

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.

Upvotes: 0

HotTester
HotTester

Reputation: 5768

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.

Upvotes: 2

Related Questions