Dell Acceleration Appliances for Databases - Manual (Page 79)

Best Practices for MS SQL Server
_____________________________________________________________________
This section outlines best practices for using ION Accelerator with MS SQL Server.
MS SQL SERVER IMPLEMENTATION BEST PRACTICES
• Configure ION Accelerator and export its volumes to the SQL DB nodes (standalone or cluster)
• Run the SQLIO synthetic benchmark tool to measure the IOPS and bandwidth for volumes, with
various workloads.
• Make sure the SQLIO performance stats come closer to the theoretically possible performance of
the hardware setup (HBAs on the target, initiator, and ioDrive/ioScale).
• Record your performance stats. These will serve as the baseline for the setup if application
performance issues are noted during workload tests.
SQL SERVER MEMORY ALLOCATION
• Leave enough memory for the OS processes. As a best practice, deduct 1GB for every 4
cores and allocate the rest to the “Max server memory (MB)” parameter.
• Use the following dynamic management views to troubleshoot memory issues that may
occur:
o sys.dm_os_memory_brokers
provides information about memory allocations using
the internal SQL Server memory manager. The information provided can be useful in
determining very large memory consumers.
o sys.dm_os_memory_nodes
and
sys.dm_os_memory_node_access_stats
provide summary information of the memory allocations per memory node and node
access statistics, grouped by type of page. This information can be used to quickly
obtain summary memory usage, instead of running
DBCC MEMORYSTATUS
.
o sys.dm_os_nodes
provides information about CPU node configuration for SQL Server.
This DMV also reflects software NUMA (soft-NUMA) configuration.
o sys.dm_os_sys_memory
returns the system memory information. The “Available
physical memory is low” value in the
system_memory_state_desc
column is a sign
72