Recorded Webinar: SQL Server 2017 Demo - Exciting New Features & Capabilities

1
SQL Server 2017 Demo Exciting New Features
Capabilities
Alicia Townsend NetCom Learning
www.netcomlearning.com info_at_netcomlearning.com
[phone]
2
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
• QA session with the speaker

www.netcomlearning.com info_at_netcomlearning.com
[phone]
3
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_at_netcomlearning.com
[phone]
4
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_at_netcomlearning.com
[phone]
5
Demo
www.netcomlearning.com info_at_netcomlearning.com
[phone]
6
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_at_netcomlearning.com
[phone]
7
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_at_netcomlearning.com
[phone]
8
Demo
www.netcomlearning.com info_at_netcomlearning.com
[phone]
9
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_at_netcomlearning.com
[phone]
10
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_at_netcomlearning.com
[phone]
11
Graph Database

• NODES
• Person
• Restaurant
• City
• EDGES (relationships)
• Friends
• LivesIn
• Likes ? City
• LocatedIn
• Likes ? Restaurant

www.netcomlearning.com info_at_netcomlearning.com
[phone]
12
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_at_netcomlearning.com
[phone]
13
Automatic Query Tuning
www.netcomlearning.com info_at_netcomlearning.com
[phone]
14
The How Tos 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_at_netcomlearning.com
[phone]
15
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_at_netcomlearning.com
[phone]
16
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_at_netcomlearning.com
[phone]
17
Adaptive Query Processing Fixes the Issues (cont)

• Interleave execution for multi-statement table
  valued functions

www.netcomlearning.com info_at_netcomlearning.com
[phone]
18
Recorded Webinar Video
To watch the recorded webinar video for live
demos, please access the link https//bit.ly/2nnd
2tw
www.netcomlearning.com info_at_netcomlearning.com
[phone]
19
About NetCom Learning
www.netcomlearning.com info_at_netcomlearning.com
[phone]
20
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_at_netcomlearning.com
[phone]
21
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_at_netcomlearning.com
[phone]
22
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_at_netcomlearning.com
[phone]
23
Follow Us On
www.netcomlearning.com info_at_netcomlearning.com
[phone]
24
www.netcomlearning.com info_at_netcomlearning.com
[phone]
25
THANK YOU !!!
www.netcomlearning.com info_at_netcomlearning.com
[phone]