So where have you got this wisdom from? It's just plain Bullshit! Just as most cache managers do, Windows cache manager uses some sort of LRU caching scheme. So all data once read from file is kept in memory until either some memory pressure occurs or it is simply pushed out by newer data. Actually, from what I've experienced, I'd recommend to size sqlite's memory to fit for your largest transactions and leave most of the caching to the OS.
And yes, unlike in many *NIX implementations, Windows' FlushFileBuffers() call DOES issue according write commands to the hardware, so setting PRAGMA synchronous ON/OFF makes a big difference in write performance on Windows systems. Howard Chu schrieb am 30.01.2016 um 23:23: > dpb wrote: >> Dear Community, >> >> Assuming that SQLite would arrange the database table contents on page >> boundary and >> >> 1) if db is small let us say maximum 5MB, >> >> 2 further if such a db is part of windows service / application running >> continuously then may be pages will remain in the memory under normal >> conditions. >> >> Q1) Operating System like Windows would cache the series of pages, is this >> behavior not almost same as that of Cache? > The Windows cache manager is quite aggressive at evicting cached pages from > RAM. It used to be tunable back in Win2000, but none of those tuning knobs > survived past WinXP. Generally, if you access some data, leave it for more > than 5-10 seconds, don't expect to be able to reference it again without > incurring a hard page fault. > > Note that the cache eviction runs quite frequently - once every 5 seconds or > so, and evicts pages regardless of whether there's any memory pressure in the > system. It's quite possibly the stupidest cache manager ever written. >
