White Papers

17 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
SQL Parameter studies Figure 5
The query completion time is the duration between the first query submission and completion of the last
query. From the tests conducted with the SQL server parameters set, the total execution time of the
queries decreased by 41 minutes (10% decrease) compared to the execution without setting the SQL
Server startup parameters. However, the throughput remained the same in both the cases. The decrease in
query execution times can be attributed to the startup option setting “-E” that improved the sequential
access by increasing the number of contiguous extents in each file. Scans benefit from this contiguity
because the data is less fragmented allowing for fewer file switches and faster access. The “maximum SQL
Server memory” setting and “lock pages memory” policy setting also contributed to the execution
performance improvement. These settings prevented the system from paging memory to the disk and
making the pages remain in the memory which improved data reads.
One of the key performance metrics in DW is the query execution times. Setting these SQL Server
parameters improved DW user query execution times in the tests performed in this section. The remaining
database tests presented in this paper incorporated these settings as well in order to take advantage of the
performance benefits.
663
665
385
344
320
330
340
350
360
370
380
390
500
550
600
650
700
Without SQL tuning
parameters
With SQL tuning
parameters
Time (minute)
Read throughput (MB/sec)
SQL parameters - performance comparisions
Read Throughput
Query Completion Times