Cache find

Cache fetches/sec.

Low numbers can indicate an ineffective database cache.

Each time something is found in the database cache, this counter is incremented. This counter applies only to disk-based tables, not to in-memory tables. If you use disk-based tables, you expect to see large numbers in this counter. Compare this number to the Cache read counter to determine the effectiveness of the database cache.

Cache read

Number of blocks (of database block size) per second that must be read from disk because of a database cache miss.

High numbers can indicate excessive disk I/O.

The counter applies only to disk-based tables. You want this number to be as low as possible after the database cache is warmed up, meaning that the cache is full, or as full as it can be from normal database operation. Any time a cache miss occurs and data must be read from disk, performance is affected. If the total size of the database is larger than the amount of memory that can be assigned to database cache, numbers in this counter are likely to be higher depending on the workload and data access patterns.

Cache write

Cache page flushes per second

Cache prefetch

Number of database blocks being prefetched into the database cache per second.

High numbers can indicate table scans are being done.

This counter applies only to disk-based tables. High values in this counter indicate that the internal prefetching algorithm is detecting that large sequential reads are being performed. At the SQL level, this number can mean that table scans are being performed, which can affect performance and can be a result of a bad query plan or a missing index.

Cache prefetch wait

Number of waits that occurred per second because of prefetching while attempting a read.

High numbers can indicate insufficient prefetching.

When a read is attempted, if prefetching is in the process of bringing the data into the database cache, the read must wait for the prefetch to complete. High numbers can indicate that prefetching is not performing appropriately for the type of reading that is being done. Consider increasing the IndexFile.ReadAhead configuration parameter.

Cache preflush

Number of preflush operations done by the preflush thread per second.

Excessive values can indicate insufficient database cache.

A preflush is the asynchronous writing of dirty page data from the database cache to disk. This counter applies only to disk-based tables. Values in this counter indicate normal database operation when writes are occurring (meaning data is changing). If the number seems excessive, it might be an indication that the database cache is configured too small. It could also be an indication that the database cache is being used for other database operations and cache, which might or might not be expected.

Cache read bytes

File bytes read/sec through cache manager

Cache write bytes

File bytes written/sec through cache manager

Cache LRU write

A write from cache is done when performing an LRU replacement. This indicates that the client thread must write one block to disk before reading a new block from the disk because there has not been a free disk block available.

A high value can indicate high I/O load, insufficient database cache, or that I/O preflusher values are not optimal.

If poor preflush performance is suspected, considering modifying the IndexFile.PreFlushPercent configuration parameter.

Cache slot wait

This counter indicates that there is concurrent access to the same block and one thread must wait for the other. Depending on the cache configuration, it can also indicate that the mutex count for the cache is not optimal and there are false conflicts. The default mutex count does not cause false conflicts here.

Cache slot replace

Database cache slot is replaced and old slot is removed.

Cache write storage leaf

Database cache has written a storage tree leaf page to disk.

Cache write storage index

Database cache has written a storage tree index page to disk.

Cache write bonsai leaf

Number of times that a Bonsai tree leaf has been written to disk from the database cache. This applies only to disk-based tables.

High numbers can indicate that the cache is too small or that applications are not closing transactions properly.

For best performance, the Bonsai tree should remain in the cache. If cache does not have enough space, parts of the Bonsai tree are written to disk, which has a negative affect on performance. In this situation, either the cache is too small or the Bonsai tree is too large. A large Bonsai tree can be caused by applications not properly closing transactions.

Cache write bonsai index

Number of times that a Bonsai-tree index page has been written to disk from the database cache.

High numbers could indicate that unnecessary indexes exist.

Similar to Cache write bonsai leaf except that this counter indicates that a high volume of writes to a table, or tables, that have many associated indexes is occurring. If you see high numbers for this counter, examine your index design to determine whether any unnecessary indexes exist.

Cache preflush bytes

Number of bytes written by preflusher before log file is flushed. The counter is reset at each flush.

Cache preflush flush

Number of preflush calls/sec before log file is flushed.

Cache seg remains default contra request

Number of cache pages (per second) that are allocated to default (unassigned) segment against request

Cache seg remains special contra request

Number of cache pages (per second) that are allocated to assigned segments against request

Cache seg set default, victim from other

Number of cache pages (per second) that came from assigned segment

Cache seg set default, victim from self

Number of cache pages (per second) that came from the same segment

Cache seg set special, victim from other

Number of cache pages (per second) that came from other assigned or unassigned segment

Cache seg set special, victim from self

Number of cache pages (per second) that came from the same assigned segment

Cache seg remains default per request

Number of cache pages (per second) that are allocated to default (unassigned) segment as requested

Cache seg remains special per request

Number of cache pages (per second) that are allocated to default assigned segments as requested

Cache seg set default, original dropped

Number of cache pages in a dropped segment