Still looking under the hood of the MS SQL Server Engine, trying to answer the question how is the memory being used:
One of the areas that should be explored in terms of memory utilization is reviewing SQL Server Query Plan recompiles.
Invariably, Brent Ozar, has a good post on this topic:
Microsoft’s SQL Customer Advisory Team’s Top SQL 2005 OLTP Performance Issues says that if Compilations/sec is more than 10% of Batch Requests/sec, you may be experiencing CPU pressure because SQL Server has to build execution plans. This one gets tricky, and frankly, it’s trickier than I want a sysadmin to hassle with. This rule just doesn’t work in too many cases because it ignores the quantity of work being done. If you’ve got a small number of queries coming in, and you’ve armed the server with big multi-core processors, then building execution plans is hardly any work at all even if you’re compiling every single statement from scratch. However, if Compilations/sec is 25% or higher relative to Batch Requests/sec, and if you’ve got in-house developers, it’s time to start asking questions. They’re probably using development tools like LINQ or dynamic SQL that can force SQL Server to build execution plans unnecessarily. We have to work around that by educating the developers, because no amount of memory is going to fix that problem.
Tom Davidson writing for SQL Server Pro “walks the same line”…..
High ratio of compilations to batch requests. When you’re working with online transaction processing (OLTP) applications, you usually want SQL Server to reuse query plans as much as possible to reduce the length of queues. When SQL Server reuses query plans, it doesn’t need to compile the query before execution, thus reducing CPU utilization (which the System:Processor Queue Length counter shows) and shortening query-processing times. To get a ratio of compilations to batch requests, use the Performance Monitor counters SQLServer:SQL Statistics:Batch Requests/sec and SQLServer:SQL Statistics:SQL Compilations/sec.
When you find that SQL Server isn’t reusing query plans efficiently, poor memory utilization (i.e., memory pressure) or poor coding practices might be to blame. To diagnose the cause, you can look at the SQL Server Profiler events SP:CacheMiss, SP:CacheInsert, SP:CacheRemove, SP:Recompilation, and SP:CacheHit. These events report plan activity in the cache. SP:CacheHit events show plan reuse. If a compile occurs, Profiler records the SP:CacheMiss and SP:CacheInsert events. SQL Server sometimes discards query plans to free memory for other activities. In such cases, the SP:CacheRemove event occurs. SP:Recompilation indicates that a recompile has occurred during the execution of a stored procedure. And if you notice a low value in the SQL Buffer Mgr:Page Life Expectancy Performance Monitor counter, your SQL Server system might be suffering from memory pressure.
1) A Sysadmin’s Guide to Microsoft SQL Server Memory
2) Opening Microsoft’s Performance-Tuning Toolbox