Improving the Performance of Inserts in SQL Server

Not all All-Flash Arrays (AFA) are created equal.

Just moving to an AFA doesn’t always guarantee performance improvements on the database side. That said, if your database workload is I/O bound and the storage is the bottleneck, an AFA move is likely to help drive great improvements. However, there are times when you will need to make changes to the application in order to benefit from the AFA.

One such example comes from a recently completed project by one of Kaminario’s key partners, CrossRealms, with a prospect running a large SQL Server Data Warehouse (DWH). The customer’s goal was to improve an Insert query that loads data to a Columnstore index.  The queries reading the data were much faster on the K2 AFA but to their surprise, the faster loading times didn’t appear to improve application performance.

After a little investigation, it was determined the lack of performance improvement was because there was no  I/O wait when executing the Insert query. The query was single threaded and the throughput that a single thread was able to push wasn’t causing an I/O bottleneck.

Experts from CrossRealms were able to take advantage of new features in SQL Server 2016 by changing the queries to add TABLOCK hint (which allows SQL Server to use parallelism). This resulted in an instantaneous and noticeable improvement in the application performance.

 

MAXDOP Setting Circumstance Query Execution Duration
8 Single threaded 10 – 20M rows 12:02
8 Parallel: Using TABLOCK , 0 – 10M rows 3:40
8 Parallel: Using TABLOCK , 10 – 20M rows 3:33
8 Parallel: Using TABLOCK , 20 – 30M rows 3:39
8 Parallel: Using TABLOCK , 30 – 40M rows 3:55
8 Parallel: Using TABLOCK , 40 – 50M rows 3:59
8 Parallel: Using TABLOCK , 50 – 60M rows 3:35

 

There are some key lessons that were learned after this exercise. Let’s look at some of those –

 

  1. While All-Flash arrays are clearly the dominant force in accelerating workload performance (especially AFAs architected the “right way”), just faster flash storage sometimes is not enough. Database applications may also need to be tuned to achieve best performance.
  2. Strong value-added partners matter! Kaminario is a 100% committed to building a strong, sustainable and technically adept channel that can provide customers with the right level of support. Regardless of what other storage vendors tell you, the answer isn’t always “more storage” for your performance need.

 

New Call-to-action