SQL Server - Optimize as an in memory database

Currently working on a migration project for Java+Oracle to Java+SQL Server as you may expect there are several documented approaches for doing this migration however the challenge in this case is that the client is using Berkeley DB (in memory DB licensed by Oracle) in their distributed architecture and Microsoft currently doesn’t have a corresponding in memory DB product. So how can we build a database centric migration strategy (i.e. no change to client code)?
Initial brainstorming helped us identify 2 possible approaches to replacing the Berkeley DB: a. Use SQLCE for data caching b. Use SQL Express for data caching (also applies to SQL Standard and Enterprise skew) In both the above choices the key to arriving at the target solution is buffer pool management, SQL Server internally tries to reduce the disk I/O by storing data pages into the buffer pool.
SQL CE seemed a good choice initially because its an inproc DB an this inherent nature could improve query performance however we concluded that it was not the right fit since:•

• SQL CE doesn’t have a native JDBC compliant driver, worst case we will have to use some .NET interop like JNBridge/ Web Services etc.
• SQL CE doesn’t expose performance counters it will be very difficult to evaluate performance and SQL CE behavior under load, we can only measure response time and throughput but would not be able to observe internal resource utilization.
• SQL CE doesn’t support very high level of concurrency (ideally meant for desktop and device usage)
• SQL CE does not offers good buffer pool management

So with SQL CE out of the way we could just focus on leveraging SQL Express and optimizing the buffer pool management. So how does the buffer pool work?
When SQL Server starts it calculates the virtual address space for the buffer based on several parameters the most important being the physical memory on the system. SQL Server only identifies the max amount it should reserve for the buffer pool it doesn’t reserve the memory on startup it only commits memory on a need basis. The DMV sys.dm_os_sys_info can be used to view the amount of buffer memory, bpool_commit_target show "target" memory limit for the buffer pool and bpool_committed displays the amount of memory currently committed to the buffer pool( both number represent the number of 8 KB pages). This is also impacted by the configuration in the min server memory and max server memory. Note that when SQL Server starts it tries to commit large number of pages to buffer pool memory this is called ramp up time, SQL 2005 Enterprise edition tries to improve the performance by allocating 8 pages at a time (i.e. 64KB). Buffer manager performs disk I/O asynchronously. Optimizing the table data types and length to fit optimally into pages, identifying the indexing strategy + index usage and optimizing the I/O subsystem would all be important in optimizing the buffer pool management.
Scenarios that are not discussed in this blog which impact the buffer pool include

• AWE (for 32 bit environment) - most organizations are going for 64bit servers so AWE itself would fade away
• NUMA
  

There is news in the air that Microsoft is planning to release an in-memory DB however we are under NDA not to disclose further information, will blog about it separately when the product plans are more concrete and when the NDA is lifted.