Does checkpoint operations flush everything in the log buffer to log file? Or just the log records relating to the dirty pages that are about to be flushed? I found some inconsistent description about this by the top names in the SQL Server industry. From Kalen Delaney's "Microsoft SQL Server 2012 Internals": Checkpoint operations also write log records from transactions in progress to disk because the cached log records are also considered to be dirty. "from transactions in progress", yes that's reasonable, since the log records for committed transactions were already written to disk. So it basically means all unflushed log records will be flushed. From Itzik Ben-Gan's "Understanding log buffer flushes" at https://sqlperformance.com/2018/11/sql-performance/understanding-log-buffer-flushes: SQL Server needs to harden dirty data pages, e.g., during a checkpoint process, and the log records representing the changes to those pages were not yet hardened (write ahead logging, or WAL in short) Are the the log records in log buffer that corresponding to the dirty pages all unflushed log records in log buffer? I'm not sure. Is Itzik's description basically means the same thing as Kalen's description?

Using SQL server 2017 (14.0.3381.3) with Always On High Availability environment. Getting error:"Buffer Pool scan took 10 seconds: database ID 8, command 'CHECKPOINT', operation 'FlushCache', scanned buffers 144321190, total iterated buffers 150079215, wait time 42 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information" This is a new feature that is included in the 2017 CU23, under the VSTS bug number 13741858. I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan? - OR - Is there a Trace Flag that can be used to turn off the log of this error?

I recently updated my mariadb database to 10.5.5 and I can't set innodb_buffer_pool_instances ``` innodb_log_file_size = 3G innodb_buffer_pool_size = 12G innodb_buffer_pool_instances = 12 ``` But when ``` MariaDB [(none)]> SHOW VARIABLES LIKE "%innodb_buffer_pool_%"; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 12884901888 | +-------------------------------------+----------------+ ``` Do I need other vars to configure pool instances? Which? Thank you very much for any ideas.

As part of a migration, I created a new bufferpool - say BP8K - size 8K, and a tablespace using that bp, and a table in that tablespace, that is loaded with data. However, I get a: ``` SQL1218N There are no pages currently available in bufferpool "4097" ``` during that process. Looking at `db2top` it appears as if the default bufferpool IBMSYSTEMBP8K is used instead of BP8K which explains the phenomena. I've seen similar when there is not sufficient shared memory to start the bufferpool, but in this case BP8K shows up in both `db2top`, and `db2pd -d ... -bufferpools`. It is also possible to change the size of the bp which in the case of insufficient memory, usually results in a warning that the bufferpool is not started. A `db2stop; db2start` fixes the problem, but should that really be necessary? The migration is a sql-script that runs from an upgrade framework, so I would rather not add some hook that forces all applications. Thoughts? `db2level DB21085I This instance or install (instance name, where applicable: "db2inst1") uses "64" bits and DB2 code release "SQL11050" with level identifier "0601010F". Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64", and Fix Pack "0". Product is installed at "/opt/ibm/db2/V11.5". db2licm -l Product name: "DB2 Enterprise Server Edition" License type: "Restricted" Expiry date: "Permanent" Product identifier: "db2ese" Version information: "11.5" Max amount of memory (GB): "128" Max number of cores: "16" cat /proc/meminfo MemTotal: 164759044 kB MemFree: 4267032 kB MemAvailable: 131089520 kB ` Mark Barinstein helped me determine that the bufferpool is not started after all: `ADM6073W The table space "TBSPC8K" (ID "9") is configured to use buffer pool ID "3", but this buffer pool is not active at this time. In the interim the table space will use b

I am trying to understand read-ahead reads, but it seems a bit complicated to me. I searched on the web and got the following: From Reading Pages (Microsoft documentation): Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. From an answer to Why is 'physical reads' less than 'read-ahead reads' & 'logical reads' in SQL Server for first time execution of query? by huntharo on Stack Overflow: Physical Read - The query is blocked waiting for the page to be read from disk into the cache for immediate use. Read-Ahead Read - The page is being read before it blocks the query and is read into the cache as are all reads. Read-Aheads are possible when you are scanning an index, in which case the next leaf pages in the index can assume to be needed and the read can be initiated for them before the query actually says it needs them. This allows the disk to be busy while the db engine is examining the contents of previously fetched pages. Maybe someone could clarify the above using their own explanation because I can't find a detailed explanation for read-ahead reads. To set an example, look at `statistics io` info: ``` Table 'TestLarge'. Scan count 1, logical reads 159185, physical reads 348, read-ahead reads 159209 ```

Are (disk-based, so no Hekaton) tables persisted in memory for longer than the lifespan of the query that is reading data from them? If so, what determines how long they stay in memory for? Is there a way to manage how long they persist in memory for?

We have been monitoring some `SQLServer: Memory Manager`'s metrics, and noticed that after `DBCC CheckDB` job, metric ``` Database Cache Memory (KB) ``` drops down significantly. If to be exact, it dropped from 140 GB cached DB memory to 60 GB. And after that, slowly ramp up again during the week. (Amount of "`Free Memory KB`", went from 20 to 100 GB right after `CheckDB`) `DBCC CheckDB` is run every Sunday, so Database Cache Memory has to ramp up back again each week ``` What is the behavior of this ? Why CheckDB pushes database pages out of memory ? ``` Second question is why "`buffer cache hit ratio`" did not change after `DBCC CheckDB` completes ? It was 99.99% on average and after `DBCC CheckDB` job it drops to ~98.00%, and returns back to 99% pretty fast while I expected "`buffer cache hit ratio`" to drop significantly because database data has to be read from storage to RAM again ?

I am running MySQL 5.5.52 I am trying to see how much of my innodb buffer pool is used. If I run 'show engine innodb status' I see this: ``` ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 10731520; in additional pool allocated 0 Dictionary memory allocated 1823022 Buffer pool size 639 Free buffers 0 Database pages 638 Old database pages 215 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 16622990, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 19765731, created 109264, written 174555 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 638, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ``` Is there anything in there that tells me how much is used? If I run MySQL workbench it shows InnoDB Buffer Usage: 79.2% but I want a way to get this programmatically with SQL not just from a GUI. How can I do that?

How do i check memory usage by my SQL server in production box. I am using SQL Server 2016.When ever i check task manager,it shows above 90%. I don't think that is the real memory usage by sql server. I have a SQL performance tool grafana which shows CPU usage very less than what i see in task manager. I checked Resource Monitor,there is can see Average CPU value.I am confused as to which is the SQL server memory usage. I am trying to determine if memory pressure is an issue to some of my problem. Can someone direct to a good/proper explanation.

Is there a way to preserve the buffer pool cache so that is can be loaded in after a restart? My research suggests that other DB servers can do this but SQL Server is not one of them. I'm looking for ways to maximize server performance after a maintenance restart.