White Papers

15 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
6 DSS performance studies using TPC-H Benchmark Factory
A series of DSS application simulations were conducted using Benchmark Factory as the simulation tool
running a TPC-H like workload. TPC-H is an industry standard benchmark for DSS. DSS user queries were
run from Benchmark Factory against the database to understand the I/O behavior at the storage arrays
when the SQL Server database executed those queries.
Before beginning the test runs, the database was populated and backed up to a backup array in the SAN
(refer to Figure 2). The backed up database was restored as necessary before beginning subsequent test
runs. This allowed each test run to start from exactly the same state.
6.1 SQL Server startup parameters test studies
Microsoft recommends using the below startup parameters and settings for the SQL Server Fast Track
Data warehouse configuration (reference http://msdn.microsoft.com/en-us/library/hh918452.aspx
).
These parameters were set in the following tests to evaluate the performance benefits.
• -E: This parameter increases the number of contiguous extents in each file that are allocated to
a database table as it grows. This option is beneficial because it improves sequential access.
Refer to http://support.microsoft.com/kb/329526
for details.
• -T1117: It is recommended to pre-allocate data file space rather than depending on auto grow.
However, if auto growth is enabled, then this trace flag ensures the even growth of all files in a
file group when auto growth is enabled. For the tests performed, the data file space has been
pre-allocated.
• Enable option Lock Pages in Memory. For more information, refer to
http://support.microsoft.com/kb/918483
• SQL Server Maximum Memory: For SQL Server 2012, Fast Track 4.0 guidelines suggest
allocating 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 test, the TPC-H database was loaded from Benchmark Factory by placing the two largest tables,
LineItem and Order , in separate data files and then placing these files on separate volumes. All other tables
were placed in a separate file belonging to a separate volume. This data layout pinpointed the table that
produced the largest throughput while running TPC-H user queries. Identifying the table that constituted
for the largest percentage of the total read throughput was crucial for the subsequent table partition tests.
The configuration parameters used for this test are shown in Table 2.