Current Filter: Storage>>>>>>

   

While the thought of adding flash to a SQL Server infrastructure is now commonly accepted, it does not guarantee overcoming the typical performance bottlenecks. By using flash hardware and intelligent software that seamlessly integrates with SQL, applications accelerate and access the right & relevant data exactly when needed.

Frequently used 'hot' data is placed on the SSD flash while older and 'cold' data resides in the SAN on the conventional HDD array. Analysing IOPS performance, one host-based flash acceleration card can replace thousands of HDDs. OCZ recently introduced the ZD-XL SQL Accelerator that tightly integrates into the SQL Server deployment as a 'Plug and Play' device, providing the DBA an easy installation including a user friendly wizard that guides him through the optimisation process.

FOUR KEY ELEMENTS
Through the analysis of SQL Server workloads, an optimised acceleration card will seamlessly deploy four key elements. When these elements are efficiently combined, IT managers can accelerate SQL Server databases by up to 25 times.

1. Flash Volumes: Transient calculation tables, such as tempDB, can be efficiently placed on server-side flash volumes. When high availability is assured, additional files such as logs can also benefit from the extremely high read/write performance from on-host flash volumes.

2. Flash Caching: Larger database volumes may not fit on server-side flash volumes. However, using optimised flash caching for hot data allows efficient acceleration of very large databases using much smaller quantities of flash.

3. Cache Policy Optimisation: The use of application-optimised policies makes sure that high hit ratios are achieved and the right data is available on flash at the correct time for SQL Server in both Online Transaction Processing (OLTP) and analysis workloads.

4. Dynamic Pre-Warming of Cache: The ability to automatically pre-warm the cache in advance of important and demanding jobs ensures that the right and relevant data resides in cache in time for use by SQL Server.

FLASH VOLUME REQUIREMENTS
SQL Server Applications are highly dependent on I/O latencies and bandwidths. In particular the PCIe based SSD flash memory provides a perfect fit for the data requirements minimising the access latencies and it can satisfy high IOPS loads comparable to dozens of SSDs and thousands of HDDs.

An example supporting the need for server-side flash volumes is data warehousing using Microsoft xVelocity columnar tables. A data warehouse workload may consume large amounts of RAM for intermediate query results, and in many cases, when SQL Server does not have enough RAM available, the queries will automatically spill into a tempDB. If the tempDB resides on a remote SAN, this redirection can create a drastic drop in database performance.

With the ZD-XL SQL Accelerator, tempDB write operations can be directed to a virtualised flash volume residing on the host, dramatically reducing any performance impact of tempDB usage. The ZD-XL SQL Accelerator can efficiently expose part of its capacity of host-based flash as a volume and make it available to SQL Server. This unique capability utilises the PCIe-based SSD for tempDB usage while simultaneously retaining other portions for use as a hot data cache from additional database volumes.

The first step then in achieving efficient SQL Server acceleration is for IT managers to make sure that the SSD card has the ability to host databases (such as tempDB) on a flash volume in parallel while accelerating other databases that use flash for caching.

FLASH CACHING SUPPORT
The critical characteristics of data are sometimes collectively referred to as the 'data access DNA' and are vital in determining what data to cache. Advanced policy engines can analyse these patterns and use this information as part of the selection criteria for determining specific data to place on SSD flash. If the server system neglects to find and place important data, SQL Server performance will drop as a result of cache misses. If unimportant data is placed in SSD flash, critical data may be displaced and no longer available for low latency server access. Therefore, the caching mechanism must possess the capability to intelligently choose what data to place in SSD flash.

Page   1  2