White Papers

45 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
7.3 SQL Server Best Practices
The following SQL Server best practices were implemented for the tests documented in this paper.
7.3.1 SQL Server startup parameters
The following startup options are recommended to be added to the SQL Server Startup options:
• -E: This parameter increases the number of contiguous extends in each file that are allocated to
a database table as it grows. This option is beneficial because it improves sequential access.
• -T1117: This trace flag ensures the even growth of all files in a file group when auto growth is
enabled.
• SQL Server Maximum Memory: It is recommended to allocate no more than 92% of total server
RAM to SQL Server. If additional applications will share the server, the amount of RAM left
available to the operating system should be adjusted accordingly. For this reference architecture,
the maximum server memory was set at 117.76 GB out of 128 GB RAM.
• Lock Pages in memory: Grant the windows lock pages in memory privilege to the SQL Server
service account. This should be done to prevent the Windows operating system from paging out
the buffer pool memory of the SQL Server process by locking memory that is allocated for the
buffer pool in physical memory
7.3.2 Database volume creation
• Use a basic disk storage type for all EqualLogic volumes.
• Use the default disk alignment provided by Windows 2008 or greater.
• Use an NTFS file system with a 64 KB allocation unit for SQL database and log partitions.
7.3.3 Files and file groups
A database file is a physical allocation of space and can be designated as primary (.mdf), secondary (.ndf),
or log (.ldf). Database objects can be grouped in file groups for allocation, performance, and
administration purposes. User defined and primary filegroups are the two types of file groups and either of
them can be the default filegroup. The primary file is assigned to the primary filegroup. Secondary files can
be assigned to user filegroups or the primary filegroup. Log files are never a part of a file group. Log space
is managed separately from data space. Microsoft’s recommendations are:
• If the primary filegroup is set as default, the size or the auto grow setting needs to be carefully
planned to avoid running out of space.
• Microsoft recommends that with larger database deployments that are easily administrated and
for performance reasons, to define a user-defined filegroup as the default. In addition, create all
secondary database files in user-defined filegroups so that user objects do not compete with
system objects for space in the primary filegroup.
7.3.4 Data file growth
If sufficient space is not initially assigned to a data warehouse database, the database could grow
continuously while loading and performance would be affected. Performance is improved if the initial file