Tag Archives: Columnstore

Columnstore Indexes in SQL Server 2014: Flipping the DW /faster Bit at #SQLSaturday347 / #SQLSatDC

This weekend on December 6, 2014 I continue my evangelization of Columnstore—possibly the most exciting unheralded feature in SQL Server 2014—at SQL Saturday 347 at an auspicious venue: the Microsoft Technology Center in Chevy Chase, proximal to the US capital city of Washington DC.

Register, see the schedule, or see the event home page on the SQL Saturday site.  I’ll look forward to seeing you here:

Microsoft MTC
5404 Wisconsin Ave
Chevy Chase, MD 20815

Join me & an All-Star cast of speakers including MVPs such as Reeves Smith, Joey D’Antoni, Allen White, Jessica Moss, Grant Fitchey, Steve Jones, Randy Knight, Jason Brimhall, & Wayne Sheffield.  Other luminaries include shiny new Microsoft PFE Theresa Iserman & Consultant Ayman El-Ghazali.

image

Two Presentations This Weekend at #SQLSaturday349 in Salt Lake City

I have the privilege of being selected to fill two slots this weekend at #SQLSaturday349 in Salt Lake City.  The venue is simultaneously hosting the Big Mountain Data event.

My talks are:

To the Cloud, Infinity, & Beyond: Top 10 Lessons Learned at MSIT

&

Columnstore Indexes in SQL Server 2014: Flipping the DW /faster Bit

The latter is one of my staples.  The former is a new presentation, a preview of my forthcoming delivery for PASS Summit14.

Register, see the schedule, or see the event home page on the SQL Saturday site.  I’ll look forward to seeing you here:

Spencer Fox Eccles Business Building
1768 Campus Center Drive
Salt Lake City, UT 84112

Kudos to Pat Wright (blog | @sqlasylum) & crew for their crazy efforts (pardon the pun) in coordinating the event.

TJay Belt (@tjaybelt), Andrea Alred (@RoyalSQL), & Ben Miller (@DBADuck), keep your peepers peeled—I’m on the way!

Columnstore Case Study #2: Columnstore faster than SSAS Cube at DevCon Security

Preamble

This is the second in a series of posts documenting big wins encountered using columnstore indexes in SQL Server 2012 & 2014.  Many of these can be found in my big deck along with details such as internals, best practices, caveats, etc.  The purpose of sharing the case studies in this context is to provide an easy-to-consume quick-reference alternative. See also Columnstore Case Study #1: MSIT SONAR Aggregations

Why Columnstore?

As stated previously, If we’re looking for a subset of columns from one or a few rows, given the right indexes, SQL Server can do a superlative job of providing an answer. If we’re asking a question which by design needs to hit lots of rows—DW, reporting, aggregations, grouping, scans, etc., SQL Server has never had a good mechanism—until columnstore.

Columnstore indexes were introduced in SQL Server 2012. However, they’re still largely unknown. Some adoption blockers existed; yet columnstore was nonetheless a game changer for many apps.  In SQL Server 2014, potential blockers have been largely removed & they’re going to profoundly change the way we interact with our data.  The purpose of this series is to share the performance benefits of columnstore & documenting columnstore is a compelling reason to upgrade to SQL Server 2014.

The Customer

DevCon Security provides home & business security services & has been in business for 135 years. I met DevCon personnel while speaking to the Utah County SQL User Group on 20 February 2012. (Thanks to TJ Belt (b|@tjaybelt) & Ben Miller (b|@DBADuck) for the invitation which serendipitously coincided with the height of ski season.)

The App: DevCon Security Reporting: Optimized & Ad Hoc Queries

DevCon users interrogate a SQL Server 2012 Analysis Services cube via SSRS. In addition, the SQL Server 2012 relational back end is the target of ad hoc queries; this DW back end is refreshed nightly during a brief maintenance window via conventional table partition switching.

SSRS, SSAS, & MDX

Conventional relational structures were unable to provide adequate performance for user interaction for the SSRS reports. An SSAS solution was implemented requiring personnel to ramp up technically, including learning enough MDX to satisfy requirements.

Ad Hoc Queries

