Home arrow SQL Server arrow Page 3 - SQL Server Hardware Tuning and Performance Monitoring

SQL Server Hardware Tuning and Performance Monitoring

In this article, Sayed will lead the way in presenting you guys with a performance enhancing solution for your system. Discover how to optimise your system by investigating what hardware you really need for your applications.

Author Info:
By: Sayed Geneidy
Rating: 4 stars4 stars4 stars4 stars4 stars / 50
August 14, 2003
  1. · SQL Server Hardware Tuning and Performance Monitoring
  2. · Performance Monitoring Issues
  3. · Memory Tuning Issues
  4. · More Performance Monitoring Issues
  5. · SQL Server Performance Tuning Resources, Websites and Articles

print this article

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.

blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials