Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute.

enter image description here

Your expert advice: How can I write the C# insert code for SQL Server, such that there is a guarantee that SQL caches everything immediately in its own RAM, without blocking my app for more than it takes to feed data into said RAM? To achieve this, do you know of any patterns for setup of the SQL server itself, or patterns to set up the individual SQL tables I'm writing to?

Of course, I could do my own version, which involves constructing my own queue in RAM - but I don't want to reinvent the Paleolithic Stone Axe, so to speak.

  • 1
    Are you talking about C# client code? So you're interested in SQL code that ensures writes are cached?
    – Richard
    Commented May 27, 2011 at 8:28
  • 6
    I be inclined to queue inserts myself EVEN if the RDBMS supports it because (a) it's not hard, (b) it's totally under your control, and (c) it ain't vendor dependent.
    – corlettk
    Commented May 27, 2011 at 8:33
  • I'm interestedi in the C# client code that contains the SQL code to ensure writes are cached. However, I"m sure that I could work with the straight T-SQL and write my own C# wrapper.
    – Gravitas
    Commented May 27, 2011 at 13:12

3 Answers 3


Have you tried just writing and see what happens? Do you have a known bottleneck?

If you need to prevent your app being blocked then you one way would be to queue the writes to defer the database call. However, I'd expect the queue to clear in a second or 2: so do you need an queue if this is OK?

Or you can spool to a staging table and then flush later? We use this technique to deal with sustained writes of millions of new rows per minute (we actually use a staging DB with Simple recovery): but we didn't implement it until we had experience of just writing rows.

Note: Every write in SQL Server will go do disk as part of the Write Ahead Logging (WAL) protocol. This applies to the t-log entry for that write.

The data page with the row will go to disk at some point (based on time, use, memory pressure etc) but generally your data will be in memory anyway. This is called "Checkpointing" and doesn't evict data from memory, just flushes changes (edited 24 Nov 2011)


For throughout considerations, based on ther last paragraph above, shift your LDF for this database to a dedicated set of disks for more performance. Ditto a staging database (one each for MDF/LDF). It's quite common to have a dozen or 3 different volumes (via a SAN normally) for your database server

  • 1
    Spooling to a staging table is probably the best way to go. I've also had confirmation from one of my friends, who works in an environment with billion row tables, he said he uses temp tables for faster analysis.
    – Gravitas
    Commented May 30, 2011 at 21:37

Unless I'm missing something, this would violate the Durability requirement from ACID (http://en.wikipedia.org/wiki/ACID). That is, if your application "writes" the data to RAM and then the server crashes, your data is lost.

So, what you seek is either a non-database system that serves as a queue for eventual storage into a database or a database system that is sufficiently fast for what you're doing. I'd suggest trying the latter first and see if it's sufficient; don't borrow trouble.


I used once a Dataset for this. I was inserting rows to dataset as they arrived, and there was another thread that was flushing the rows every 2 seconds or so to the database. You can also use xml document to do the cachin, and then pass the xml to the database in one call, this may be even better.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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