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
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
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
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.