Dell PowerEdge External Media System 1434 Dell SMB Reference Configuration for - Page 14

Fast Track Database Validation with Column Store Index CSI, Reference Architecture Performance

Get Dell PowerEdge External Media System 1434 PDF manuals and user guides View all Dell PowerEdge External Media System 1434 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 14 highlights

Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd For the validated Fast Track configuration, the aggregate BCR was 950.75 MB/s. During the evaluation cycle, the system configuration was analyzed for multiple query variants (simple, average, and complex) with multiple sessions and different degrees of parallelism (MAXDOP) options to arrive at the optimal configuration. Fast Track Database Validation with Column Store Index (CSI) SQL Server 2012 implements CSI technology as a nonclustered indexing option for pre-existing tables. FTDW for SQL Server 2012 system design and validation is based on non-CSI benchmarks. FTDW systems are designed to run effectively in the case that no columnar optimization is achieved for any given period of time. Significant performance gains are often achieved when CSI query plans are active and this performance can be viewed as incremental to the basic system design. After the test configuration was validated, CSI was added and the I/O and the CPU saturation packages were run. The configuration was analyzed for multiple query variants (simple, average, and complex) with multiple sessions, degrees of parallelism (MAXDOP) set at 12 and Resource Governor memory grant set at 19%. Reference Architecture Performance Details Table 4 shows the performance numbers reported for the recommended reference configuration. Table 4. Performance Metrics Metric Value Description FTDW Rated Data Warehouse 5 Capacity (TB) This capacity rating is based on "up-to" capacity but adjusted to account for Fast Track Rated I/O. FTDW Rated I/O (MB/s) 950.75 Core performance metric for validation. Is the average of Physical and Logical I/O. Benchmark Scan Rate Logical 1156.5 (MB/s) Reflects actual user query throughput which includes reads from RAM/Buffer cache. Benchmark Scan Rate 745 Physical (MB/s) Reflects physical IO read from disk during benchmark. FTDW Peak I/O (MB/s) 1512 Maximum observed IO rate. FTDW Rated CSI (MB/s) 1662.5 Represents potential throughput using Columnstore Index. 14

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on
PowerEdge R720xd
14
For the validated Fast Track configuration, the aggregate BCR was 950.75 MB/s.
During the evaluation cycle, the system configuration was analyzed for multiple query variants (simple,
average, and complex) with multiple sessions and different degrees of parallelism (MAXDOP) options to
arrive at the optimal configuration.
Fast Track Database Validation with Column Store Index (CSI)
SQL Server 2012 implements CSI technology as a nonclustered indexing option for pre-existing tables.
FTDW for SQL Server 2012 system design and validation is based on non-CSI benchmarks. FTDW systems
are designed to run effectively in the case that no columnar optimization is achieved for any given
period of time. Significant performance gains are often achieved when CSI query plans are active and
this performance can be viewed as incremental to the basic system design.
After the test configuration was validated, CSI was added and the I/O and the CPU saturation packages
were run. The configuration was analyzed for multiple query variants (simple, average, and complex)
with multiple sessions, degrees of parallelism (MAXDOP) set at 12 and Resource Governor memory grant
set at 19%.
Reference Architecture Performance Details
Table 4 shows the performance numbers reported for the recommended reference configuration.
Table 4.
Performance Metrics
Metric
Value
Description
FTDW Rated Data Warehouse
Capacity (TB)
5
This capacity rating is based
on “up
-
to” capacity
but adjusted to account for Fast Track Rated
I/O.
FTDW Rated I/O (MB/s)
950.75
Core performance metric for validation. Is the
average of Physical and Logical I/O.
Benchmark Scan Rate Logical
(MB/s)
1156.5
Reflects actual user query throughput which
includes reads from RAM/Buffer cache.
Benchmark Scan Rate
Physical (MB/s)
745
Reflects physical IO read from disk during
benchmark.
FTDW Peak I/O (MB/s)
1512
Maximum observed IO rate.
FTDW Rated CSI (MB/s)
1662.5
Represents potential throughput using
Columnstore Index.