Microsoft SQL Server and Kaminario K2

Input/Output is the heart of Microsoft SQL Server and the performance of the storage sub system and it is critical for SQL Server applications. High latency or low throughput can result with significant I/O Wait, resulting in poor database response times for end users.

The top areas that are being affected by insufficient storage performance are:

Issue	Impact	With Kaminario K2
High Latency	A major issue for many OLTP applications. Mostly affects applications with high usage of index seeks (clustered and non-clustered) and high amounts of concurrent active queries. Latency for HDD starts at 3ms and can reach 20ms.	Kaminario K2 offers low latency in the microsecond range without sacrificing high availability that is critical for OLTP applications.
Low Throughput	Storage is unable to provide adequate throughput. Mostly seen in OLAP/DWH applications where read ahead is significant.	Kaminario K2’s throughput starts at 3.2 GBytes/Sec and can scale to support more than 12 GBytes/sec. In most cases, fibre channel bandwidth or initiator backplane become the bottleneck with the Kaminario K2.
Log and Tempdb contention	A major issue especially for OLTP applications where existing storage cannot scale to support burst of WRITES. As WRITES to log are in a sense synchronous to the thread, it is critical to minimize log latency.	Kaminario K2 provides consistent low latency. Latency will not change as a response to the amount of READS or WRITES.
WRITES affecting READS performance	WRITES to disk in SQL are done asynchronous and in theory do not affect end user’s response time. However, most storage can’t scale to support the burst of WRITES (for example, as a result of checkpoint) and performance of READS will be affected. System might perform within the SLA but as WRITES increase the READS performance is affected.	Kaminario K2 provides consistent performance regardless of the workload and utilization.

Kaminario K2 all solid-state storage systems are the optimal solutions for SQL Server applications that are I/O bound and suffer from high I/O Wait. Kaminario K2’s superior performance, coupled with its high availability, is a compelling reason for companies to keep their most demanding production databases on the Kaminario K2.

The following is an example of the top four sys.dm_os_wait_stats non idle events taken before and after implementing Kaminario K2. Prior to Kaminario K2, I/O Wait is the dominated Wait event.

Before Implementing on Kaminario K2 Storage Appliance

Wait Type	Count	Wait Time (Min)	Percentage	Avg Wait (ms)
PAGEIOLATCH_SH	98110454	14655.9	58.1	9.0
PAGEIOLATCH_EX	18262415	6152.8	12.5	10.4
LCK_M_IX	10227	2145.7	8.5	1288.7
WRITELOG	72367648	2155.5	8.5	1.8
LATCH_EX	5219956	837.4	2.2	6.2
LCK_M_IS	1261	690.1	1.7	19897.5
LCK_M_IU	8047479	203.3	5.1	9.6
ASYNC_NETWORK_IO	4202923	113.1	0.4	1.6
SOS_SCHEDULER_YIELD	18793758	84.3	0.3	0.3

By moving the database files and tempdb to Kaminario K2, an 8-fold improvement was experienced on the overall performance of the application:

After Implementing on K2 SAN Storage

Wait Type	Count	Wait Time (Min)	Percentage	Avg Wait (ms)
SOS_SCHEDULER_YIELD	172963429	775.5	26.2	0.3
ASYNC_NETWORK_IO	9752346	682.7	23.0	4.2
PAGEIOLATCH_SH	78312423	391.6	13.2	0.3
WRITELOG	92490621	308.3	10.4	0.2
PAGEIOLATCH_EX	14262366	95.1	3.2	0.4
LATCH_EX	377455	13.8	0.5	2.2
LCK_M_IS	984	7.4	0.3	455.0

Kaminario utilizes a simple process for evaluating performance issues and implementing the Kaminario solution to resolve them:

Performance Assessment: Performance Specialists review the performance of the application under different workloads to identify if I/O is the bottleneck. They also simulate the expected improvements of the Kaminario K2 on the application. Submit your Microsoft SQL Server database for a complimentary I/O Performance Analysis.
Planning: Performance Specialists recommend what components of SQL should move to Kaminario K2 as well as how to handle backup, mirroring and DR.
Implementation: Within several hours you can start experiencing the improved performance of Kaminario K2.

"We put all of our databases on the new Kaminario K-H and the results are amazing. Even with our databases doubling from 5 billion to 10 billion records, the K2-H cut the entire processing time from 2 weeks to 3 days."

- Amir Doron, Match and Merge Team Leader, Digital Trowel

Go to the Kaminario Storage Performance Assessment to get more information on this process and example results.