Resolving SQL Server Database Performance Issues With Flash-Based Solutions

Hybrid Data Storage

For many database administrators reducing SQL Server wait times and application latency while supporting high availability are high priorities on their to-do lists. Typically, user applications dependent on SQL Server for their information store require low latency service times for OLTP workloads, however, many of today’s workload-intensive business applications are constrained by the I/O performance limitations of hard disk drives (HDDs).

One possible solution is to use solid state drives (SSDs) to resolve the problem because they can provide up to a thousand times more transactions per second, but this approach comes with a much higher cost per gigabyte.

The good news is that most applications do not require such an indiscriminate use of SSD technology to resolve I/O performance limitations. Because many of them cause ‘hot spots’ of transactions that only affect a certain percentage of the database, latency can be significantly improved by identifying the ‘hot spots’ and relocating those portions of the database onto flash.

This works by placing hot application data in caching SSD storage which can then service subsequent hot data requests. Once the SSD cache becomes completely full, cached read and write data is replaced using intelligent policies that continuously track and retain hot spot regions as they change. This ensures that only the “hottest” of the hot data is retained in the caching SSD storage.

By using a hybrid approach that deploys SSDs to handle hot data while retaining HDDs to deliver the bulk of storage requirements, database administrators can address performance bottlenecks, reduce transactional costs and improve overall application performance, without extensive investment in flash technology.

To make the best use of SSDs, administrators previously had to monitor and configure performance-critical applications, but the technology now exists to intelligently and dynamically handle critical application acceleration and achieve the data performance requirements without the need to monitor and configure applications. At the same time, administrators can also deliver enhancements to the I/O performance of their HDDs.

Database administrators are also keenly aware that many enterprise applications need to be available in the event of failure. With the incorporation of high availability into SQL Server 2012 using the AlwaysOn Availability Groups (AAG) feature, a secondary server can be enabled to resume database transactions from applications nearly instantly if the primary server goes down.

Secondary replicas are active read-only databases that can be used to offload workloads from the primary for reporting, analytics, and backups. Each AAG supports one primary replica and up to four secondary ones. Multiple availability groups are supported and there is no enforced limit, but it is dependent on workload and server resources.

Another advantage, database administrators can gain from the new SQL Server 2012 features is to achieve better response times in remote offices and branch offices. With selected solutions, database administrators can locate Tempdb on local storage without compromising the high availability. Depending on the workload Tempdb can be a source of contention with regard to latency, and accessing it locally means much less latency. The response times of the Tempdb will be further accelerated by using flash as its primary storage.

Until now, SQL Server Instance high availability has been supported only by locating all of the databases on storage area network (SAN)-accessed shared storage, but this new technology in SQL Server 2012 allows direct-attached storage (DAS) to be used for the tempdb database offering flexibility and cost savings.

With SQL Server 2012 Availability Groups Database Administrators now have another choice to specify which databases they want to make highly available. By using only direct-attached storage (DAS) this option reduces the cost and management complexity of storage compared to traditional SAN solutions.

AAG reinforces the trend towards hybrid storage by enabling organisations to get the best of both worlds from flash and HDD technology. Key business applications can be accelerated for DAS using application acceleration technology and flash where appropriate to lower SQL Server wait times, improve latency and boost transaction throughput.

By adopting a hybrid approach, database administrators can significantly improve the performance of their SQL Server environment without being forced to invest huge sums in new SSD technology. A hybrid storage strategy seeks to provide the right blend of speed from SSDs with the lower cost higher capacity of HDDs. The good news is that the technology already exists to enable database administrators to achieve the right balance between SSDs and HDDs and deliver significant enhancements to their SQL Server environments.

SHARETweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Pin on PinterestDigg thisShare on RedditShare on TumblrShare on StumbleUponEmail this to someone
Thomas Pavel

Thomas Pavel currently holds the position of Director Channel Sales EMEA. In this role Thomas is responsible for LSI’s storage Channel business throughout region. He also manages sales support for LSI’s major OEM accounts in EMEA, covering ICs and board level products. Beginning his career in the semiconductor industry in 1991, Thomas is a sales professional with many years of experience in key account management in a variety of different market segments, including; storage, server and PC, automotive and digital consumer. Thomas completed his degree in electrical engineering at the Fachhochschule Schweinfurt and is currently based in LSI’s Munich office.