are any of these recommendations causing too much ram and cpu to be used.  How to fix

Posted on 2011-04-27
Medium Priority
Last Modified: 2012-06-21
this a 'sql doctor' scan of my sql

Finding: SQL Server is experiencing relatively high levels of page splits and its memory is not under stress
Recommendation ID: SDR-M15
Impact: HIGH
Category: MEMORY
Rank: 3

Page splits are currently accounting for 100% of all new page allocations.

It is recommended that the fill factor be lowered on indexes that are experiencing a high number of page splits. This will result in better insert and update performance.
Learn more about: http://www.sql-server-performance.com/faq/reduce_page_splits_p1.aspx

Finding: The Operating System is set to give best performance to foreground applications
Recommendation ID: SDR-M21
Impact: LOW
Category: MEMORY
Rank: 12

SQL Server performance will increase if the setting is changed to 'Allow best performance of background services'.

It is recommended that your Operating System should have the setting set to 'Allow best performance of background services' to improve SQL Server performance.
Learn more about: http://www.sql-server-performance.com/articles/audit/operating_system_performance_p1.aspx

Finding: The SQL Server user right “Lock Pages in Memory” is not being used
Recommendation ID: SDR-M1
Impact: LOW
Category: MEMORY
Rank: 14

Granting the “Lock Pages in Memory” user right to the SQL Server service on a 64-bit system allows SQL Server to manage its memory allocations to ensure that critical data remains in physical memory.

Why is this a problem?
•The right “Lock Pages in Memory” is necessary in order to enable AWE on 32-bit SQL Servers.  AWE has no impact on 64-bit systems, but the user right does still allow SQL Server greater authority to manage its own memory use.
•Without this user right granted the SQL Server is unable to manage what portions of its memory are stored in physical RAM and what portions are paged out to virtual memory.
•SQL Server performance is degraded significantly when critical data is paged out of physical memory.

When is this not a problem?
•When multiple applications are running on a single server, giving SQL Server the right to “Lock Pages in Memory” without also configuring the maximum server memory setting can result in other applications or the operating system becoming unable to allocate sufficient memory.  If it is not possible to configure the maximum server memory for SQL Server, then the “Lock Pages in Memory” right should not be granted.
•When multiple applications are running on a single server and have the “Lock Pages in Memory” right, each application should be configured with an upper bound for memory consumption.  Essentially, the total system memory should be divided up and portioned to each application including the operating system in order to ensure that none of your essential applications become memory starved.  If any of the applications running on the server do not support this configuration, then the “Lock Pages in Memory” right should not be granted.

It is recommended that you review the memory usage of all applications running on the server to determine if it is appropriate to grant the user right “Lock Pages in Memory” to the SQL Server.  You should NOT try to set the AWE switch on 64-bit systems as it has no effect.

However, be aware that When SQL Server manages its own physical memory usage, it may not yield memory to the operating system, which can lead to memory starvation in other applications running on the server.  To avoid this situation, you should ensure that the SQL Server configuration option “Max Server Memory” is also configured to set up upper bound which will leave sufficient memory available for the operating system and other applications.

Question by:rgb192

Accepted Solution

radcaesar earned 2000 total points
ID: 35478807
The first will increase the SQL Server Performance.

The second will retain more memory and decrease performance (If its 32 bit, N/A for 64 bit)

Author Closing Comment

ID: 35485381

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question