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.
Learning Objectives
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