SQL Server 2017 Demo: Exciting New Features & Capabilities
Alicia Townsend NetCom Learning www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Agenda
• What's New in SQL Server 2017 • Installing SQL Server on Ubuntu • Understanding Machine Learning Services Package • Getting Familiar with The Graph Databases • Understanding Automatic Query Tuning • Learn About Adaptive Query Processing • Q&A session with the speaker
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
What's New in SQL Server 2017
• Installing SQL Server on Ubuntu • Understanding Machine Learning Services Package • Getting Familiar with The Graph Databases • Understanding Automatic Query Tuning • Learn About Adaptive Query Processing
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Installing SQL Server on Ubuntu Prerequisites Memory
2 GB
File System
XFS or EXT4 (other file systems, such as BTRFS, are unsupported)
Disk space
6 GB
Processor speed
2 GHz
Processor cores
2 cores
Processor type
x64-compatible only
• Use NFS version 4.2 or higher. Older versions of NFS do not support required features, such as fallocate and sparse file creation, common to modern file systems. • Locate only the /var/opt/mssql directories on the NFS mount. Other files, such as the SQL Server system binaries, are not supported. • Ensure that NFS clients use the 'nolock' option when mounting the remote share. www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Demo
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Understanding Machine Learning Services Package
• SQL Server Machine Learning Services is an embedded, predictive analytics and data science engine that can execute R and Python code within a SQL Server database as stored procedures, as T-SQL script containing R or Python statements, or as R or Python code containing T-SQL. • SQL Server Machine Learning Services (In-Database) operates within the database engine instance, where the calculation engine is fully integrated with the database engine. Most installations are this option. • SQL Server Machine Learning Server (Standalone) is Machine Learning Server for Windows that runs independently of the database engine. Although you use SQL Server Setup to install the server, the feature is not instance-aware. Functionally, it is equivalent to the non-SQL-Server Microsoft Machine Learning Server for Windows. www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Machine Learning
• Make sure it is enabled • sp_configure 'external scripts enabled’ • Make sure SQL Launchpad service is started
• What can you do? • Run Python and R scripts • Use Python and R to do data analysis and data mining operations
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Demo
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Getting Familiar with the Graph Databases
• What can a Graph Database do well? • Expressing Many to Many relationships • Edges or relationships are first class entities in a Graph Database and can have attributes or properties associated with them. • A single edge can flexibly connect multiple nodes in a Graph Database. • You can express pattern matching and multi-hop navigation queries easily.
• Best Use Cases • Your application has hierarchical data. The HierarchyID datatype can be used to implement hierarchies, but it has some limitations. For example, it does not allow you to store multiple parents for a node. • Your application has complex many-to-many relationships; as application evolves, new relationships are added. • You need to analyze interconnected data and relationships. www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Getting Familiar with The Graph Databases (cont)
• A graph is composed of two elements: a NODE (vertices) and an EDGE (relationship). Each node represents entities, and the nodes are connected to one another with edges; these provide details on the relationship between two nodes with their own set of attributes and properties. • The graph database can be defined as the data structure representation of an entity modeled as graphs. It is derived from the graph theory. The data structures are the Node and the Edge. The attributes are the properties of the node or the edge. The relationship defines the interconnection between the nodes.
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Graph Database
• NODES • Person • Restaurant • City
• EDGES (relationships) • • • • •
Friends LivesIn Likes City LocatedIn Likes Restaurant
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Understanding Automatic Query Tuning
• Provides insight into potential query performance problems • Recommend solutions • Automatically fix identified problems • Two Automatic options available • Automatic plan correction (available in SQL Server 2017 (14.x) and Azure SQL Database) that identifies problematic query execution plans and fixes SQL plan performance problems. • Automatic index management (available only in Azure SQL Database) that identifies indexes that should be added in your database, and indexes that should be removed.
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Automatic Query Tuning
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
The How To’s of Automatic Query Tuning
• ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); • Automatically forces any recommendation where the estimated CPU gain is higher than 10 seconds, or the number of errors in the new plan is higher than the number of errors in the recommended plan, and verify that the forced plan is better than the current one
• sp_query_store_force_plan • Manually for it to run the last known good plan
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Learn About Adaptive Query Processing
• Normal Query Optimization • Query Optimizer generates a set of feasible execution plans for a specific query. During this time, the cost of plan options is estimated and the plan with the lowest estimated cost is used. • The query execution process takes the plan chosen by the query optimizer and uses it for execution. • Not always the most optimized plan • Wrong number of rows • Over estimate memory allocation
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Adaptive Query Processing Fixes the Issues
• Over allocating memory • Batch Mode Memory Grant Feedback – adapts the allocated memory based upon memory requirements of subsequent executions
• Using inefficient join method • Batch Mode Adaptive Join – defers join choice of Nested vs Hash until after fist input has been scanned (small input better for Nested join)
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
Adaptive Query Processing Fixes the Issues (cont)
• Interleave execution for multi-statement table valued functions
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Recorded Webinar Video
To watch the recorded webinar video for live demos, please access the link: https://bit.ly/2nnd2tw
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
About NetCom Learning
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Recommended Courses
» 20764: Administering a SQL Database Infrastructure (SQL Server 2017) - Class scheduled on Sep 10 » 20774: Perform Cloud Data Science with Azure Machine Learning - Class scheduled on Sep 10 » 20761: Querying Data with Transact-SQL (SQL Server 2017) - Class scheduled on Sep 17 » 20762: Developing SQL Databases (SQL Server 2017) - Class scheduled on Sep 24 » 10990: Analyzing Data with SQL Server Reporting Services (SQL Server 2017) - Class scheduled on Oct 1
» OD10998A: Updating Your Skills to SQL Server 2017 MOD
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning
How to Hunt for Security Threats Creating Social Media Graphics in Photoshop CC Project Management: Developing Project Schedules and Budgets How to Configure Networking in Windows 10 Devices ASP.NET Functions on Microsoft Azure Getting Started With CompTIA PenTest+ PowerPoint 2016: 10 Tips to Master Presentations Hands-On Power BI for Data Visualization
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Promotions
From Cloud to Security, to Data and AI, to Networking, to Application Development, to Design, to Business Process & Application; all classes delivered by top-notch instructors in in-person Instructor-led Classroom or Live Online. And after you train, treat yourself with Gift Card rewards. Learn More www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
Follow Us On:
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
Š1998-2018 NetCom Learning
THANK YOU !!!
www.netcomlearning.com | info@netcomlearning.com | (888) 563 8266
©1998-2018 NetCom Learning