Ready for the Future

Local Data Visualization

In addition to SQL Server on Linux, Microsoft is finally implementing another feature in SQL Server 2017 that users have been waiting for since the release of SQL Server 2016: the ability to provide Power BI dashboards on premises. This functionality is not freely available for every SQL Server installation, however. You have two ways to obtain a license for the separately installed Report Server for Power BI:

• Subscribe to the Power BI Premium service in Azure. In addition to improved performance and a contingent of Power BI reports, which you can make available online to any user, you also receive a license for local use.
• SQL Server 2017 Enterprise licenses with active SA entitles you to deploy Power BI on premises. If you are considering using your Report Server to put Power BI dashboards on the Internet, be sure to talk to your licensing department first.

A free trial version of the Report Server is available on the Power BI website [6]. By the way, your data does not have to be in an SQL Server 2017 database instance for you to benefit from Power BI. Everything starting with SQL Server 2008 is supported. Power BI Desktop remains free of charge. You can use it to develop your dashboards and publish them in the Power BI cloud or on a local report server.

Intelligent Queries

The best features are of no use if a database server cannot perform its main task efficiently: querying data from the database and making client applications or other SQL Server services available. In earlier versions, the database engine followed a rigid procedure:

1. The request was submitted.

2. The Query Optimizer made assumptions about how many rows were to be processed in each table, view, and sub-query along with their memory requirements. It drew up several implementation plans, which were given a "cost factor" on the basis of these assumptions.

3. The plan with the lowest cost factor was submitted for execution and processed rigidly.

If the assumptions of the Query Optimizer deviated greatly from the actual values, execution became inefficient. If the assumption was fewer lines or a lower volume of data sets, the server had to transfer data from RAM to the hard drive because the RAM requirement could not be satisfied. If the estimate was significantly greater than actual demand, resources were not available to other processes, whose performance might have suffered as a result.

With SQL Server 2017, the Query Optimizer becomes smarter and employs three new query optimization techniques that adjust execution plans based on the values achieved, some of which are determined while the query is still being processed. The following functions are used:

1. Memory Grant Feedback measures the amount of memory actually needed to execute the query and corrects the cached plan accordingly. This technique realizes its potential if the same query is repeated frequently. If the query is parameterized, the memory requirements can vary extremely from version to version. In this case, an extended event (XE) is provided, which switches off the function for the affected query.

2. Batch Mode Adaptive Joins decide on the join technique to be used (nested loop versus hash) only after the first results have been retrieved from the database and the resulting number of rows is not merely estimated but is precisely known. Adaptive joins are the perfect choice if the same query produces a strongly fluctuating number of rows.

3. Interleaved execution for multi-statement table valued functions (MSTVFs) changes the procedure outlined above in that the execution of special complex queries does not follow optimization rigidly, but begins as part of the optimization. MSTVFs are executed right at the beginning, so precise data regarding number and size are available for the results. Only then is the optimized execution plan for the rest of the query calculated and submitted for execution.

All execution plans prepared by adaptive optimization apply the same storage logic as the conventional plans. They are calculated the first time the query is executed and remain in the cache until they become obsolete or until the cache is cleared by restarting the instance. Therefore, it is usually not a good idea to start SQL instances on a speculative basis. Failover of the database within a cluster also erases the cache content. To use the new query optimization technique, your database must be at compatibility level 140 (SQL Server 2017). The functionality of Adaptive Query Processing is described in detail in the online SQL docs [7].

Fit for Big Data and IoT

Since its introduction in 1989, Microsoft SQL Server has been a relational database (RDB). However, the world of big data and IoT no longer works relationally: Information, and thus also data, is created simultaneously in a multitude of networked systems and often includes traces of these network topologies and hierarchies. The attempt to map such multi-networked data relationally always results in a major processing overhead and limited functionality. Therefore, classic RDBs, such as SQL Server, have lost their supremacy in the age of big data and have had to give way to special databases such as Hadoop-based TitanDB.

Microsoft's SQL product team is now striking back and has given the database engine the ability to process graph data directly. Two new table types have been introduced for this purpose: node and edge. Edges can have properties (metadata) or simply establish a directional connection between two nodes. The newly introduced MATCH clause in Transact-SQL (T-SQL) is used to search graphs. It contains conditions in the form of

node1-(edge)->node2

(all nodes with direct connection to node1) or more complex queries such as

node1-(edge1)->nodeX<-(edge2)-node2

(pairs of nodes, each connected to the same node). The names used here are aliases for the same node or edge table, so the first query could look like:

SELECT node2.first name FROM node node1, connection, node2 WHERE MATCH(node1-(connection)->node2) AND node1.place of residence='Berlin'

(Get all first names of nodes that have a connection to a node from Berlin.) The MATCH syntax is documented in the SQL docs [8]. How the performance of the SQL graph tables behaves compared with the original graph databases remains to be seen.