SQL Server Hardware Tuning and Performance Monitoring - Memory Tuning Issues
(Page 3 of 5 )
Memory Tuning: The Operating System and SQL Server Start your intensive analysis of memory by looking at two counters,
- Memory: Available Bytes
- Memory: Pages Faults/sec
The Available Bytes counter tells how much memory is available for use by processes.
The Pages Faults/sec counter tells us the number of hard page faults, pages which had to be retrieved from the hard disk since they were not in working memory. It also includes the number of pages written to the hard disk to free space in the working set to support a hard page fault.
A low number for Available Bytes indicates that there may not be enough memory available or processes, including SQL Server, may not be releasing memory. A high number of Pages Faults/sec indicate excessive paging. Further looks at individual instances of process:Page Faults/sec to see if the SQL Server process, for example, has excessive paging. A low rate of Pages Faults/sec (commonly 5-10 per second) is normal, as the operating system will continue to do some house keeping on the working set.
As previously noted, starting with SQL Server 7, memory is auto-tuning by default. In general though, you want to give SQL as much dedicated memory as possible. This is mostly dependent on what other application may be running on the server. By using the sp_configure stored procedure, you can set the values to MIN SERVER MEMORY and MAX SERVER MEMORY to dedicated values.
If SQL Server is the only application on the server, MIN SERVER MEMORY and MAX SERVER MEMORY to the same value. If SQL Server co-exists with one or more applications, lower the MIN SERVER MEMORY setting to count for the memory demands of the other application(s). If the other application fails to start in a timely manner, it may be because SQL Server has been operating at or near the MAX SERVER MEMORY setting and is slow in releasing memory to the new, and now starved, application. In this instance, lower the value of MAX SERVER MEMORY. Obviously, MAX SERVER MEMORY always needs to be greater than or equal to MIN SERVER MEMORY.
NOTE: If you have installed and are running the Full-Text Search support (Microsoft Search service, also known as MSSearch), then you must set the max server memory option manually to leave enough memory for the MSSearch service to run. Microsoft supplies a handy formula here:
Total Virtual Memory (SQL Server MAX + Virtual Memory for Other Processes) = 1.5 * Server Physical Memory.
Once we have tuned the SQL Server memory settings, it is a good idea to decide if you want SQL Server 7/2000 to tune the process memory automatically or have values set for the configuration. For better performance, you can lock the amount of working set memory that SQL Server reserves. The trade-off here is that you may receive out of memory messages from other applications on the same server.
If you do decide to fix the amount of working set memory, two configuration settings are necessary. First, equalize the MIN SERVER MEMORY and MAX SERVER MEMORY settings. Then turn on the SET WORKING SET SIZE configuration flag using sp_configure. MAX SERVER MEMORY should generally not exceed the RAM available for the server.
SQL Server Process Memory Tuning
Once you have gotten the overall OS and SQL server memory tuned, look further at the SQL Server memory usage. Four counters are desirable here:
- process: Working Set:sqlserver
- SQL Server: Buffer Manager: Buffer Cach Hit Ratio
- SQL Server: Buffer Manager: Free Buffers
- SQL Server: Memory Manager: Total Server Memory (KB)
The process: Working Set:sqlserver instance shows the amount of memory that SQL Server is using. If the number is consistently lower than the amount SQL Server is configured to muse by the MIN SERVER MEMORY and MAX SERVER MEMORY options, then SQL Server is configured for too much memory. Otherwise, you may need to increase RAM and MAX SERVER MEMORY.
Buffer Cach Hit Ratio should be consistently greater than 90. This indicates that the data cach supplied 90 per cent of the requests for data. If this value is consistently low, it is a very good indicator that more memory is needed by SQL Server. If Available Bytes is low, this means that we need to add more RAM.
When Free Buffers is low, this means that there is not enough RAM to maintain a consistent amount of data cach. It too, is indicative of a need for more memory.
If Total Server Memory for SQL Server is consistently higher than the overall server memory, it indicates that there is not enough RAM.
Next: More Performance Monitoring Issues >>
More SQL Server Articles
More By Sayed Geneidy
/ 37 
