Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 263

ON plns.plan_handle = qstats.plan_handle
CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) as qplan
CROSS APPLY sys.dm_exec_sql_text(qstats.plan_handle) as stext
WHERE qplan.query_plan is not null
Buffer Pool
SQL Server 2005 has a pool of memory known as the buffer pool. All the memory components utilized
by SQL Server are drawn from this pool. SQL Server utilizes something called the Memory Broker, which
manages these components. The Memory Broker works by monitoring the behavior of each memory
component. This information is then communicated to the individual components, which they can use to
adapt their own memory usage.
When SQL Server starts up it computes the size of the virtual address space (VAS). This size depends on
the server type (32 or 64 bit) and the OS (again, 32- or 64-bit based). Note that this is not dependent on
the amount of physical memory installed.
One recommendation concerning tuning is to use a 64-bit solution. Memory management is much easier
using the 64-bit architecture, for both the DBA and SQL Server (the humans and non-humans). It's easier
for the DBA because special startup flags and memory options, such as AWE, don't have to be used.
(Note, however, that with a 64-bit setup, the AWE can be used for creating a non-paged pool.) It's easier
for SQL Server because there's so much more allowable virtual address space.
Even though there are several memory components, the two most concerned with performance are the
procedure cache and data cache. No doubt you've deduced that SQL Server has to perform a delicate
balancing act in order to provide adequate memory resources for both. The procedure cache holds all
the query plans that SQL Server has generated through its optimization process. The data cache contains
the latest data that SQL Server has read or written. It's easy to understand the dilemma that SQL Server
must address. Generating optimized query plans is not a trivial task. Therefore, once a plan has been
created, SQL Server will attempt to hang onto it as long as possible. Likewise, retrieving (or writing) data
from a hard drive is tremendously expensive. Again, once SQL Server retrieves a piece of data, the server
willtrytohangontothataslongaspossibletoo.
DataCache
The data cache usually occupies the largest amount of memory. It's also very volatile. You can think of
the page in the data cache as an in-memory, mirror image of a data page stored on disk. As a data page
is read or written it must first be brought into the data cache. Once the page is in memory the operation
can proceed. As you can imagine, SQL Server's data cache can fill up pretty quickly even for moderately
active database applications.
In-Memory Data Pages
In order to expedite accessing the data pages in the data cache, SQL Server organizes the pages in a
hash table structure. Recall from the discussion about join types that a hash structure requires a hashing
function in order to define a logical way to create groups. For the data cache, SQL Server uses the page
identifier as the basis for the hash function. You should also recall that a hash structure is like an
on-demand index. The hash structure allows SQL Server to find a specific page in cache with just a
few reads of memory. Likewise, SQL Server will just as easily determine that a specified page is not in
the data cache, and therefore must be read from disk.