Skip to main content
added 490 characters in body
Source Link
goodfella
  • 395
  • 1
  • 13

SQL Server loves memory! Unlike other database systems like PostgreSQL which depends on system cache SQL server do not depends on system cache. If you have a system with 1TB of memory and if you leave SQL server with an unlimited setting sooner or later it going to take all that memory and once it took there is no going back unless you restart the service.

Hence wise thing to do is limit the memory usage of SQL server to 75-80 % of your total system memory if it's a dedicated SQL Server VM.By doing this we save whole system from memory pressure and plenty of alerts piling up.

But how do we know we have sufficient memory for smooth database operations?

Best thing to check is cache hit ratio. An optimal system expected to have >95% cache hit rate. Anything <95% I would say it's suboptimal and you having problem with high physical disk read which is the most undesired in database world. If it <95% consider adding indexes and tuning I/O intensive queries or last resort upgrade!

UPDATE

You seeing free pages is because previously it was in use and it got evicted based on how frequently and how recently they are used- cost based eviction. Now it's no longer active either because whatever already in buffer satisfying all your query workloads- in other words 100% buffer cache hit rate or there is no activity at all. These free pages may or may not be used in future based on query pattern. But OS will never able to use it unless you release in some way.

SQL Server loves memory! Unlike other database systems like PostgreSQL which depends on system cache SQL server do not depends on system cache. If you have a system with 1TB of memory and if you leave SQL server with an unlimited setting sooner or later it going to take all that memory and once it took there is no going back unless you restart the service.

Hence wise thing to do is limit the memory usage of SQL server to 75-80 % of your total system memory if it's a dedicated SQL Server VM.By doing this we save whole system from memory pressure and plenty of alerts piling up.

But how do we know we have sufficient memory for smooth database operations?

Best thing to check is cache hit ratio. An optimal system expected to have >95% cache hit rate. Anything <95% I would say it's suboptimal and you having problem with high physical disk read which is the most undesired in database world. If it <95% consider adding indexes and tuning I/O intensive queries or last resort upgrade!

SQL Server loves memory! Unlike other database systems like PostgreSQL which depends on system cache SQL server do not depends on system cache. If you have a system with 1TB of memory and if you leave SQL server with an unlimited setting sooner or later it going to take all that memory and once it took there is no going back unless you restart the service.

Hence wise thing to do is limit the memory usage of SQL server to 75-80 % of your total system memory if it's a dedicated SQL Server VM.By doing this we save whole system from memory pressure and plenty of alerts piling up.

But how do we know we have sufficient memory for smooth database operations?

Best thing to check is cache hit ratio. An optimal system expected to have >95% cache hit rate. Anything <95% I would say it's suboptimal and you having problem with high physical disk read which is the most undesired in database world. If it <95% consider adding indexes and tuning I/O intensive queries or last resort upgrade!

UPDATE

You seeing free pages is because previously it was in use and it got evicted based on how frequently and how recently they are used- cost based eviction. Now it's no longer active either because whatever already in buffer satisfying all your query workloads- in other words 100% buffer cache hit rate or there is no activity at all. These free pages may or may not be used in future based on query pattern. But OS will never able to use it unless you release in some way.

edited body
Source Link
goodfella
  • 395
  • 1
  • 13

SQL Server loves memory! Unlike other database systems like PostgreSQL which depends on system cache SQL server do not depends on system cache. If you have a system with 1TB of memory and if you leave SQL server with an unlimited setting sooner or later it going to take all that memory and once it took there is no going back unless you restart the service.

Hence wise thing to do is limit the memory usage of SQL server to 75-80 % of your total system memory if it's a dedicated SQL Server VM.By doing this we save whole system from memory pressure and plenty of alerts piling up.

But how do we know we have sufficient memory for smooth database operations?

Best thing to check is cache hit ratio. An optimal system expected to have >99%>95% cache hit rate. Anything <97%<95% I would say it's suboptimal and you having problem with high physical disk read which is the most undesired in database world. If it <97%<95% consider adding indexes and tuning I/O intensive queries or last resort upgrade!

SQL Server loves memory! Unlike other database systems like PostgreSQL which depends on system cache SQL server do not depends on system cache. If you have a system with 1TB of memory and if you leave SQL server with an unlimited setting sooner or later it going to take all that memory and once it took there is no going back unless you restart the service.

Hence wise thing to do is limit the memory usage of SQL server to 75-80 % of your total system memory if it's a dedicated SQL Server VM.By doing this we save whole system from memory pressure and plenty of alerts piling up.

But how do we know we have sufficient memory for smooth database operations?

Best thing to check is cache hit ratio. An optimal system expected to have >99% cache hit rate. Anything <97% I would say it's suboptimal and you having problem with high physical disk read which is the most undesired in database world. If it <97% consider adding indexes and tuning I/O intensive queries or last resort upgrade!

SQL Server loves memory! Unlike other database systems like PostgreSQL which depends on system cache SQL server do not depends on system cache. If you have a system with 1TB of memory and if you leave SQL server with an unlimited setting sooner or later it going to take all that memory and once it took there is no going back unless you restart the service.

Hence wise thing to do is limit the memory usage of SQL server to 75-80 % of your total system memory if it's a dedicated SQL Server VM.By doing this we save whole system from memory pressure and plenty of alerts piling up.

But how do we know we have sufficient memory for smooth database operations?

Best thing to check is cache hit ratio. An optimal system expected to have >95% cache hit rate. Anything <95% I would say it's suboptimal and you having problem with high physical disk read which is the most undesired in database world. If it <95% consider adding indexes and tuning I/O intensive queries or last resort upgrade!

Source Link
goodfella
  • 395
  • 1
  • 13

SQL Server loves memory! Unlike other database systems like PostgreSQL which depends on system cache SQL server do not depends on system cache. If you have a system with 1TB of memory and if you leave SQL server with an unlimited setting sooner or later it going to take all that memory and once it took there is no going back unless you restart the service.

Hence wise thing to do is limit the memory usage of SQL server to 75-80 % of your total system memory if it's a dedicated SQL Server VM.By doing this we save whole system from memory pressure and plenty of alerts piling up.

But how do we know we have sufficient memory for smooth database operations?

Best thing to check is cache hit ratio. An optimal system expected to have >99% cache hit rate. Anything <97% I would say it's suboptimal and you having problem with high physical disk read which is the most undesired in database world. If it <97% consider adding indexes and tuning I/O intensive queries or last resort upgrade!