Even though the fact table is relatively small—only 22 million rows & 33GB—the table was a typical DW table in terms of its width: 137 columns, any of which could be the target of ad hoc interrogation. As is common in DW reporting scenarios such as this, it is often nearly to optimize for such queries using conventional indexing.

DevCon DBAs & developers attended PASS 2012 & were introduced to the marvels of columnstore in a session presented by Klaus Aschenbrenner (b|@Aschenbrenner)

The Details

Classic vs. columnstore before-&-after metrics are impressive.

 

 

 

Scenario

Conventional Structures

Columnstore

Δ

SSRS via SSAS

10 – 12 seconds

1 second

>10x

Ad Hoc

5-7 minutes
(300 – 420 seconds)

1 – 2 seconds

>100x

Here are two charts characterizing this data graphically.  The first is a linear representation of Report Duration (in seconds) for Conventional Structures vs. Columnstore Indexes. 

image

As is so often the case when we chart such significant deltas, the linear scale doesn’t expose some the dramatically improved values corresponding to the columnstore metrics.  Just to make it fair here’s the same data represented logarithmically; yet even here the values corresponding to 1 –2 seconds aren’t visible. 

image

The Wins

  1. Performance: Even prior to columnstore implementation, at 10 – 12 seconds canned report performance against the SSAS cube was tolerable. Yet the 1 second performance afterward is clearly better. As significant as that is, imagine the user experience re: ad hoc interrogation. The difference between several minutes vs. one or two seconds is a game changer, literally changing the way users interact with their data—no mental context switching, no wondering when the results will appear, no preoccupation with the spinning mind-numbing hurry-up-&-wait indicators.  As we’ve commonly found elsewhere, columnstore indexes here provided performance improvements of one, two, or more orders of magnitude.
  2. Simplified Infrastructure: Because in this case a nonclustered columnstore index on a conventional DW table was faster than an Analysis Services cube, the entire SSAS infrastructure was rendered superfluous & was retired.
  3. PASS Rocks: Once again, the value of attending PASS is proven out. The trip to Charlotte combined with eager & enquiring minds let directly to this success story. Find out more about the next PASS Summit here, hosted this year in Seattle on November 4 – 7, 2014.

DevCon BI Team Lead Nathan Allan provided this unsolicited feedback:

“What we found was pretty awesome. It has been a game changer for us in terms of the flexibility we can offer people that would like to get to the data in different ways.”

Summary

For DW, reports, & other BI workloads, columnstore often provides significant performance enhancements relative to conventional indexing.  I have documented here, the second in a series of reports on columnstore implementations, results from DevCon Security, a live customer production app for which performance increased by factors of from 10x to 100x for all report queries, including canned queries as well as reducing time for results for ad hoc queries from 5 – 7 minutes to 1 – 2 seconds. As a result of columnstore performance, the customer retired their SSAS infrastructure.

I invite you to consider leveraging columnstore in your own environment. Let me know if you have any questions.

Columnstore Case Study #1: MSIT SONAR Aggregations

Preamble

This is the first in a series of posts documenting big wins encountered using columnstore indexes in SQL Server 2012 & 2014.  Many of these can be found in this deck along with details such as internals, best practices, caveats, etc.  The purpose of sharing the case studies in this context is to provide an easy-to-consume quick-reference alternative.

Why Columnstore?

If we’re looking for a subset of columns from one or a few rows, given the right indexes, SQL Server can do a superlative job of providing an answer. If we’re asking a question which by design needs to hit lots of rows—DW, reporting, aggregations, grouping, scans, etc., SQL Server has never had a good mechanism—until columnstore.

Columnstore indexes were introduced in SQL Server 2012. However, they’re still largely unknown. Some adoption blockers existed; yet columnstore was nonetheless a game changer for many apps.  In SQL Server 2014, potential blockers have been largely removed & they’re going to profoundly change the way we interact with our data.  The purpose of this series is to share the performance benefits of columnstore & documenting columnstore is a compelling reason to upgrade to SQL Server 2014.

App: MSIT SONAR Aggregations

At MSIT, performance & configuration data is captured by SCOM. We archive much of the data in a partitioned data warehouse table in SQL Server 2012 for reporting via an application called SONAR.  By definition, this is a primary use case for columnstore—report queries requiring aggregation over large numbers of rows.  New data is refreshed each night by an automated table partitioning mechanism—a best practices scenario for columnstore.

