17

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.

Contango
  • 411
  • 3
  • 7

3 Answers3

11

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)

Edit:

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

gbn
  • 70,237
  • 8
  • 167
  • 244
7

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.

Ben Thul
  • 1,957
  • 2
  • 15
  • 20
2

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.

Regards

Piotr

Piotr Rodak
  • 121
  • 3