We had a major issue on one of the SQL Server instance with the memory and getting following error message intermittenlty:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
Eventually I was able to rule-out each one of the symptoms that are mentioned in this SQLProtocols blog entry, as it suggests that before further troubleshooting, you need to know what is the current protocol setting on the server box, and make sure server is running and sqlbrowser service is running, two ways to check. Handful information to checkout in case you tend to see the above error within your SQL Server error log.
Further I have gone through another blog PSSpost by Bob Dorr in troubleshooting the relevant steps for memory issues when SQL Server 2005 instance is used. Few of the Technet articles suggest to use the 64-bit version of Windows Server 2003 R2 with SP2 or use the 64-bit version of SQL Server 2005 with SP2, but I don't see any such requirement or no use of spending too much budget in this case.
Within my troubleshooting saga I was able to use DMV queries (again!) to see how the memory usage is handled in the SQL Server. SYS.DM_OS_MEMORY_CLERKS will be helpful here.
To see top 10 memory consuming pages execute:
SELECT TOP 10 type, sum(single_pages_kb) AS [Pages_KB]
FROM sys.dm_os_memory_clerks
GROUP BY type ORDER BY SUM(single_pages_kb) DESC
Further to this you need to determin what objects are consuming large amount of space within a give period of time from the SQL Server BufferPool.
EdgeWood solutions has provided such an useful script to get values from DMV: SYS.DM_OS_BUFFER_DESCRIPTORS.
Also getting cached pages count will help to see what indexes & objects are used extensively:
SELECT count(*) AS cached_pages_count, obj.name,
ind.name, obj.index_id FROM sys.dm_os_buffer_descriptors
Further to the above refer to NUMA_Scenario by Bob Dorr again.