Friday, October 28, 2016

Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection



There were a bunch of cool announcement at the SQL QA and chalk talk sessions at the PASS Summit



Here are the people presenting that session(not all pictured here)  Lindsey Allen  Jakub Szymaszek  Jovan Popovic  Kevin Farlee  Tomas Talius  Sunil Agarwal  Joseph Sack and Tomas Talius

Resumable Index Rebuilds

For now this will be row-store only, this will not be available for columnstore indexes for the coming release. I assume it will work like showcontig back in the day. I need nore info and have the following questions....

What happens if you stop the index rebuild and you add more data?
Are statistics update only at the end, when everything is done?
Is this just a reorg in disguise?
Is this online or offline only?


SQL Graph

Think about this like Neo4J but then in SQL Server and probably better


SQL Graph adds graph processing capabilities to SQL Server, which will help you link different pieces of connected data to help gather powerful insights and increase operational agility. Graphs are well suited for applications where relationships are important, such as fraud detection, risk management, social networks, recommendation engines, predictive analysis, dependence analysis, IoT suites, etc.
Initially, SQL Server will support CRUD graph operations and multi-hop graph navigation, and the following functionality will be available in the private preview:
Create graph objects, that is, nodes to represent entities and edges to represent relationships between any 2 given nodes. Both Nodes and Edges can have properties associated to them.
SQL language extensions to support join free, pattern matching queries for multi-hop navigation.


If you like to signup for the private preview...go here: http://aka.ms/GraphPreview



Adaptive Query Plans
I was sitting in this session, and this is more or less what I remembered

This is for starters about Multi-statement Table Valued Functions (MSTVF). The legacy cardinality estimator will use 1,  in 2014 the new new cardinality estimator will use the number 100. Both are of course wrong. The idea with Adaptive Query Plans is when the tree is build, grab the correct value from the MSTVF, inject that into the plan and pass it down the pipeline

Adjust the plan..consecutive execution, if you use too much memory and start spilling, this will be adjusted and you will stop spilling
Adaptive joins, the idea is if they think that it is appropriate that they can have a nested loop operation but there might be a chance that the estimate is wrong, they will revert to a hash join otherwise they will stick with their original nested loop join

The plan is changed in flight, there will be an intra-plan change, same thing with the adaptive join.

For starters, they will just address MSTVF, other stuff will follow later


If you like to signup for the private preview...go here: http:/aka.ms/adaptiveqppreview


SQL Server  vNext CTP1 coming next month
CTP1 for SQL Server vNext will be available next month, this is both for the Linux as well as the Windows version. This is available now, here is the link https://www.microsoft.com/evalcenter/evaluate-sql-server-vnext-ctp

See also: SQL Server 2016 SP1 released, SQL Server vNext available for download

SQL Server Standard Edition announcement on November 15th

There will be a big announcement about SQL Server Standard Edition on November 15th....  mmmm maybe the ability to use more memory? We will have to wait and see what will be announced

SQL Injection detection
The way this will work is that if they detect a query that has been SQL injected with for example DROP TABLE, they will block it and sent an email to a DL or group that you have defined explaining that the statement has been blocked



Python coming to SQL Server
It looks like in addition to R, Python will be coming to SQL Server as well




P.S.

If you want to read about my time at SQL Pass 2016, read these posts


SQL Pass Summit 2016... day 2
SQL Pass Summit 2016... day 3
SQL Pass Summit 2016... day 4
SQL Pass Summit 2016... day 5


You might be thinking, where is day 1?  The day 1 posts is mostly about Seattle, if you are interested in that one, here it is:  SQL Pass Summit 2016... day 1.... just looking around

2 comments:

Jon Lunn said...

Thanks for the details from the PASS conference. For python coming SQL server will it just be for analytics like R or more integrated?

Denis Gobo said...

The only thing we managed to get out of them is that python would be coming. Not sure if right away in the next version. I assume it would be like R but I have no details on what the plans are.....