Looking at performance counters for a SQL Server, I noticed that the Page Life Expectancy (PLE) counter was very low for this server. A quick description of PLE is how long data is stored in the memory of SQL Server before it is replaced by new data.
Current PLE value can be viewed using this query, and the value you get is in seconds. You should grab this value several times during a longer period to get an idea of how you SQL Server performs.
SELECT [counter_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'
Whitepapers from Microsoft suggests that PLE should be above 300 on average, but I guess it really depends on the kind of application you have.
My server is a standard OLAP database server but the PLE is around 1 – 20 seconds throughout peak hours, which i think is really bad. Users also experienced sluggish performance so something has to be done – adding more memory. Given that it didn’t have that much memory to start with, and the size of the data it was a no brainer.