The Win

Compared to performance using classic indexing which resulted in the expected query plan selection including partition elimination vs. SQL Server 2012 nonclustered columnstore, query performance increased significantly.  Logical reads were reduced by over a factor of 50; both CPU & duration improved by factors of 20 or more.  Other than creating the columnstore index, no special modifications or tweaks to the app or databases schema were necessary to achieve the performance improvements.  Existing nonclustered indexes were rendered superfluous & were deleted, thus mitigating maintenance challenges such as defragging as well as conserving disk capacity.

Details

The table provides the raw data & summarizes the performance deltas.

Logical Reads
(8K pages)

CPU
(ms)

Durn
(ms)

Columnstore

160,323

20,360

9,786

Conventional Table & Indexes

9,053,423

549,608

193,903

Δ

x56

x27

x20

The charts provide additional perspective of this data.  "Conventional vs. Columnstore Metrics" document the raw data.  Note on this linear display the magnitude of the conventional index performance vs. columnstore. 

image

The “Metrics (Δ)” chart expresses these values as a ratio.

image

Summary

For DW, reports, & other BI workloads, columnstore often provides significant performance enhancements relative to conventional indexing.  I have documented here, the first in a series of reports on columnstore implementations, results from an initial implementation at MSIT in which logical reads were reduced by over a factor of 50; both CPU & duration improved by factors of 20 or more.  Subsequent features in this series document performance enhancements that are even more significant. 

TechDays 2014 – Session material

Last week in the Hague, I had the honor to present two very different sessions at the Dutch TechDays conference. A deep-dive session on internals of the SQL Server 2012 nonclustered columnstore index, and a very developer-oriented session on the bare basics of performance tuning. To my delight, both times the room was filled with very interested people, asking great questions and, I guess, enjoying my presentations. All sessions were recorded, and I have been told that in due time, all will be available…(read more)

Columnstore Preso to the Oregon SQL Server User Group

In the latest-&-greatest effort in my mission to deliver my Columnstore presentation to every geekly denizen of the SQL community on the Northleft Coast of the United States & beyond, I’ll be delivering this week to the Oregon SQL Server User Group.

Though Columnstore indexes were introduced in SQL Server 2012; they’re still largely unknown.  In 2012, some adoption blockers remained; yet Columnstore was nonetheless a game changer for many apps.  In SQL Server 2014, potential blockers have been largely removed & Columnstore is going to profoundly change the way we interact with our data.

Here’re the logistical details:

Title:  Columnstore Indexes in SQL Server 2014: Flipping the DW Faster Bit
Group:  Oregon SQL Server User Group
URL:  http://osql-d.org
Twitter:  @OSQLd

Location
: 1515 SW 5th Avenue, Suite 900, Portland, OR  97201  (downtown Portland in the ninth floor conference room of OHSU IT Group)
Time:  Wednesday evening April 9, 2014, 6:00p

Thanks to MVP Arnie Rowland (b|t) for the invitation as well as Vern Rabe & Paul Turley (b|t).  As I understand it, there’ll be several MVPs & MCMs there.  No pressure—and with luck, there’ll be no Stump-the-Chump.

I’m bringing a copy of the following for one lucky attendee: Professional SQL Server 2012 Internals and Troubleshooting by Christian Bolton (b|t), Rob Farley (b|t), Glenn Berry (b|t), Justin Langford (b|t), Gavin Payne (b|t), Amit Banerjee (b|t), with contributions & reviews from other big-time geeks such as Robert Davis (b|t) & Mike Anderson (b).

 


SQL Server 2014 Columnstore Indexes: The Big Deck

The History

Though Columnstore indexes were introduced in SQL Server 2012; they’re still largely unknown.  In 2012, some adoption blockers remained; yet Columnstore was nonetheless a game changer for many apps.  In SQL Server 2014, potential blockers have been largely removed & Columnstore is going to profoundly change the way we interact with our data.

I’ve been working with Columnstore Indexes since Denali alpha bits were available.  As SQL CAT Customer Lab PM, I hosted over a half-dozen customers in my lab proving out our builds, finding & entering bugs, & working directly with the product group & our customers to fix them. 

