| |
 |
|
SQL Server Tips by Burleson |
Memory Ratios
Most DBAs would fit all their key databases into RAM if they could,
but alas, with database sizes growing at an average of 42% a year,
this is not a viable option. So, DBAs and developers alike work to
keep data access to a minimum and attempt to keep as much often-used
data in memory as possible so response times remain low and
acceptable.
In addition to keeping referenced data in memory, DBAs are becoming
all too aware that keeping parse and compile activities of queries
and code objects low plays a big part in performance. All database
engines, including SQL Server, keep query and code plans in a cache,
the Plan Cache, so identical requests for both code object and
queries can avoid the sometimes lengthy parse process and proceed
straight to execution helping runtime performance.
An exception to this rule applies when a procedure or query depends
on parameters that are passed into it playing a part in how SQL
Server should execute the request or affect the size of the result
set. Whereas one plan may work great for a particular input
parameter, another parameter may dramatically alter the result set
returns and run much better with a different access plan.
SQL Server has offered dynamic memory management since version 7.0.
This means the DBA can turn loose the reins and let SQL Server
determine workload demands and adjust memory accordingly, with one
eye always on overall server memory utilization, in order to keep
performance high. The basic memory regions SQL Server uses are the
database or buffer cache, the plan cache, referred to in older
versions as the procedure cache, and workspace memory.
The database cache holds 8KB pages that contain database
information. SQL Server attempts to eliminate seldom-used pages from
the database cache so room is left for often-referenced data.
As has already been mentioned, the plan cache holds compiled and
executable plans for both code objects (procedures, etc.) and ad-hoc
queries.
Workspace memory is sometimes required for database requests that
require hashing or sorting operations. There are also miscellaneous
areas of memory used for locking and such.
Before looking at the various memory efficiency ratios, it is a good
idea to understand how much memory SQL Server has allocated across
the various memory regions. The up_memory_status procedure below
works for SQL Server 2000 and 2005 and gives a quick overview of all
primary memory allotments
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
