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…

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.  

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.