Skip to main content
23 votes

Memory Usage by SQL Server

'I am trying to determine if memory pressure is an issue to some of my problem.' very useful script: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql ...
Igor's user avatar
  • 730
17 votes
Accepted

Memory Usage by SQL Server

Can someone direct to a good/proper explanation. I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server ...
Shanky's user avatar
  • 19k
17 votes
Accepted

Difference between Physical Reads and Read-Ahead Reads

A query always reads data from memory (a logical read). Your example query scanning the TestLarge table touched 159,185 8KB memory pages during its execution. During execution, SQL Server does two ...
Paul White's user avatar
  • 86.4k
13 votes
Accepted

How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

Pages are read into memory as required, if there is no free memory available, the oldest unmodified page is replaced with the incoming page. This means if you execute a query that requires more data ...
Hannah Vernon's user avatar
  • 70.2k
11 votes
Accepted

How Important is the buffer pool really?

The test query that you ran to measure the difference a cold and warm cache is going to spend most of its execution time waiting on ASYNC_NETWORK_IO. Sending 14 million rows to SSMS likely takes about ...
Joe Obbish's user avatar
  • 32.5k
10 votes
Accepted

At what point is a (disk-based) table removed from memory after data is read from it?

This is a complicated topic, much of which is covered in this Microsoft Docs article. To answer a couple of points: Are (disk-based, so no Hekaton) tables persisted in memory for longer than the ...
Josh Darnell's user avatar
  • 29.4k
9 votes
Accepted

Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB

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 ...
Randi Vertongen's user avatar
8 votes
Accepted

Dump the buffer pool to disk so it can be loaded after a restart

No, there is no way to restore the buffer pool. Some will suggest pre-filling it by selecting all rows from all tables, but that is just silly given how rare it is for systems to use all rows from all ...
Solomon Rutzky's user avatar
6 votes

Making sense of INNODB buffer pool stats

I'll disagree with the assessment that "you are lucky enough to have a buffer pool with a perfect 100% hit rate" At the top of the output (which is chopped off), is a line something like: Per second ...
Rick James's user avatar
  • 78.6k
6 votes

What are the different ways to Warmup InnoDB Buffer Pool in MySQL 5.6?

Assuming, you have a 7200 rpm HDD, if you perform random reads and expect the data to be loaded whenever it is required by the application, it will slow down the whole process. Random reads are way ...
MontyPython's user avatar
5 votes

How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

I can't speak to what exactly your query would do in this scenario but SQL Server has several options depending on how much is needed. Data can "spill" to TempDB, this would be using your disk Old ...
Arthur D's user avatar
  • 932
5 votes

Page life expectancy very low on certain time?

It's almost certainly going to be full backups or indexing. The queries won't show up as a bunch of memory allocated but rather as intensive disk IO. If you are tracking those counters then have a ...
Cody Konior's user avatar
  • 3,508
5 votes
Accepted

getting innodb buffer usage

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free' and SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total' will enable you to derive this percentage. Keep in mind a page_free of 0 isn't ...
danblack's user avatar
  • 7,809
5 votes
Accepted

SQL Server RAM usage - how to find out where it is going?

If you're running DBCC DROPCLEANBUFFERS before checking the memory usage, it will only show a small amount of memory being used by SQL Server, but the freed memory won't be released back to the OS by ...
HandyD's user avatar
  • 9,982
4 votes

Why are certain objects loaded into Buffer?

First, check the plan cache with queries that hit sys.dm_exec_query_stats. My favorite is the open source sp_BlitzCache (disclaimer: I'm one of the coauthors). Queries could be running outside of your ...
Brent Ozar's user avatar
4 votes
Accepted

New bufferpool not being used

db2stop/db2start is not required to make a newly created bufferpool useable, but in most cases you must deactivate and reactivate the database for the tablespaces to be able to use the new bufferpool. ...
mustaccio's user avatar
  • 26.3k
4 votes
Accepted

Is sys.dm_os_memory_clerks a good source to use when setting max server memory (MB)?

bad news Yeah, dm_os_memory_clerks is a pretty bad single-source to figure out what max server memory should be set to. It just shows you what's currently consuming memory, which can shift and bob and ...
Erik Darling's user avatar
  • 41.9k
3 votes
Accepted

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

After a long experimentation, I've found the problem to be the infamous swap insanity. The problem has been fixed by using the innodb-numa-interleave option.
Marcus's user avatar
  • 378
3 votes

What is the purpose of a buffer and a buffer manager?

A buffer is an 8 KB page in memory, the same size as a data or index page you can consider buffer as a frame which holds data and index pages when they are brought from disk to memory. SQL Server ...
Shanky's user avatar
  • 19k
3 votes
Accepted

If a query reads more pages than the InnoDB buffer can hold, are all the previous pages evicted?

According to MySQL 5.7 Reference Manual - The InnoDB Buffer Pool InnoDB manages the buffer pool as a list, using a variation of the least recently used (LRU) algorithm. When room is needed to add a ...
joanolo's user avatar
  • 13.5k
3 votes
Accepted

In Full recovery mode, when does the buffer/transaction log changes get applied to the database data pages?

According to the SQL Server Transaction Log Architecture and Management Guide, data pages are modified in cache first (logical write). This occurs the same way regardless of the database recovery ...
Dan Guzman's user avatar
  • 28.3k
3 votes
Accepted

How does SQL Server determine how much memory to allocate to Data cache

With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? SQL Server memory( Buffer Pool) is designed to adjust its requirement according to available ...
Shanky's user avatar
  • 19k
3 votes
Accepted

Big Amount of <free_space_in_bytes> in Buffer Pool's Database Pages

Is this a problem ? Maybe, maybe not. If your database(s) only make up 80% of your server's max server memory, it's about what I'd expect to see. If you have more data than RAM, then the free space ...
Erik Darling's user avatar
  • 41.9k
3 votes
Accepted

Restore plan cache and buffer pool after database restore?

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use? No. The caches will have to warm up based on use. You can force plans ...
David Browne - Microsoft's user avatar
3 votes
Accepted

Can we set the fixed size for both plan cache and data cache?

No. You can set Minimum & Maximum server memory to control the range of total memory utilization by the Instance, but there is not fine grained control for the constituent parts that make up "...
AMtwo's user avatar
  • 16.2k
3 votes
Accepted

I can´t change innodb_buffer_pool_instances

innodb_buffer_pool_instances where removed in 10.5 because they offered no benefit due to internal restructuring of the code. There is no replacement setting. The variable, that serves no purpose, is ...
danblack's user avatar
  • 7,809
3 votes

In SQL Server, what's the impact of a checkpoint to log records in log buffer?

We could implement a DBMS such that every thread that needs to write a log record writes it straight to disk immediately and waits for acknowledgement. This would be inefficient since there would be ...
Michael Green's user avatar
3 votes
Accepted

Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html says: During the analysis, the table is locked with a read lock for InnoDB and MyISAM. ANALYZE TABLE removes the table from the table ...
Bill Karwin's user avatar
  • 15.1k
2 votes
Accepted

Mysql innodb_log_buffer_size change

The files are created when you first install MySQL. It is clumsy to change the size after that. In particular, the size cannot be changed dynamically. How big is the setting now? How many rows ...
Rick James's user avatar
  • 78.6k
2 votes

How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

My question is how does SQL Server handle a query that needs to pull more volume of data into the buffer cache then there is space available To answer this specific part let me tell you how this is ...
Shanky's user avatar
  • 19k

Only top scored, non community-wiki answers of a minimum length are eligible