Faster Data for Tableau: 3 Killer Features in SQL Server 2014

Data

Faster Data for Tableau: 3 Killer Features in SQL Server 2014

SQL Tuesday

I’m a huge fan of Microsoft SQL Server. It’s a very user-friendly database with a wonderful tool stack, making it a perfect fit for many data warehouses that feed data to your Tableau users. The latest release, SQL Server 2014, gives us even more things to be excited about. Though there are a bunch of features, here are a few you should be looking at for the future of your data warehouse.

Clustered Columnstore Indexes

This is the new hotness. Non-Clustered Columnstore Indexes were introduced in 2012 and are absolutely fantastic. I found them to be extremely fast and effective, blowing away the speed I thought SQL Server could reach. That said, they had one huge problem: any time you updated the table you had to disable and rebuild the index. There were workarounds to this, including building a separate “delta” table, but…

Sweet Brown

SQL Server 2014 solves this with the introduction of Clustered Column Store indexes. These indexes are fully updatable, letting you have your cake and eat it too. This greatly expands the scenarios in which you can deploy Columnstore indexes effectively. Of course, the downside is that this is only available in the Enterprise Edition. If you’re running Enterprise, you should be considering how to leverage this in your data warehouse.

Cache to SSD

I love SSDs. You want better performance? Great, load your server up with them. Commodity SSDs are the way of the future, but sometimes we need to move in baby-steps. If you’re not able to replace your SAN quite yet, SQL Server 2014 still gives us a wonderful option to leverage the speed of SSDs.

We can now use a local SSD (or SSD array) as a cache to extend memory. This is great for read-heavy workloads, especially when your data is too big to fit into main memory. It’ll give your SQL Server fast, local access to your data, providing a huge performance benefit. The only caveat here is that you should probably max out your RAM before considering it.

More RAM in Standard Edition

Speaking of RAM, you can, and should, have more of it. SQL Server 2012 Standard Edition has a new max of 128 GB of RAM. Even if you’re running Standard Edition and don’t have access to the fancy new Clustered Columnstore indexes, you can still realize a ton of benefit by utilizing this option. It is the single best thing you can do to speed up your performance.

Skimping on your RAM is starving your database of needed resources and making it rely on disk, which is several orders of magnitude slower. The decision is even easier when you consider that RAM is cheaper and often more effective than what you’d spend on people optimizing your database. Save yourself the headache and buy more. You’ll get more bang for your buck and look like a genius in the process.  

Scotty

Loose Ends

There are lots of other features out there, not to mention general query performance improvements, but one feature I wanted to address is Hekaton. Hekaton is a specialized, in-memory component that interacts with Tableau.

If you’re reading this blog post as a BI Developer, this is likely not for you.

Though being in-memory leads you to believe it would be fast, it’s optimized for high-concurrency loads on transactional systems. For the kind of workloads Tableau puts on your server, you’ll have a lot more success with Columnstore indexes.

With these three things in mind, you should be flying through your data for years to come. If you run into turbulence, don’t worry – we’ve got your back.

More About the Author

Ben Bausili

Global Experience Practice Lead
Better, Faster, Stronger: The Power of the Cloud in BI Two weeks ago, my fellow content connoisseur Andrea Avey sat down with InterWorks IT gurus Scott Matlock and Tim Rhymer to talk about ...
Analytics Explained: Modeling Our World Models are amazing. Built upon the understanding we gained in type 2 analytics, models provide an amazing tool that we can pass to ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072