Database Cache Parameters
Applies to: Windows | Windows Server
Database Cache Parameters
This topic contains parameters that are used for the database cache.
JET_paramBatchIOBufferMax
22
This parameter controls the size of an auxiliary part of the database page cache that is used to simulate scatter gather I/O when it is otherwise not available. The size is in database pages.
Windows XP and later: This parameter is obsolete and does not affect the operation of the database engine.
| Label | Value |
|---|---|
Default Value: |
256 |
Type: |
Integer |
Valid Range: |
0, 2 – 2147483647 |
Scope: |
Global |
Set After JetCreateInstance: |
No |
Set after JetInit: |
No |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
JET_paramCacheSize
41
This parameter can be used to control the size of the database page cache at run time. Ordinarily, the cache will automatically tune its size as a function of database and machine activity levels. If the application sets this parameter to zero, then the cache will tune its own size in this manner. However, if the application sets this parameter to a non-zero value then the cache will adjust itself to that target size (in database pages). The cache will then hold its size at that threshold until given a new size or until it is released to choose its own size.
Note The cache size is still subject to the limits imposed by JET_paramCacheSizeMin and JET_paramCacheSizeMax.
When this parameter is read, the actual size of the cache in database pages is returned. This size can be used by the application as an input to drive its manual adjustment of the cache size.
| Label | Value |
|---|---|
Default Value: |
Special |
Type: |
Integer |
Valid Range: |
Windows 2000: 1 – 1048575 Windows XP: 1 – 4294967295 |
Scope: |
Global |
Set After JetCreateInstance: |
Yes |
Set after JetInit: |
Yes |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
JET_paramCacheSizeMin
60
This parameter configures the minimum size of the database page cache. The size is in database pages.
By default, the database cache will automatically adjust its size between the limits set by JET_paramCacheSizeMin and JET_paramCacheSizeMax.
Windows 2000: On Windows 2000, this parameter should be set to a value roughly equal to four times the number of threads that will be inside the ESE API at the same time. This is required to avoid deadlocks caused by an insufficient number of database page cache buffers to perform complex operations like B+ Tree splits.
Windows XP and later: The cache manager will automatically set its own minimum cache size to avoid deadlocks.
| Label | Value |
|---|---|
Default Value: |
Windows 2000: 64 Windows XP: 1 |
Type: |
Integer |
Valid Range: |
Windows 2000: 1 – 1048575 Windows XP: 1 – 4294967295 |
Scope: |
Global |
Set After JetCreateInstance: |
Windows 2000: No Windows XP: Yes |
Set after JetInit: |
Windows 2000: No Windows XP: Yes |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
JET_paramCacheSizeMax
23
This parameter configures the maximum size of the database page cache. The size is in database pages.
By default, the database cache will automatically adjust its size between the limits set by JET_paramCacheSizeMin and JET_paramCacheSizeMax.
Note If this parameter is left to its default value, then the maximum size of the cache will be set to the size of physical memory when JetInit is called.
Windows Vista: As of Windows Vista, the default value of this parameter was changed to clarify this behavior.
| Label | Value |
|---|---|
Default Value: |
Windows 2000, Windows XP and Windows Server 2003: 512 Windows Vista: 2000000000 |
Type: |
Integer |
Valid Range: |
Windows 2000: 1 – 1048575 Windows XP: 1 – 4294967295 |
Scope: |
Global |
Set After JetCreateInstance: |
Windows 2000: No Windows XP: Yes |
Set after JetInit: |
Windows XP and Windows 2000: No Windows Vista and Windows Server 2003: Yes |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
JET_paramCheckpointDepthMax
24
This parameter controls how aggressively database pages are flushed from the database page cache to minimize the amount of time it will take to recover from a crash. The parameter is a threshold in bytes for about how many transaction log files will need to be replayed after a crash.
If circular logging is enabled using JET_paramCircularLog then this parameter will also control the approximate amount of transaction log files that will be retained on disk.
It is important that this parameter not be set too low. As the value of this parameter approaches zero, the cache will become more and more aggressive when flushing database pages to disk. This not only results in an increased number of writes to the database files but it also indirectly causes an increased number of reads to those files as well. This can cause very significant performance problems in some cases. Unfortunately, setting the smallest optimal value for this parameter can only be done using experimentation with the target application.
| Label | Value |
|---|---|
Default Value: |
20971520 |
Type: |
Integer |
Valid Range: |
Windows 2000, Windows XP and Windows Server 2003: 0 – 2147483647 Windows Vista: All Values |
Scope: |
Windows 2000, Windows XP and Windows Server 2003: This parameter is global. Windows Vista: This parameter is per instance. |
Set After JetCreateInstance: |
Yes |
Set after JetInit: |
Yes |
Affects Physical Layout: |
No |
Affects Reliability: |
Yes |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
JET_paramCheckpointIOMax
135
This parameter controls the maximum number of concurrent writes that the database engine will use to flush modified database pages for the purpose of advancing the checkpoint. The value of this parameter can be used to balance the speed with which the checkpoint can be advanced versus the negative impact this process will have on the response time for other I/O operations to the disks holding the database.
| Label | Value |
|---|---|
Default Value: |
96 |
Type: |
Integer |
Valid Range: |
8 – 1024 |
Scope: |
Global |
Set After JetCreateInstance: |
Yes |
Set after JetInit: |
Yes |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
No |
Availability: |
Windows Vista and later |
JET_paramEnableViewCache
127
When this parameter is True, the database engine will use database data directly from the Windows file cache rather than copying the cached data into its own private memory. Any database data that is modified will still be cached in private memory.
The intent of this mode is to further reduce the amount of private memory used by the database engine to cache database data.
The view cache may only be used if the use of the Windows file cache is enabled by setting JET_paramEnableFileCache to True.
| Label | Value |
|---|---|
Default Value: |
False |
Type: |
Boolean |
Valid Range: |
False, True |
Scope: |
Global |
Set After JetCreateInstance: |
No |
Set after JetInit: |
No |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
Windows Vista and later |
JET_paramLRUKCorrInterval
25
This parameter sets the time interval in microseconds over which two database page accesses are considered to be correlated. This correlation interval controls the sensitivity of the cache's page replacement algorithm (LRU-K) to successive page accesses. This in turn will affect which pages it chooses to keep cached.
| Label | Value |
|---|---|
Default Value: |
128000 |
Type: |
Integer |
Valid Range: |
Windows 2000, Windows XP and Windows Server 2003: 0 – 2147483647 Windows Vista: All Values |
Scope: |
Global |
Set After JetCreateInstance: |
No |
Set after JetInit: |
No |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
No |
Availability: |
All |
JET_paramLRUKHistoryMax
26
This parameter sets the maximum number of non cached database pages for which database page access times will be retained. These history records allow the cache's page replacement algorithm (LRU-K) to more accurately detect popular pages that were wrongly evicted from the database page cache.
Windows XP and Windows Server 2003: This parameter is ignored on Windows XP and Windows Server 2003 and does not affect the operation of the database engine.
| Label | Value |
|---|---|
Default Value: |
Windows 2000: 1024 Windows Vista: 100000 |
Type: |
Integer |
Valid Range: |
Windows 2000: 0 – 4194303 Windows Vista: All Values |
Scope: |
Global |
Set After JetCreateInstance: |
No |
Set after JetInit: |
No |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
JET_paramLRUKPolicy
27
This parameter configures the number of database page accesses that are considered for determining the usefulness of the page. This parameter is essentially the K in LRU-K, the database page cache's page replacement algorithm.
| Label | Value |
|---|---|
Default Value: |
2 |
Type: |
Integer |
Valid Range: |
1-2 |
Scope: |
Global |
Set After JetCreateInstance: |
No |
Set after JetInit: |
No |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
No |
Availability: |
All |
JET_paramLRUKTimeout
28
This parameter indicates the period of time in seconds after which a page in the database page cache is considered to have lost a page access for the purpose of considering the usefulness of the page.
| Label | Value |
|---|---|
Default Value: |
100 |
Type: |
Integer |
Valid Range: |
Windows 2000, Windows XP and Windows Server 2003: 1 – 2147483647 Windows Vista: 1 – 4294967295 |
Scope: |
Global |
Set After JetCreateInstance: |
No |
Set after JetInit: |
No |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
No |
Availability: |
All |
JET_paramLRUKTrxCorrInterval
29
This parameter is obsolete and does not affect the operation of the database engine.
JET_paramStartFlushThreshold
31
This parameter controls when the database page cache begins evicting pages from the cache to make room for pages that are not cached. When the number of page buffers in the cache drops below this threshold then a background process will be started to replenish that pool of available buffers. This threshold is always relative to the maximum cache size as set by JET_paramCacheSizeMax. This threshold must also always be less than the stop threshold as set by JET_paramStopFlushThreshold.
The distance height of the start threshold will determine the response time that the database page cache must have to produce available buffers before the application needs them. A high start threshold will give the background process more time to react. However, a high start threshold implies a higher stop threshold and that will reduce the effective size of the database page cache for modified pages (Windows 2000) or for all pages (Windows XP and later).
| Label | Value |
|---|---|
Default Value: |
Windows 2000, Windows XP and Windows Server 2003: 5 (1%) Windows Vista: 20000000 (1%) |
Type: |
Integer |
Valid Range: |
Windows 2000: 1 – 1048575 Windows XP: 1 – 4294967295 Windows Vista: All Values |
Scope: |
Global |
Set After JetCreateInstance: |
Yes |
Set after JetInit: |
Yes |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
JET_paramStopFlushThreshold
32
This parameter controls when the database page cache ends evicting pages from the cache to make room for pages that are not cached. When the number of page buffers in the cache rises above this threshold then the background process that was started to replenish that pool of available buffers is stopped. This threshold is always relative to the maximum cache size as set by JET_paramCacheSizeMax. This threshold must also always be greater than the start threshold as set by JET_paramStartFlushThreshold.
The distance between the start threshold and the stop threshold affects the efficiency with which database pages are flushed by the background process. A larger gap will make it more likely that writes to neighboring pages may be combined. However, a high stop threshold will reduce the effective size of the database page cache for modified pages (Windows 2000) or for all pages (Windows XP and later).
| Label | Value |
|---|---|
Default Value: |
Windows 2000, Windows XP and Windows Server 2003: 10 (2%) Windows Vista: 40000000 (2%) |
Type: |
Integer |
Valid Range: |
Windows 2000: 1 – 1048575 Windows XP: 1 – 4294967295 Windows Vista: All Values |
Scope: |
Global |
Set After JetCreateInstance: |
Yes |
Set after JetInit: |
Yes |
Affects Physical Layout: |
No |
Affects Reliability: |
No |
Affects Performance: |
Yes |
Affects Resources: |
Yes |
Availability: |
All |
Requirements
| Requirement | Value |
|---|---|
Client |
Requires Windows Vista, Windows XP, or Windows 2000 Professional. |
Server |
Requires Windows Server 2008, Windows Server 2003, or Windows 2000 Server. |
Header |
Declared in Esent.h. |