Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter