Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is :

  • Have no index on the table whatsoever
  • Switch to simple logging (from full logging)

The test scenario that I use consists of 100 connections that each run an INSERT and then wait 0.1 seconds and then again, indefinitely. Each "inserter" logs execution time.

Looking at execution times I sometimes see 1.5 and even 10 seconds on an insert (as exceptional cases), otherwise a typical 0.2 seconds is what I see.

Further context :

  • SQL Server 2008 R2 Express (afaik the express version limits only DB size (4GB), RAM (1GB) and logical CPUs (1))
  • Machine : Laptop with 7200 RPM HDD, 8 GB RAM and 8 logical CPUs, hosting the server and the clients
  • The table consists of 1 BigInt ID (identity), an NVARCHAR(100) field, and NVARCHAR(MAX) (INSERTs on this have a 10k payload), and an NVARCHAR(MAX) (30k payload at INSERT) column(s)

Where should I look for further improvements to the performance of such a process?

share|improve this question
3  
Is there a need to insert 100 rows from 100 connections instead of from a single connection? You are paying a LOT in overhead for connecition initialization, row locking, etc. by having so many connections. SQL likes to do things in sets, not individual records. –  JNK Feb 24 '12 at 15:52
2  
Have you looked at SQLBulkCopy etc? –  gbn Feb 24 '12 at 15:55
1  
@AndreiRinea: The obvious bottleneck is "single HDD" because of Write Ahead Logging. Also, you probably have memory contention with SQL Server Express –  gbn Feb 24 '12 at 16:00
2  
This is very basic, but did you make sure that the auto growth properties are properly setup for the database files? –  yogirk Feb 24 '12 at 16:05
3  
If you have enough space, you might want to increase the data file auto growth setting to may be by 50 MB. Growing 1 mb at a time is inefficient. –  yogirk Feb 24 '12 at 16:17
show 12 more comments

2 Answers 2

up vote 5 down vote accepted

A lot of folks added a lot of good points in the comments.

1) Separate your transaction logs onto a different drive. That's going to be tough with a laptop. If you can't do that, get yourself an SSD for the laptop, and that should make your life considerably better.

2) Pre-grow your data and log files to a target amount. If you expect to add 1GB of data to your database, make your data and log files at least 1.5GB to start. Data and log file autogrowth are killers for performance and may very well be the reason you see these 10 second "chokes" in performance. SQL Management Studio has some built-in reports (I believe you right click on the DB, then select Reports -> Disk Usage) which should have a table of all of your autogrow events there.)

3) If you can batch your inserts on a per-client basis, then do so using the SqlBulkCopy or BULK INSERT statement.

4) There should be no reason why your tables shouldn't have a clustered primary key. SQL Server should be able to deal with a clustered index on your IDENTITY column in a fairly performant way.

share|improve this answer
    
Thanks! Really useful. I wouldn't host the production database on my laptop, of course! :)) I was just trying to simulate a stress test and see how I can improve on this. –  Andrei Rinea Feb 26 '12 at 17:51
1  
Partitioning seems like overkill here, and it's a lot more complicated to design and maintain that just "put a partition on it'll be faster" –  JNK Apr 10 '12 at 18:19
add comment

My advice is to get off a laptop. Put this on a real machine, with fast drives and lots of ram.

share|improve this answer
    
I am sure that would help but I was testing on the laptop just to see where things will bottleneck and what would be the first and easiest steps I can take to improve things, before considering scaling up. –  Andrei Rinea Feb 27 '12 at 8:35
    
The problem with your approach is that laptops and servers are fundamentally different from an internal architecture point of view. –  datagod Feb 27 '12 at 14:24
    
... and I cannot determine ways to speed up something that would run on a server if I test on a laptop? –  Andrei Rinea Feb 27 '12 at 14:34
1  
You can, but you are going to possibly waste time trying to overcome bottlenecks introduced by the laptop itself. It is like an olympic runner asking people to comment on his form, asking for ways to improve his time on the track, all the while he is wearing scuba gear. –  datagod Feb 27 '12 at 19:04
3  
Your bottlenecks on the laptop are probably very different (and variable!) compared to a proper server. If you're going to stress test at least run it on a device set up as a server with nothing else running. You're likely to hit RAM/CPU bottlenecks on a laptop that might not exist on a production machine –  Ben Brocka Feb 27 '12 at 19:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.