OK , so SQL 2016 is here , and I’m sure we’re all already playing with the new features.
I’ve decided to start learning R, and did a fresh installation of SQL 2016 with R Services installed.
While attempting to run my “Hello World” script I encountered the error –
“Msg 39011, Level 16, State 1, Line 1
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.”
OK, so the first thing you have to do is to enable SQL to run external scripts. Run
Exec sp_configure ‘external scripts enabled’, 1
Reconfigure with override
However , make sure that the new “Launchpad” service is running – I simply had to start this to solve the error on my VM.
OK , that’s a click-bait headline….. but its not totally untrue.
When I installed SQL 2016 for the first time on a VM, the install initially failed the list of checks due to 2 items. One was an update for Windows Server that wasn’t installed , but the other was an update for Oracle Java ( JRE ). Now this was puzzling to me – since when does SQL Server require Java ?
The answer lies in one of SQL 2016s most interesting features – Polybase.
Polybase is Microsoft’s “SQL-over-Hadoop” solution , a layer that allows you to write SQL and query relational and non-relational data. Originally launching with the APS appliance, the inclusion in 2016 is a milestone. So why Java ? Remember that MapReduce scripts are typically written in Java.
Sql 2016 allows you to query data from a connected Hadoop or HD Insight instance , a true sign of the Big Data times that we live in.
After a long wait , we finally hap SAP Certification for SQL 2014. The details can be found at Juergan Thomas’ blog here
As expected , there is support for the new 2014 ColumnStore feature – particularly useful for SAP BW scenarios.
Amongst the other features , I see that Azure also plays a big role – In fact Azure DR for SAP Netweaver seems to be a hot topic. You can find a whitepaper on this here.
Now , there was something missing in the announcement – Support for SAP Netweaver using SQL Server In-Memory OLTP. Apparently , it has to do with the fact that IMOLTP uses Snapshot Transaction Isolation Level , while SAP Netweaver normally uses Read Committed.
Disappointing – but I’m sure that we’ll have some good news on this soon !
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.