The Why

Why Columnstore?  If we’re looking for a subset of columns from one or a few rows,  given the right indexes, SQL Server has long been able to do a superlative job of providing an answer.  But if we’re asking a question which by design needs to hit lots of rows—reporting, aggregations, grouping, scans, DW workloads, etc., SQL Server has never had a good mechanism—until Columnstore.  Columnstore was a competitive necessity—our Sybase & Oracle customers needed a solution to satisfy what was heretofore a significant feature & performance deficit in SQL Server.  Our leadership & product team stepped up & provided a superb response.

The Presentation

I’ve delivered my Columnstore presentation over 20 times to audiences internal & external, small & large, remote & in-person, including the 2013 PASS Summit, two major Microsoft conferences (TechReady 17 & TechReady 18), & several PASS user groups (BI Virtual chapter, IndyPASS, Olympia, PNWSQL, Salt Lake City, Utah County, Denver, & Northern Colorado).

The deck has evolved significantly & includes a broad overview, architecture, best practices, & an amalgam of exciting success stories.  The purpose is to educate you & convince you that Columnstore is a compelling feature, to encourage you to experiment, & to help you determine whether Columnstore could justify upgrading to SQL Server 2014.

The Table of Contents

Here’s my deck’s ToC:

  • Overview
  • Architecture
  • SQL Server 2012 vs. new! improved! 2014
  • Building Columnstore Indexes
  • DDL
  • Resource Governor
  • Data Loading
  • Table Partitioning
  • Scenarios & Successes
    • Motricity
    • MSIT Sonar
    • DevCon Security
    • Windows Watson
    • MSIT Problem Management
  • Room for Improvement
  • Learnings & Best Practices
  • More Info

The Demos

I’ve included several demos, all of which are exceedingly simple & include step-by-step walkthroughs.

  • Conventional Indexes vs. Columnstore Perf
  • DDL
  • Resource Governor
  • Table Partitioning

Let me know if you have any questions.  In the meantime, enjoy!

Columnstore Presos to Denver and Northern Colorado SQL User Groups

I’m presenting soon to two Colorado user groups.

Topic:  Columnstore Indexes in SQL Server 2012 & 2014: Flipping the DW /faster bit

Monday 13 January 2014 @5:30p
Northern Colorado SQL Server Users Group
URL: http://nocodp.org :: http://nocodp.sqlpass.org
Twitter: #nocodp
Location: UNC Loveland Center at Centerra, 2915 Rocky Mountain Avenue, Loveland, CO 80538 (Breckenridge Conference Room 2nd floor)
Erik Disparti (@ErikDis|blog)

Thursday 16 January 2014 @5:30p
Denver SQL Server Users Group
URL:  http://www.denversql.org/ :: http://denver.sqlpass.org
Twitter: @denversql
Location: Denver Microsoft Offices, 7595 West Technology Way, Suite 400, Denver, CO 80237 (directions)
Mike Fal (@Mike_Fal|blog)

I’m giving away one copy at each preso of SQL matriarch Kalen Delaney et al’s latest-&-greatest hot-off-the-presses opus:
Microsoft SQL Server 2012 Internals
Kalen Delaney (blog|twitter), Bob Beauchemin (blog|twitter), Connor Cunningham, Jonathan Kehayias (blog|twitter), Paul Randal (blog|twitter), & Ben Nevarez (blog|twitter)

Microsoft-SQL-Server-2012-Internals

I was originally asked to speak last summer.  Linking my nascent passion for skiing with the timing resulted in a low latency response of something like, ‘Hey, let’s do this in six months, eh?’.  I followed up with Mike at the 2013 PASS Summit & along with Erik we carved the dates in stone.  I’m Denver-bound as I type this & am extremely eager to combine my passion for skiing along with my eagerness to evangelize one of the many new! &/or improved! features that SQL Server has to offer.  I’ll provide an update on both my experience on the slopes & with the groups. 

In the meantime, as I write this, I’m looking out over the slopes of Vail being greeted by the winter’s dawn. 

It’s going to be a great day—& a great week!  I hope you can join us!