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

When to Consider More Memory

The most common set of configuration parameters that DBAs have had to manage are those related to memory. However, starting with version 7.0, Microsoft has provided auto-management for both the total amount of server RAM given to SQL Server as well as the allocation of the different memory regions such as the buffer and plan cache.

The min server memory and max server memory parameters govern the total gift of RAM provided SQL Server from the Windows machine. These two parameters default in such a way as to allow SQL Server to dynamically adjust the feed of memory to the database engine. In normal circumstances, there is little need to disengage from SQL Server autopilot and take manual control of memory allocations. It is not uncommon for SQL Server to experience memory pressure and require more memory than it is possible for it to obtain at that time.

When deciding whether SQL Server could benefit from more physical memory, the following checklist can be used:

  • Is the SQL Server total server memory statistic at or near the target memory measure? If so, SQL Server may need more RAM but be physically constrained from allocating more.

  • Is the SQL Server page life expectancy statistic less than 300? If TRUE, and large table scans do not seem to be a problem, a shortage of RAM may be the cause.

  • Are the key memory ratios: buffer cache; plan cache; and ad-hoc SQL below desired levels? While not the best indicators of memory pressure, they can indicate a memory constrained server.

  • Is the Windows performance counter of Memory: Available Bytes consistently low or near zero?

  • Is the Windows performance counter of Memory: Pages/sec consistently high, indicating paging and swapping activity?

Another situation in which more memory may be needed occurs when numerous query plans are involved in hash joins or sort operations. These particular actions require more memory resources than other query-based operations and may benefit from increasing the min memory per query parameter. Increasing the default may increase performance for these types of SQL requests, but they will also increase overall memory consumption.

More memory may not be the silver bullet for an ailing SQL Server, but it can work miracles in the right situation in which a server has been accidently undersized for the workload.


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