snippetcsharpModerate
How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first?
Viewed 0 times
canguaranteecached2008insertssqlarefirstthathow
Problem
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.
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.
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.
Solution
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
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
Context
StackExchange Database Administrators Q#12185, answer score: 11
Revisions (0)
No revisions yet.