As previously discussed, SQL Server memory is divided between SQL Server overhead, the procedure cache, and the data cache. The primary goal is to cover SQL Server overhead while effectively distributing the remaining memory between the procedure and data cache by using the procedure cache configuration parameter. The distribution of the remaining memory between these caches is an exercise in making sure the most-used objects are cached in their respective caches. Therefore, the most-used stored procedures should be in the procedure cache, while the majority of frequently used indexes and tables should be in the data cache.
The best way to determine how memory is being used by SQL Server is to execute DBCC MEMUSAGE. This statement indicates the amount of memory allocated to SQL Server at startup, the 12 largest objects in the procedure cache, and the 20 largest objects in the data cache. Therefore, the following recommendations are based upon the use of this data and will aid in determining the optimal size for these caches.