Optimize SQL Database Objects and Infrastructure

Database professionals responsible for implementing and maintaining SQL Server 2016

Prerequisite
None

Expected Duration
119 minutes

Description
Optimizing statistics helps ensure the most efficient means of executing statements in SQL Server 2016. This course covers how to create, modify, and troubleshoot statistics and indexes in SQL Server 2016. This course also covers how to analyze and troubleshoot query plans in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.

Objective

Optimizing Statistics

  • start the course
  • create statistics and determine accuracy of results
  • modify existing statistics
  • view statistic properties
  • update query optimization statistics
  • describe available maintenance tasks to perform

Optimizing Indexes

  • perform database tuning using the database engine tuning advisor
  • demonstrate how to create a maintenance plan
  • perform index defragmentation
  • review current index usage and identify missing indexes
  • identify and consolidate overlapping indexes

Analyzing Query Plans

  • capture query plans using extended events and traces
  • describe logical operators and how they are used
  • describe physical operators and how they are used
  • recognize best practices to follow with the Query Store
  • describe when and where to use the Query Store

Troubleshooting Query Plans

  • determine how to start query performance troubleshooting
  • identify underperforming query plan operators
  • compare query plans and related metadata
  • configure the Azure SQL Database Performance Insight

Practice: Statistics and Indexes

  • use statistics objects and index optimization methods

MONTHLY SUBSCRIPTION

$129/month
 

ANNUAL SUBSCRIPTION

$1295/year

Multi-license discounts available for Annual and Monthly subscriptions.