Database Reference
In-Depth Information
has limits on the number of PCI cards that can be installed, thus limiting the
number of I/O channels and therefore bandwidth it can receive.
To address these challenges bothin hardware and software, we need toscale
beyond shared resources. We need to “share nothing,” and we need to scale
out beyond a single set of resources. In our house, we've addressed the Xbox
One scalability problem by having multiple controllers. However, that still
means we all have to play the same game. The next step would naturally be
to have multiple Xbox Ones to resolve that particular problem.
Data Volumes and I/O Throughput
Data warehouses grow. That should go without saying. However, this has
not traditionally always been by as much as you might have thought. Of
course, as warehouses mature and as businesses grow, more transactions
flood in, expanding the need for capacity. However, this type of growth is
usually manageable and tends to be measured by a percentage figure.
In recent times, warehouses have grown from gigabyte (GB) scale to
terabyte (TB) scale. In an SMP world, that is manageable. Granted, greater
care has to be taken to ensure that this data is delivered through the I/O
channels at sufficient speed to keep the CPUs busy, but at this scale this has
all been managed through reference architectures such as the SQL Server
Fast Track Data Warehouse.
Fast Track provides users with tightly scoped hardware configurations that
provide a balance of the three key compute resources—CPU, I/O, and
memory. This has been coupled with software deployment and development
guidance that maximize the benefits of these configurations. With Fast
TrackI/O,throughputhasreachedfiguresof10GB/secto12GB/sec,leading
to index and aggregate light designs. Where the response rates have been
insufficient, these gaps have been plugged through the use of summary
aggregates.
However, limits apply. Even the largest Fast Track deployments tap out at
120TB. That seems like a lot, but even at this scale, queries are starting
to suffer from the lack of available throughput. Loading times can also
suffer. To maintain the I/O throughput, Fast Track data warehouses must
conform to a loading pattern that is optimized for sequential I/O. This
loading pattern mandates a final insert using MAXDOP 1, which limits the
overall load speed. Furthermore, Fast Track architectures are predicated on
the assumption that SQL Server can handle a maximum of roughly 300MB/
