Goals for configuring caches are:
Reduced contention for spinlocks on multiple engine servers.
Improved cache hit ratios and/or reduced disk I/O. As a bonus, improving cache hit ratios for queries can reduce lock contention, since queries that do not need to perform physical I/O usually hold locks for shorter periods of time.
Fewer physical reads, due to the effective use of large I/O.
Fewer physical writes, because recently modified pages are not being flushed from cache by other processes.
Reduced cache overhead and reduced CPU bus latency on SMP systems, when relaxed LRU policy is appropriately used.
Reduced cache spinlock contention on SMP systems, when cache partitions are used.
In addition to commands such as showplan and statistics io that help tune on a per-query basis, you need to use a performance monitoring tool such as sp_sysmon to look at the complex picture of how multiple queries and multiple applications share cache space when they are run simultaneously.
