How to approach finding memory bottleneck for 64-bit O/S?

My mind is mush after reading numerous papers on monitoring memory. Most of what I found was in regards to what DMVs and perfmon/sysmon counters to check, but very little information on what values are considered bad. So I ask you, what do you check when you suspect a memory bottleneck and does this apply to 64-bit OS? I've always concentrated on paging, but I know that is too simplistic.
Thanks, Dave

That's a good blog by Grumpy, but he only mentions checking one counter.
How can you check your memory? Well not with task manager, it seems, available memory may include memory allocated within o/s pools so this figure doesn’t actually mean this memory is not being used. The perfmon counter available Mb should be the counter watched, this should never drop below 200Mb ( the actual phrase used was "a couple of hundred Mb" )
Based upon this counter I don't have a memory bottleneck on our 64-bit cluster. I have 4+GB as my average. However, Pages/sec average over 3,000 and many times rise above 14,000. I'm assuming this isn't good. Page Faults/sec total over 28,000, but that is not sustained. It drops to about 1,200 and then climbs again. I don't know what value is considered bad for Page Faults/sec and perhaps this is not the best counter to check for memory pressure. Pages found in RAM is 7% at the time of my response and the Read Hit Ratio, according to Spotlight on SQL Server, is 0%. There has to be more to check then Available MB.
Thanks, Dave

Hi Dave,
For SQL Server you can use the following Perfmon Counters:
SQL Server Buffer Manager:
Lazywrites/sec
Checkpoint/sec
Free List stalls/sec
Free Pages/sec
Pagelife expectancy
Page Writes/sec
Page Reads/sec
In SQL Server, any active worker thread will regularly sweep through the buffer pool, write to disk and put pages on the Free list (which is a list of pages available for new requests). So if you see high number of Lazywrites, it means that the worker threads could not quite keep up and hence the Lazywriter - which runs on regular interval - will be quite busy (and this goes for the Checkpoint process as well). Together with regularly low Free Pages (or possibly even Free list stalls) and low Page Life Expectancy it indicates that SQL Server would probably benefit from more memory.
The Page Write and Reads/sec is to determine how much of the paging on the server is related to SQL Server.
You should also have a look at:
SQL Server Memory Manager:
Memory Grants Outstanding
Memory Grants Pending
Memory Grants are grants that satisfies requests for queries which execution plan contain operators (parallell execution plans or queries with large sort and hash operations for instance). I would say the Pending number should ideally be zero.
How did you configure your max and min server memory for SQL Server?
/Elisabeth

Memory bottleneck should be understood from 2 aspects
Is Physical Memory on OS not sufficient:
For this Check in Perfmon Available MBytes (Memory). This should always be more than 200 MB and in case of 64 bit system it needs to be bit more to be on safe side.
If the memory falls below this threshold OS trims working set sizes of Processes hosted in it and SQL Server memory utilization would also come down.
In such cases first thing is to check if Max Server Memory of SQL Server is capped to something lower than actual physical memory. Say 16 GB, Cap SQL Server to 14.5 GB leaving 1.5 GB to OS.

Is Memory allocated to SQL Server not sufficient:
To Check if SQL Sever memory bottleneck
Check for counters as suggested by Elisabeth.
Now if you see there are memory issues in SQL Server that may not mean that points to memory bottleneck. May be queries are doing scans instead of seeks and getting more than necessary data from disk to fill up Memory.
Next course of action is to idenfity if it is indeed the case. Check FullScans / Seconds in Access Methods (Perfmon) and compare it with batches / seconds. This would give nature of load or type of queries. It should not more than 10% and lesser the better.
To identify queries we need to run Profiler or SQLDiag and analyze queries that are doing more than necesary IO (Scans vs Seeks).
HTH.
Guru