This question occassionally crops up from partners and customers.
The amount of memory needed is often directly linked to the amount of data needed in the cache to achieve high cache hit ratios.
Its not possible to have too much memory for SQL Server, but there must be some left over for the Operating System. So as a general rule of thumb is to have 90% of memory for SQL and leave 10% for the OS to handle general processing.
(The above suggestion assumes that SQL is running on a dedicated server with no other applications such as IIS or another SQL instance is running.)
Useful SQL Performance Counters (Source)
- SQL Server: Buffer Manager: Free List Stalls/sec
- SQL Server: Buffer manager: Lazy Writes/Sec
- SQL Server Buffer Manager: Checkpoint Pages/sec
- SQL Server Buffer Manager: Page Life Expectancy
- SQL Server Buffer Manager: Page Lookups/Sec
- SQL Server Buffer Manager: Page Reads/sec
- SQL Server Buffer Manager: Page Writes/ Sec
- SQL Server Buffer Manager: Readahead/sec
- Memory: Free System Page Table Entries
Questionable SQL Perforamnce Counters
SQL Server: Buffer Manager: Buffer Cache Hit Ratio