download presentation - SQL Nexus

1 .Joe Sack, Principal Program Manager, Microsoft Database Engine Enhancements in SQL Server 2017

2 .Microsoft Data Platform Landscape Business intelligence Advanced Analytics & AI DATA INSIGHTS DATA MANAGEMENT Big data processing Data warehousing Operational data Power BI Azure Machine Learning Azure Stream Analytics Azure Cognitive Services SQL Server Reporting Services SQL Server Analysis Services, R Services Azure HDInsight Azure SQL Data Warehouse Azure Data Lake Azure Document DB Azure SQL Database SQL Server SQL Server Apache Hadoop ON-PREM CLOUD

3 .Microsoft Data Platform Landscape Business intelligence Advanced Analytics & AI DATA INSIGHTS DATA MANAGEMENT Big data processing Data warehousing Operational data Power BI Azure Machine Learning Azure Stream Analytics Azure Cognitive Services SQL Server Reporting Services SQL Server Analysis Services, R Services Azure HDInsight Azure SQL Data Warehouse Azure Data Lake Azure Document DB Azure SQL Database SQL Server SQL Server Apache Hadoop ON-PREM CLOUD

4 .Most secure database over the last 7 years Highest performing data warehouse In-database Advanced Analytics Security vulnerabilities reported by NIST An industry leader four years in a row SQL Server 2016 Everything Built-in

5 .Most secure database over the last 7 years Highest performing data warehouse In-database Advanced Analytics #1 in 30TB, 10TB, 1TB TPC-H non-clustered results An industry leader four years in a row #1 SQL Server 2016 Everything Built-in

6 .Most secure database over the last 7 years Highest performing data warehouse In-database Advanced Analytics An industry leader four years in a row SQL Server 2016 Everything Built-in R + in-memory at massive scale 1 Million PREDICTIONS/SEC

7 .SQL Server 2017 Run anywhere and build apps using the language of your choice Analyze complex relationships with support for graph objects & queries Advanced Machine Learning with R & Python Unparalleled performance with adaptive query processing Industry-leading, most secure data platform with built-in intelligence for all your data SQL Server 2017

8 .SQL Server 2017 Themes

9 .Adaptability In SQL server 2017

10 .The middle-of-the-night call You’re on call for supporting the data tier of a mission-critical SQL Server instance There has been a jump in CPU utilization on a key server, and one of the critical stored procedure calls is now running (much) more slowly then it used to? You’ve been asked to mitigate the issue and then determine the root cause

11 .What does Query Store show?

12 .What does Query Store show? Good Regression

13 .SQL Server 2017: Automatic tuning We can now detect and correct these scenarios without manual intervention Recommended actions surfaced via sys.dm_db_tuning_recommendations We can now automatically switch to the last known good plan whenever the regression is detected

14 .Resumable index operations Adapt to outages without losing maintenance work Resume an index rebuild operation after an unexpected failure Pause and resume an index build operation at any time, for example, to temporarily free up systems resources in order to execute a high priority task Supports rebuilding large indexes online without requiring significant log space, allowing log truncation while rebuild operation is running

15 .Adaptive Query Processing Feature Family

16 .Interleaved Execution Problem: Multi-statement table valued functions (MSTVFs) are treated as a black box by QP and we use a fixed optimization guess Interleaved Execution will materialize row counts for multi-statement table valued functions (MSTVFs) Downstream operations will benefit from the corrected MSTVF cardinality estimate Pre 2017 2017+ 100 rows guessed for MSTVFs MSTVF identified 500k rows assumed Performance issues if skewed! Execute MSTVF Good Performance!

17 .Batch Mode Memory Grant Feedback Problem: Queries may spill to disk or take too much memory based on poor cardinality estimates MGF will adjust memory grants based on execution feedback MGF will remove spills and improve concurrency for repeating queries

18 .Batch Mode Memory Grant Feedback Before Spill Spill After Spill detected and feedback generated

19 .Batch Mode Adaptive Joins Problem: If cardinality estimates are skewed, we may choose an inappropriate join algorithm AJ will defer the choice of hash join or nested loop until after the first join input has been scanned AJ uses nested loop for small inputs, hash joins for large inputs

20 .Choice In SQL server 2017

21 .Businesses are embracing choice 101001010010 { } T-SQL Java C/C++ C#/VB.NET PHP Node.js Python Ruby Heterogenous environments Multiple data types Different development languages On-premises, cloud, and hybrid environments

22 .SQL Server on the platform of your choice

23 .Buying a SQL Server license—per-server or per-core—grants the option to use it on Windows Server or Linux Previews are free to download and use in a non-production capacity Same set of editions on Linux: Developer, Express, Standard, Web, Enterprise LICENSE Licensing Same license, new choice

24 .What operational features are available on Linux? Support for RHEL, Ubuntu, Docker Package based installs, Docker image Support for Open Shift, Docker Swarm Backup/restore SSMS on Windows connected to Linux Command line tools: sqlcmd , bcp , sqlpackage SQL Server Agent

25 .What operational features are available on Linux? Failover clustering through Pacemaker Availability groups through Pacemaker Replication Log shipping Transparent data encryption SCOM management pack DMVs Full-Text Search

26 .What programmability features are available on Linux? All major language driver compatibility In memory OLTP and Columnstore Compression Always Encrypted, Row Level Security, and data masking AD user authentication (planned) Service Broker Change data capture Partitioning Auditing CLR JSON, XML Third party tools …and more

27 .Scenario All Linux infrastructure Application-level protection Automatic and within seconds failover during unplanned outages No downtime during planned maintenance Performance sensitive app DR required for compliance regulations Solution HADR with Always On Availability Groups on Linux or Windows Backups Reports HA DR Async Log Synchronization Sync Log Synchronization Enterprise building a mission critical app

28 .Read scale-out Scenario SaaS app (website) Catalog database with high volume of concurrent read-only transactions Bottlenecks on primary due to read workloads Increased response time Solution Read scale with availability groups No cluster required Both Linux and Windows

29 .Migration/testing Scenarios ISV solution built on SQL Server on Windows Linux certification Enterprise moving to an all-Linux infrastructure Rigorous business requirements Seamless migration Solution Minimum downtime and HA for cross-platform migrations with Distributed Availability Groups Distributed Availability Group AG2 AG1 Migration/Testing