Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter