Understanding SQL Server Memory Internals
Like all database servers, Microsoft SQL Server thrives on memory. Each instance of Microsoft SQL Server
has its own memory address space. The SQL Server address space (Virtual
memory allocation by Windows) consists of two main components: executable
code and memory pool. Let's examine each of these components in more details.
SQL Server
executable code
Executable code is basically SQL Server's own EXEs and DDLs that are loaded into the address space.
The following is breakdown of the various components:
- SQL Server Engine
- Open Data Services
- Server Net-Libraries
- Distributed Queries
- Extended Stored Procedures (DLLs)
- OLE Automation Stored Procedures
SQL Server
memory pool
The memory pool is the unit of memory that an instance of SQL Server uses to
create and manage data structures relating to client requests.
The following data structures are allocated in the memory pool of an
instance of SQL Server:
- Buffer Cache: This is the pool of memory pages into which data pages are
read. An important indicator of the performance of the buffer cache is the
Buffer Cache Hit Ratio performance counter. It indicates the percentage of data pages
found in the buffer cache as opposed to disk. A value of 95% indicates that pages were found
in memory 95% of the time. The other 5% required physical disk access. A
consistent value below 90% indicates that more physical memory is needed
on the server.
- Procedure Cache: This is the pool of memory pages containing the execution plans
for all Transact-SQL statements currently executing in the instance. An
important indicator of the performance of the procedure cache is the Procedure Cache Hit Ratio
performance counter. It indicates the percentage of execution
plan pages found in memory as opposed to disk.
- Log Caches: This is the pool of memory used to read and write
log pages. Each log has a set of cache pages. The log caches are managed
separately from the buffer cache to reduce the synchronization between log and
data buffers.
- Connection Context: Each connection has a set of data structures that
record the current state of the connection. These data structures hold
items such as parameter values for stored procedures, cursor
positioning information, and tables currently being referenced.
- System-level Data Structures: These are data structures that hold data
global to the instance, such as database descriptors and the lock table.
The buffer cache, procedure cache, and log caches are the only memory
elements whose size is controlled by SQL Server.
A very important aspect to watch for is whether SQL Server is using the maximum
memory
available on the system (assuming the system is dedicated to SQL Server).
A system with a fully utilized memory may be prone to performance bottlenecks
when competition for resources increases. Prepared Transact-SQL statements, for
example, may suffer when the procedure cache is unable to expand due to fully
utilized buffer caches.
Analyzing cache hit ratios over time with Teratrax Performance Monitor
Teratrax Performance Monitor tracks the history of the buffer and
procedure cache hit ratios so you can view the trends of the ratios over
time. Performance Monitor provides two counters for cache hit ratios:
Cache Hit Ratio (Buffer cache) and Proc Cache Hit Ratio (Procedure cache). It also provides a variety of
views ranging from real-time charting of cache hit ratios all the way to
daily, weekly, and monthly charts.
Read more...
|
