RAM and I/O’s and SQL Behaviour | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
RAM and I/O’s and SQL BehaviourHi All,
A question, will more RAM memory reduce I/O activity on the harddisk? probably relative to the amount of RAM memory. But for instance if I have a 50GB Database and 64GB of RAM, then SQL would normally load everything into RAM and read I/O’s would not occure on the HDD since it can be read from Memory? Yes?
Or will SQL behave differently?
You can’t ping the db into memory but when you run any query/proc it reads from disk and caches into memory and then serves to the client…
When you have more memory, it will not be remove from cache and keeps serving from the cache which is memory….
SQL Server always happy with memory…
When you have more memory then Page Life Expectancy increases…..check the perfmon counter Page Life Expectancy…
Your general assumption of loading all the database to memory is not right, it is only the actual execution of queries would take the amount of data that needs to be return in cache, further you could use disk caching whereby advantage of quicker execution than not having issues on memory.
A database system is first and foremost responsible for the accurate storage and retrieval of data, even in the event of unexpected system failures. The system must guarantee the atomicity and durability of transactions, while accounting for current execution, multiple transactions, and various failure points. This is often referred to as the ACID (Atomicity, Consistency, Isolation, and Durability) properties.
One fo the KBA refers:
Microsoft SQL Server 2005, SQL Server 2000, SQL Server 7.0, earlier versions of SQL Server, and many mainstream database products on the market today use the Write-Ahead Logging (WAL) protocol.
Write-Ahead Logging (WAL) Protocol
The term protocol is an excellent way to describe WAL. It is a specific and defined set of implementation steps necessary to ensure that data is stored and exchanged properly and can be recovered to a known state in the event of a failure. Just as a network contains a defined protocol to exchange data in a consistent and protected manner, so too does the WAL describe the protocol to protect data. Enhanced caching controller systems disable on-disk cache and provide a functional battery-backed caching solution. These caches can maintain the data in the cache for several days and even allow the caching card to be placed in a second computer. When power is properly restored, the unwritten data is completely flushed before any further data access is allowed. Many of them allow percentage of read versus write cache to be established for optimal performance. Some contain large memory storage areas. In fact, for a very specific segment of the market, some hardware vendors provide high-end battery-backed disk caching systems with multiple gigabytes of cache. These can significantly improve database performance. I/O transfers that are performed without the use of a cache can be significantly longer because of hard drive spin rates, the mechanical time that is needed to move the drive heads, and other limiting factors. SQL Server installations are targeted at systems that provide caching controllers. These controllers disable the on-disk caches and provide stable media caches to satisfy SQL Server I/O requirements. They avoid performance issues related to disk seek and write times by using the various optimizations of the caching controller.