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

Posted on 2011-04-27
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:

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:

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
    LVL 9

    Accepted Solution

    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


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now