Tag Archives: SQLBI

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

AdventureWorks 2014 Sample Databases Are Now Available

 

Where in the World is AdventureWorks?

Recently, SQL Community feedback from twitter prompted me to look in vain for SQL Server 2014 versions of the AdventureWorks sample databases we’ve all grown to know & love.

I searched Codeplex, then used the bing & even the google in an effort to locate them, yet all I could find were samples on different sites highlighting specific technologies, an incomplete collection inconsistent with the experience we users had learned to expect.  I began pinging internally & learned that an update to AdventureWorks wasn’t even on the road map

Fortunately, SQL Marketing manager Luis Daniel Soto Maldonado (t) lent a sympathetic ear & got the update ball rolling; his direct report Darmodi Komo recently announced the release of the shiny new sample databases for OLTP, DW, Tabular, and Multidimensional models to supplement the extant In-Memory OLTP sample DB. 

What Success Looks Like

In my correspondence with the team, here’s how I defined success:

1. Sample AdventureWorks DBs hosted on Codeplex showcasing SQL Server 2014’s latest-&-greatest features, including: 

  • In-Memory OLTP (aka Hekaton)
  • Clustered Columnstore
  • Online Operations
  • Resource Governor IO

2. Where it makes sense to do so, consolidate the DBs (e.g., showcasing Columnstore likely involves a separate DW DB)

3. Documentation to support experimenting with these features

As Microsoft Senior SDE Bonnie Feinberg (b) stated, “I think it would be great to see an AdventureWorks for SQL 2014.  It would be super helpful for third-party book authors and trainers.  It also provides a common way to share examples in blog posts and forum discussions, for example.” 

Exactly.  We’ve established a rich & robust tradition of sample databases on Codeplex.  This is what our community & our customers expect.  The prompt response achieves what we all aim to do, i.e., manifests the Service Design Engineering mantra of “delighting the customer”.  Kudos to Luis’s team in SQL Server Marketing & Kevin Liu’s team in SQL Server Engineering for doing so.

Download AdventureWorks 2014

Download your copies of SQL Server 2014 AdventureWorks sample databases here.

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. 

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).

 


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!