Product:

Microsoft SQL server

Issue:
SQL server is slow, is it low on memory?

Solution:

Get Brent Ozar Health check.  https://www.brentozar.com/blitz/ 

Other solution:

Check if SQL server need to move pages out of memory with this code:

SELECT *

FROM sys.dm_os_performance_counters

WHERE [counter_name] = 'Page life expectancy'

“Page Life Expectancy drops can be triggered by confusing operations. By default, any one running query can get a memory grant the size of 25% of your buffer pool. Run a few of those queries at the same time, and your buffer pool gets drained – but PLE doesn’t necessarily drop. However, the instant an unrelated query runs and needs to get data that isn’t cached in RAM, your PLE will drop catastrophically. Which queries are at fault? ”

Check how memory is used by each database:

SELECT

    (CASE WHEN ([database_id] = 32767)

        THEN N'Resource Database'

        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],

    COUNT (*) * 8 / 1024 AS [MBUsed],

    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id];

GO

 

 

More Information:

https://www.brentozar.com/archive/2020/06/page-life-expectancy-doesnt-mean-jack-and-you-should-stop-looking-at-it/

https://houseofbrick.com/page-life-expectancy-and-what-its-telling-you/

https://www.spotlightcloud.io/blog/monitoring-page-life-expectancy-in-sql-server

https://training.brentozar.com/p/how-i-use-the-first-responder-kit

SQL SERVER – What is Page Life Expectancy (PLE) Counter

https://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/

https://github.com/SQLadmin/AwesomeSQLServer/blob/master/T-SQL%20Scripts/Memory%20Monitoring.sql