Database Reference
In-Depth Information
including RAID and using server-side flash acceleration technology to increase
performance and provide greater return on investment. We concluded the chapter by
covering off one of the biggest IT trends and its impact on SQL Server:
hyperconvergence and scale-out, shared-nothing architectures.
Let's briefly recap the key SQL design principles:
Your database is just an extension of your storage
. Make sure you optimize all
the IO paths from your database to storage as much as possible and allow for
parallel IO execution.
Performance is more than just the underlying storage devices
. SQL Buffer
Cache has a direct impact on read IO, whereas virtual IO controller device queues
and LUN, HBA, and Storage Processor queues can all impact performance and
concurrency of IO before anything touches a physical storage device.
Size for performance before capacity
. If you size for performance, capacity
will generally take care of itself. Much of this is due to the overheads associated
with RAID storage needed to provide enterprise-grade data protection and
resiliency. Use flash storage and automatic tiering to balance the performance and
capacity requirements to get a more cost-effective solution overall.
Virtualize, but without compromise
. This involves reducing risk by assessing
current performance, designing for performance even during failure scenarios,
validating your design and its achievable performance, and ensuring storage
quality of service, such as Storage IO Control. These all contribute to a successful
SQL virtualization project. Make sure project stakeholders understand what
performance to expect by having SLAs aligned to achievable IOPS per TB.
Keep it standard and simple
. Whatever design decisions you make for your
environment, keep them as consistent as possible and have defined standards.
Design for as few options as possible in your service catalog that cover the
majority of system requirements. Only deviate from defaults where required.
We have covered storage performance in depth, as it is one of the most critical
resources for a SQL Database. The next chapter will drill into how SQL memory
allocation impacts the performance of your database, and how SQL and memory might
change in the future.
Tip
Throughout this chapter, we have referred to SQL Server trace flags. A full list of
the trace flags can be viewed at
sql-server.aspx
. To enable trace flags when using Windows 2012, you need to
run the SQL Configuration Manager, which doesn't appear in the list of
