What’s The Best Flash Acceleration Strategy For SQL Server?

Simply adding flash to provide more storage IOPS doesn’t guarantee better application performance, steps must be taken to ensure that the right data is in flash when it’s needed. Accelerating applications like SQL Server that can have data sets TBs in size or larger, requires the calculated movement of the right data into and out of the flash area. That said, some performance-critical subsets of data are small enough to be pinned into flash, especially in database applications.

This means no ‘one size fits all’ solution really exists in flash implementations. The most effective strategy would be to provide advanced “intelligent” caching with an awareness of which data is most important and when, plus the ability to pin those most critical data sets into flash. OCZ’s ZD-XL SQL Accelerator was designed to do just that, by combining high performance PCIe form-factor hardware with advanced caching software, that has an awareness of the way SQL Server uses and protects data.

Flash Volumes

Accelerating file data is a relatively simple process based on determining which files are accessed most often and making sure they’re on the fastest storage tier, like flash, when they’re needed. But databases applications like SQL Server are more complex, so acceleration involves more than just identifying ‘hot data’ and moving it into cache.

Databases have certain data objects, like tempDB files that contain data typically placed in system memory. But often, when enough RAM isn’t available, some of these data are moved into disk storage, greatly increasing access latency. Flash is an ideal storage area for these performance critical data, and a caching device that can be partitioned to create a flash volume for this purpose can accelerate SQL Server performance significantly.

ZD-XL SQL Accelerator leverages the advantages of being close to the CPU by running on the PCIe bus. This enables on-host volumes to be run in flash, effectively augmenting the available RAM capacity and eliminating the latency created when the overflow of critical data is placed on disk storage.

As an example, in SQL Server query results can spill over to the tempDB file when they run out of RAM. By exposing a portion of its host-based flash capacity to SQL Server, the ZD-XL SQL Accelerator can enable the application to direct tempDB write operations to a virtualized flash volume instead of disk storage. Its flash controller can also distribute read and write loads to all available flash cells in parallel, increasing the performance of these random operations.

Flash Caching

The essence of caching is identifying which data is the ‘hottest’ and making sure it spends the most time in flash, or at least making sure it’s there when the application needs to access it. The successfulness of this process is called the “hit ratio” and improving it requires having some intelligence into the workloads that are being accelerated and an understanding of the application environment.

Access DNA

The ZD-XL SQL Accelerator detects which data locations are most frequently accessed but also performs “sequentiality detection”. This process filters out irrelevant data associated with background processing tasks like error checking and index creation that are associated with relevant data needing to be cached. It can also detect these irrelevant data accesses by inspecting the command sizes being generated by SQL Server. Called the “access DNA”, this ability to know how an application uses data is critical to improving the cache hit ratio.

Cache Policy Optimization

Accelerating SQL applications also requires an understanding of the different types of workloads that are present. SQL Server implementations typically include transactional and analytical workloads, which generate different data access requirements. Transactional workloads, such as capturing sensor data or supporting internet commerce, involve fast read and write operations resulting from relatively small queries. Analytical workloads, on the other hand, involve initial sequential writes and then multiple sequential reads, mixed with random reads to perform analysis or reporting. Being able to differentiate between these two workload types and efficiently accommodating each can help optimize caching decisions and increase hit ratios.

Direct Pass Caching

Instead of making CPU-intensive caching decisions within the data path, which can increase latency, the ZD-XL SQL Accelerator’s Direct Pass Caching technology makes caching decisions based on dynamically optimized policies. These policies are updated with a cache engine analysis module, out of band, which incorporates the latest intelligence about currently running workloads and the appropriate access DNA into this dynamic caching process.

Dynamic Pre-warming

Caching algorithms focus on improving the caching hit rate by determining which data is the most appropriate for the cache at a given point in time. By using intelligence about the application involved and the workloads, data types and access patterns generated, caching solutions attempt to gain some insight into the data that should be in cache before it’s actually needed. But determining which data objects should be in cache isn’t always such a mystery.

Many processes performed by an application are regular activities, scheduled to be run on a recurring basis. Database applications like SQL Server involve scheduled processing runs corresponding to monthly financial events or historically high customer transaction periods. In these scenarios there’s no question about which data needs to be cached, but there are still things that can be done to improve caching performance.

The ZD-XL SQL Accelerator monitors SQL workloads and, leveraging a ‘business rule’ pre-warming cache engine, determines which data is needed to support these regularly scheduled activities. It then pre-warms the cache by moving these data into the cache before it’s needed.

User Experience

Cache operations involve a certain amount of configuration and management and a solution that purports to accelerate an environment like SQL Server should be as easy as possible to set up and run. After all, the SQL environment is operated by SQL experts not flash performance experts. The ZD-XL SQL Accelerator has a GUI management wizard that advises the DBA as to which data/workload to place on the flash volume, providing a list of each volume so that the best policy can be selected for either a transactional or analysis workload. It also guides the administrator through cache pre-warming and monitors the performance delivered to the SQL Server environment by the XD-XL SQL Accelerator.

MS AlwaysOn Availability Group

The kinds of applications that drive SSD use are typically the ones that also need uptime protection. These mission critical and business critical databases that run on SQL Server often leverage Microsoft’s AlwaysOn Availability Group as well, which is fully supported by the ZD-XL SQL Accelerator.

The AlwaysOn Availability Group supports the synchronous or asynchronous mirroring of data between servers on the ZD-XL cards using Mellanox’s ConnectX-3 EN 40GbE NIC. This high speed connectivity assures the fastest replication between the primary and secondary servers, keeping the mirror up to date.

Faster Reads from Secondary Server

This active/active topology supported by the AlwaysOn Failover Cluster capability provides transparent failover from the primary server to the secondary. But also, having this near-real time mirrored copy available enables the application on the secondary side to support read-only queries, removing those transactions from the primary server and improving performance.

Summary

Accelerating complex database applications on SQL Server requires more than just fast storage hardware or caching software. It also takes the application awareness to know which data needs to be in flash and when, plus the ability to reserve flash volumes to augment system memory for the most critical data objects. OCZ’s ZD-XL SQL Accelerator integrates all these component technologies into a combined easy to deploy solution that optimizes their effectiveness and adds the uptime protection of Microsoft’s AlwaysOn Availability Groups.

OCZ is a client of Storage Switzerland

Eric is an Analyst with Storage Switzerland and has over 25 years experience in high-technology industries. He’s held technical, management and marketing positions in the computer storage, instrumentation, digital imaging and test equipment fields. He has spent the past 15 years in the data storage field, with storage hardware manufacturers and as a national storage integrator, designing and implementing open systems storage solutions for companies in the Western United States.  Eric earned degrees in electrical/computer engineering from the University of Colorado and marketing from California State University, Humboldt.  He and his wife live in Colorado and have twins in college.

Tagged with: , , , , ,
Posted in Product Analysis

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 22,209 other followers

Blog Stats
  • 1,529,108 views
%d bloggers like this: