It’s Wednesday morning, October 16, and Quentin Clark, Vice President of Data Platform, is on stage here at the PASS Global Summit in Charlotte, NC, giving the keynote presentation. Highlights include:
SQL Server 2014 CTP2, the public preview, is released today
This release of SQL Server enables “Hekaton”, in-memory transactional capabilities that will speed up certain transactions by orders of magnitude (up to 100x in some cases.) This enhances the in-memory column store index features that were introduced in SQL Server 2012. According to Quentin SQL Server is light years ahead of any competitor using in-memory database technology.
Hekaton not only enables star schema queries to run very fast using read-only column store indexes but data and indexes can be rebuilt and updated in real time.
Of course, I’ll be paying close attention to announcements about BI capabilities that are not necessary encompassed in the SQL Server 2014 package. I’ll posy those announcements here.
Quentin said: “BI is not only for the High Priests of IT but should be for the people running the business”
Thursday Keynote
Current President, Bill Graziano, announced the departure of PASS Directors; Douglas McDowel, Rob Farley and past President, Rushabh Mehta. This was a bittersweet moment for these three respected leaders who have all been serving PASS for about 8 years. Tom LaRock, the incoming PASS President, introduced David DeWitt; Techical Fellow from Microsoft.
David DeWitt explains the architecture of Hekaton…
Of course there are many technical details and complexity to the engine but the bottom line is that Hekaton runs completely without locks (or latches). In SQL Server 2014, there are three different database storage and integrated query engines: relational, column store and Hekaton/InMemory. A Hekaton table gets loaded into memory. Multiple operations performed on the same row create multiple version copies of the same row, rather than placing locks on the rows. The query engine reads the last row using a time stamp. The garbage collector eventually removes outdated records. Typical performance improvement is 20x.
Another key feature of Hekaton is the ability to compile stored procedures used on InMemory tables into native code. There are three modes: Interpreted, Interop & Native. Classic stored procs interpret procedure definitions. Native mode procedures can have the instruction set reduced by about 3x and run much faster. There are data type and structure restrictions (no XML).
Other BI posts? This post is as much relevant to BI as I can imagine. Isn’t that where you will see most use of the new in-memory features?
With new T-SQL functions introduced in 2012, Columns tore indexes, Always on, and now compiled SPs, all with server level administration capabilities, who’s gone need BISM Tabular or MOLAP except in direct-query or ROLAP mode?
OK, these last 2 add functionality but also have limitations you won’t see on the database engine.
Artie
Artie
I see your point about performance and feature improvements to the T-SQL query and relational engine. I had some of the same thoughts during the keynote presentation at the PASS Summit by Dr. DeWitt. However, true data analysis often relies on semantic models and analytic query languages like MDX and DAX. Most analytic reporting tools are designed only to connect and query Analysis Services and not relational data sources. For example, Power View can be used with SSAS Tabular or Multidimensional but not with the relational engine. Even if they perform faster, normalized databases can still be too complicated for business users to browse for self-service analysis. I agree with you the some of the gaps that SSAS has filled in the past have now been bridged with new features like column store indexes and in-memory tables; but I don’t see this replacing semantic models or slowing the adoption of BI technologies.