Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use SQL Server 2008 R2 Enterprise on my server.

The minimum required memory for installing SQL Server is 4GB.

I have several databases on my server. How can I determine the minimum required RAM on my server?

EDIT

I want to determine optimum amount of memory for performance. And I don't know affected parameter for my purpose. Please say me SQL Server and database affected parameter to determine minimum memory.

All on my database are operational.

EDIT2

I have Red Gate SQL Monitor. and have analysis chart on my server. Can I use these analysis for my purpose.

share

migration rejected from stackoverflow.com Dec 18 '13 at 14:58

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, bluefeet, Paul White Dec 18 '13 at 14:58

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Bit of an open ended question really. If its a development server you wont need anything like as much as a production server. Do you mean the minimum for it to run or the optimum amount for performance? Its not a question anyone could answer without more detail. Even then you may find its not the kind of question to be asked here. –  Fred Dec 17 '13 at 14:01
    
@Fred. Please see my edit. –  mehdi lotfi Dec 17 '13 at 14:07
1  
The question can't be answered with the information given. There are many factors to consider in sizing your server appropriately, including the size of the databases that you're hosting on the server, required response time, the workload thrown at the server, and the design of the databases and applications that depend upon them. –  alroc Dec 17 '13 at 14:17
2  
Here are a couple of links that are worth a read. sqlskills.com/blogs/jonathan/… sqlskills.com/blogs/jonathan/… –  Fred Dec 17 '13 at 14:26
1  
The Red Gate tool you are running is ideal for this type of analysis. You can find custom metrics to use for gathering that information from here if you want to dig a little deeper: sqlmonitormetrics.red-gate.com/tag/memory –  Shawn Melton Dec 17 '13 at 15:27
show 1 more comment

2 Answers

up vote 3 down vote accepted

How can I determine minimum required RAM on my server.

Its not that easy to give you exact number. Since you are using Enterprise edition, SQL Server will take as much memory as it is assigned to.

Remember that you can limit the amount of memory that sql server's BUFFER POOL can consume using min and max memory settings.

Glenn Berry has some “rule of thumb” starting guidelines for a dedicated database server, only running the DB engine, (which is the ideal situation).

enter image description here

Once you have that, you can monitor and baseline below PERFMON counters :

SQL Server:Buffer Manager\Page Life Expectancy

SQL Server:Buffer Manager\Page reads/sec

Physical Disk\Disk Reads/sec

Refer to : How much memory does my SQL Server actually need?

share
add comment

MSDN Troubleshooting Performance Problems in SQL Server 2008 chapter "Memory Bottlenecks"

... Memory Pressures

Memory pressure indicates that a limited amount of memory is available. Identifying when SQL Server runs under memory pressure will help you troubleshoot memory-related issues. SQL Server responds differently depending on the type of memory pressure that is present. ...

Detecting Memory Pressures

Memory pressure by itself does not indicate a problem. Memory pressure is a necessary but not a sufficient condition for the server to encounter memory errors later on. Working under memory pressure could be a normal operating condition for the server. However, signs of memory pressure can also indicate that the server is running close to its capacity and the potential for out-of-memory errors exists. In the case of a normally operating server, you can use information about memory pressures as a baseline for determining reasons for out-of-memory conditions later. Tools for Memory Diagnostics The following tools and sources of information can be used for memory troubleshooting:

  • General system and SQL Server state and memory-specific DMVs.
  • The DBCC MEMORYSTATUS command.
  • SQL Server ring buffers.
  • Performance counters.
  • The SQL Server error log, and Windows application and system logs. You can use

Log File Viewer in SQL Server Management Studio to look at Windows and SQL Server error logs in one place in a time-synchronized fashion. Log File Viewer is accessible through Object Explorer. For connected database servers, expand Management, and then click SQL Server Logs. For more information, see Log File Viewer (http://msdn.microsoft.com/en-us/library/dd206996.aspx)in SQL Server 2008 Books Online. New DMVs in SQL Server 2008 Several new dynamic management views (sometimes known as DMVs) in SQL Server 2008 simplify retrieval of information that can be helpful with memory troubleshooting. In some cases, newly introduced DMVs provide information that was previously available only in DBCC MEMORYSTATUS output. The following list provides a summary of new DMVs for memory troubleshooting:

  • sys.dm_os_memory_brokers provides information about memory allocations using the internal SQL Server memory manager. The information provided can be useful in determining very large memory consumers.
  • sys.dm_os_memory_nodes and sys.dm_os_memory_node_access_stats provide information about physical non-uniform memory access (NUMA) memory nodes and node access statistics grouped by the type of the page. (sys.dm_os_memory_node_access_stats is populated under dynamic trace flag 842 due to its performance impact.)
  • sys.dm_os_nodes provides information about CPU node configuration for SQL Server. This DMV also reflects software NUMA (soft-NUMA) configuration.
  • sys.dm_os_process_memory provides overview information about SQL Server memory usage.
  • sys.dm_os_sys_memory provides overview information about the system memory usage.
  • sys.dm_resource_governor_configuration, sys.dm_resource_governor_resource_pools, and sys.dm_resource_governor_workload_groups provide information about the state of the Resource Governor feature of SQL Server 2008.

Some of the configuration parameters of Resource Governor affect how SQL Server allocates memory; you should check these parameters during memory troubleshooting.

select 
    total_physical_memory_kb / 1024 as phys_mem_mb,
    available_physical_memory_kb / 1024 as avail_phys_mem_mb,
    system_cache_kb /1024 as sys_cache_mb,
    (kernel_paged_pool_kb+kernel_nonpaged_pool_kb) / 1024 
        as kernel_pool_mb,
    total_page_file_kb / 1024 as total_page_file_mb,
    available_page_file_kb / 1024 as available_page_file_mb,
    system_memory_state_desc
from sys.dm_os_sys_memory
share
    
Your answer describes how to monitor sql server memory pressure - which is good. The OP wants to know how much RAM he needs to get for an enterprise edition of sql server. I cant see how this will help OP. –  Kin Dec 17 '13 at 15:07
2  
@Kin i suppose it'll let OP know if they undersized :) –  zlatan Dec 17 '13 at 15:35
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.