Wow, where does the time go? Hope I see every reader there in Louisville this weekend for a lovely time. Say you live in Alaska and it would be too far to go in one day? Hey, we all have our problems ( and don’t tell anyone, but I am going to try to finish up video versions of these sessions by the end of the year.. shh .) I will be doing my session on Database Design Fundamentals and How to Write a DML Trigger, both sessions I have done quite often of late, and I really like how they work now. I…(read more)
Thursday night, at 6:00 (or so) I will be speaking in Richmond ( http://richmondsql.org/cs2007/ ), talking about How to Implement a Hierarchy using SQL Server. The abstract is: One of the most common structures you will come across in the real world is a hierarchy (either a single parent “tree” or a multi-parent “graph”). Many systems will implement the obvious examples, such as a corporate managerial structure or a bill of materials. It turns out that almost any many-to-many relationship can be…(read more)
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
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.
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.
Classic vs. columnstore before-&-after metrics are impressive.
SSRS via SSAS
10 – 12 seconds
(300 – 420 seconds)
1 – 2 seconds
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.
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.
- 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.
- 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.
- 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.”
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.
One of my favorite locations to speak or attend is when Richmond has a SQL Saturday . (though if you are an organizer of another SQL Saturday’s I have submitted to, note that I said “one of my favorites” :)). This will be the third time I go to Richmond. I like it for several reasons: The people – I have coworkers coming up from Virginia Beach to attend, and from Maryland, and our leader lives in Richmond; I have a cowriter who is also speaking ( Jessica Moss ), and other good friends who are amongst…(read more)
Update! 10 more books added to the cadre from my friends at Red-Gate. With less than a week to go, I am starting to pack up for Charlotte and PASS 2013. I love that it is in Charlotte this year so I can drive and bring along some goodies to give away. Books and toys mostly, a variety of which were chosen rather specifically for some manner of symbolism with a tie in to database design for the most part. (Okay, symbolism is perhaps a bit of a stretch, but I have tied everything, even the goofy stuff,…(read more)
It’s PASS Election time, and this year, there are no serving board members on the ballot.
Two years ago, Adam Jorgensen, Denise McInerney and I were elected to the PASS board, serving two-year terms. Since then, Adam and Denise have both been elected as Vice-Presidents of PASS, and will remain on the board in that role. Of the previous VPs, Douglas McDowell is stepping off the board, while Tom LaRock has become President (the current President, Bill Graziano, becoming Immediate-Past President and the current IPP, Rushabh Mehta, stepping off the board).
So instead of the vote being to replace or re-elect Adam, Denise and me, we see Douglas and Rushabh leaving the board.
This leaves the question of me. I could run again this year, but instead have decided to take a break from the board.
Being on the PASS board is a great way to serve the SQL community, and I’m all about serving – it’s a large part of what drives me. In fact, I often think I’m better at serving than pursuing my own vision. Sometimes I think I’d make a great case study for psychologists. Other times, I think I’d they’d just roll their eyes and say “No, we see people like you all the time, you’re a classic <insert term here>.”
I was asked to be on the board just over two years ago, when PASS gathered people from the UK, Nordic and Australian regions to discuss the globalisation of PASS. Greg Low and I were there from Australia, and when JRJ, Raoul Ilyes and I were asked to be on the board, I accepted. All three of us did. Because Raoul was ineligible to become a director (he’d recently taken a job with SolidQ, and PASS has rules about having more than two board members from the same company), we were put in as board advisors. As I was already involved, I ran for election to be a director at the end of the year (two years ago). JRJ was chosen by the board to take over Andy Warren’s position when he left the board mid-term. And so the story of PASS globalisation continued with JRJ and me as directors.
Being a board member from Australia has been tough, but if it was that alone, I’d make it work. I’ve got up plenty of early mornings to be in meetings. But there’s more. I run a business (LobsterPot Solutions) out of Adelaide, which is a tough market. We also have staff in Melbourne and now Canberra (Julie’s moved there) too, and take on clients all around Australia and overseas. That doesn’t mean I travel a lot, but it does take time and emotional investment.
I should travel more than I do. In fact, that’s one of the reasons why I’m leaving the board – my old back injury. It doesn’t normally affect me much, but this year, it has done more than I would’ve liked. I trained for and completed a marathon earlier in the year. I ran because I could. I’d had nine years of not being able to run, and then after working with my physiotherapist to get it back, wanted to see how far I could push it. It was hard, but I ran a marathon. I have a medal to prove it. It was hard though, and it took a lot out of me. Every injury during training was exacerbated by my back condition, and I ended up not being able to fly to the other board meetings this year. I missed the first one because it was the first week of the school year and I chose to be here for my family, but the other two were because my back simply wasn’t up to it. I attended via Skype instead, but it wasn’t the same.
Essentially, I don’t feel that I’ve been able to be as effective this year on the board as what I’d like. I see my role on the board being to stand up for what is right for the community, and being an enabler. My portfolio this year has been SQLSaturday, and the focus I’ve taken has been in enabling Karla and Niko to do what they do more effectively, and to try to make life easier for the SQLSaturday organisers, attendees and sponsors. It’s been a year of change at PASS in many ways though, and with my energy levels being lower this year than I’d’ve liked, I don’t know how well I’ve done that. I have high standards, of course.
I would happily remain on the board as an advisor, or take on a different role within PASS (although Australia already has two Regional Mentors), but I’m also really thrilled that the people that are running for the board this year are such high quality. There are a few PASSion award winners in there, and almost everyone on the list has been involved in growing the SQL community in significant ways. I would love to see Allen get back on the board, and all the others will have a positive effect too. I could happily write recommendations for every one of them! I’m pleased I’m not running against these people, because I feel PASS deserves to have these people on the board more than it deserves to have me continue. I might run again in the future, but am also happy to serve behind the scenes too.
So instead, the PASS membership gets to vote three new people onto the board! Exciting times.
Well, here we go again. I am making my final preparations again for my “How to Design a Relational Database” pre-conference seminar coming up at PASS. If you want to know what the “official” abstract is, you can find it here and if you are already convinced and have credit card in hand, click here and register before the price goes up more. If you have already registered but not for my (or any other) pre-con, you can always add on the precon! So you can read the abstract, but what to expect? First…(read more)
Every September for the past 12 years, not only does summer slowly come to an end, but it is the time that final preparations for the SQLPASS Summit are being made by just a ton of people. I have always focused on speaker preparations myself for the most part, but there are hundreds of other folks furiously prepping to make the trip up to Seattle.. er.. I mean, Charlotte, NC (still seems weird after the long run in Seattle!) If you have never been to the Summit, I would highly recommend it. You will…(read more)
Today’s interviewee is someone who is quite a prolific writer, with her most recent book being Microsoft SQL Server 2012 Reporting Services, having been released in May of 2013; and if you check out her Linkedin page ( http://www.linkedin.com/in/staciamisner ), you will find 12 other books she has either authored or co-authored. And that is just the start… Stacia has a blog at http://blog.datainspirations.com and it is amazing that it has any entries at all considering how busy she is speaking and…(read more)
In July and October, I will be doing my "How To Design a Relational Database" full day conference in two places. First on July 26 for the East Iowa SQL Saturday , and then for the big daddy SQLPASS Summit in Charlotte, NC on October 14. You can see the entire abstract here on the SQL PASS site. It is essentially the same concept as last year, but this year I am making a few big changes to really give the people what they have desired (and am truly glad to have a swing at it several months…(read more)