I’m sure that many of you are familiar with the Column Store index feature that was launched with SQL 2012. Using Microsoft’s In-memory technology, the Column Store index delivered a massive performance boost to data warehouse queries. However , I’ve found that many people were put off the feature because of the fact that the Fact table becomes read only, once you implement Column Store.
Well , you’ve probably heard the big news ( I’m writing this post months later than I originally wanted to ) , and for those of you who haven’t, the Column Store feature in SQL 2014 is update-able. That’s right , no more read-only fact table. However, that isn’t the reason for this post – I have something else here that might interest you.
Space, the final frontier….
Firstly , you actually have 2 options with SQL 2014 – You can create the Non-Clustered Column Store as per 2012 ( which is NOT update-able ). Alternatively , you can create the new Clustered Column Store which IS update-able. The Clustered Column Store has some other interesting features :
1) It becomes the primary storage mechanism for the table.
2) It eliminates the need for other covering indexes.
3) It dramatically reduces the storage requirements for the table – and that’s a fact.
Now its point 3 that I want to talk more about. In my test DW that I built for demo purposes, I have a fact table with about 20 million rows. Lets take a look at the storage statistics of that table.
This table is currently taking up about 2.3 GB of space. We could apply Page Compression to this table ( which I believed has also been improved in 2014 ) , and we would get the following result.
Not bad , a reduction of the space used to less than 25% of the original size.However, we haven’t built any indexes yet to boost performance. Prior to applying the Page compression , I created a copy of the table , called FactSales2. Let’s apply the Non Clustered Column Store index to that table , to give us that performance boost , and see what happens to the storage.
The storage space of the table increases , and we can see that there is a 242MB overhead for the index. Now we could implement page compression and then the Non Clustered Column Store index , but then your table would still only be read-only. In addition to that , you would probably need to implement more indexes which takes up more space. In SQL 2014 , we have a better solution , which is, implement the Clustered Column Store Index. What about the Page Compression ? …..
….. it simply isn’t needed. The Clustered Column Store delivers better compression than Page Compression , reducing the size of the Table and the Index together to a mere 200MB. Technically , the index is the table. This is astonishingly less than a tenth of the storage space required when compared to the regular table with the Non-Clustered Column Store. Is the performance the same ? I’ve written a typical Data Warehouse reporting query with joins to the dimensions , and executed it on all 4 table types. The results are as follows :
|Type||Avg. Execution Time||Notes|
|Regular Table||32 Seconds||No Indexes|
|Regular Table with Page Compression||23 Seconds||No Indexes|
|Table with Non Clustered Column Store||5 Seconds||No Compression ( 2.3 GB Table ), Read Only|
|Clustered Column Store Table||5 Seconds||Table is 200MB !|
The only thing left to say is that, if you had reservations about using the Non-Clustered Column Store previously, you would be mad not to use it on Fact tables in SQL 2014. The benefits with regards to storage and performance are astounding. It remains to be seen if there is any impact to insert performance , but that’s a topic for another day.
Lots of people have been asking me about Tech Ed South Africa recently. The official comms was that there will not be an event this year , but Microsoft will be hosting other smaller events throughout the year. I refer you to the local Tech Ed page here :
If I hear of any new events I’ll mention it here on my blog.
In my last post , I spoke about the free Azure credits that every MSDN subscriber gets , and detailed the steps to activate them.
Well , the Azure channel on Youtube now has a video which talks about this.
I thought I’d share a Windows 8 tip with you guys. When you’re using a Modern Application ( like Windows Mail ) , and you click on a Hyperlink , it normally opens it up with Modern Internet Explorer ( like in the image below ).
This is fine when you’re on a tablet ( or in touch mode ). It’s not so great if you’re using a mouse and keyboard. However, there is a setting to change this behaviour.
1) Launch the Desktop Internet Explorer
2) Go to [Tools] , [Internet Options] and then [Programs]
3) Under [Choose how you open links], select [Always in Internet Explorer on the Desktop]
When you go back into a Modern Application and click on a link , it will take you to the Desktop Internet Explorer.
This is the big one !! SQL 2014 , with the in-memory OLTP capability, has officially launched as of 1 April 2014. You’ll find everything that you need to know at the link below :
So far , I’ve been experimenting with the in-memory tables , and I’ve definitely seen some big performance improvements. I’ll do an upcoming post detailing how you create these tables.
Before that , I want to talk about dev and test environments. Many of you will be wanting to try out SQL 2014 , but perhaps don’t have the space available to spin up a VM. What you can do in this situation, if you have an MSDN subscription, is spin up a VM in Azure at no cost, and try out SQL 2014 there. To do so :
1) Log into the main MSDN page ( http://www.MSDN.com )
2) Click on “Cloud”
3) Under “Start your free trial” click on sign up
4) Scroll down to the bottom of that page. You’ll see a heading called “MSDN Subscriber?”. Click on “Activate Now”
5) You are basically entitled to $150 per month of free Azure services.
Once you have sorted that out , you’ll see that SQL 2014 is already available in Azure. There are preconfigured VM templates with various options.
It takes about 5 minutes to spin up a new Virtual Machine , with the OS and SQL 2014 already installed !! Once that’s done, you can remote desktop into that and work on it as normal.
Bet you didn’t see that one coming ! While some people are still getting to grips with the excellent SQL 2012 , Microsoft has officially announced SQL 2014 at Tech Ed North America.
Your first stop should be Microsofts website , where you can sign up to be notified once the trial version is available. There are also 3 PDF documents at the bottom of that page that I highly recommend downloading.
Next , what exactly was announced in terms of features ? Just some of them :
1) Heckaton ! I’ve blogged about this before. It’s basically in-memory technology to give SQL Server OLTP applications a massive performance boost.
2) Writeable Column Store Indexes !! – A popular feature , and now your fact tables won’t be read-only once you create the index.
3) SSD Caching – extend memory by caching on an SSD. Also a performance boosting enhancement.
4) Extended Online Operations – more operations supported without taking database offline , and this extends uptime.
I’m sure that there are a whole lot more that we’ll learn about in the upcoming months. In the meantime , I recommend that you download those whitepapers at the Microsoft site and get reading !!
Like many enthusiasts, I’m very excited about Intel’s upcoming Haswell chips, and I’m really hoping that Intel can deliver on the hype. If you want more information on this new architecture, there’s nothing better than the ( as usual ) in-depth article at Anandtech.
Now while any new processor launch gets people excited in terms of more horsepower being available , here we also expect better integrated graphics performance and much reduced power consumption. And it’s that last aspect, together with the timing of this launch that makes this a significant milestone on the computing timeline, in my opinion.
You see, we’ve been hearing recently about how we are now in this “post-pc” era, and how tablets and phones are the computers of the 2010’s. Its something we certainly see , people walking around with massive smart phones and, in meetings, everyone holding an iPad.
But what is also clear to me is that these devices haven’t replaced PC’s for most people. Maybe it has with college students, but not in the enterprise. They are simply complimentary devices.
Enter Windows 8. When launched, I really thought that Windows 8 tablets would change all this. I felt that tablets like Surface would be to notebooks what notebooks were to desktops 10 years ago. We didn’t see that happen in a big way , because the launch of Windows 8 was only the first step.The second important step that I feel will drive this will be the launch of Haswell. Why ?
How does 5 times the power efficiency of current chips sound ?
I’ve been wanting to write this blog post for a long time , but an article from Seeking Alpha today spurred me into action.
“The advantage of the Windows 8 based tablets and hybrids was supposedly that it can run legacy software, but the disadvantage was that there was an awkward trade-off between power and energy and cooling needs. The user could either buy a rather underpowered Atom CPU based tablet/hybrid that was as sleek and energy efficient as most of the competition, or a much less energy efficient iCore based tablet/hybrid that is thick, heavy, doesn’t last all day but is fully powered in terms of processor needs.
With the new Haswell processors, that trade-off has become distinctly reduced, perhaps it even disappears completely.”
As I said , this is the next milestone and I can’t wait. Of course , we will then have the improved Windows 8.1 released after that and we’ll then be truly into the “PC-Plus” era…..