4-Optimize query performance (5-10%).md

DP-300: Optimize Query Performance (5-10%)

Review query plans

• Determine the appropriate type of execution plan
  • Live Query Statistics
  • Actual Execution Plan
  • Estimated Execution Plan
  • Showplan
• Identify problem areas in execution plans
• Extract query plans from the Query Store

Evaluate performance improvements

• Determine the appropriate Dynamic Management Views (DMVs) to gather query performance information
• Identify performance issues using DMVs
• Identify and implement index changes for queries
• Recommend query construct modifications based on resource usage
  • Monitoring performance by using the Query Store
• Assess the use of Hints for Query performance

Review database table and index design

• Identify data quality issues with duplication of data
  • Data Matching
• Identify normal form of database
• Assess index design for performance
  • Improve the performance of your Azure SQL Databases using Index Advisor
• Validate Data Types defined for columns
  • Validate Integer and Decimal Values in SQL Server
• Recommend table and index storage including filegroups
• Evaluate Table Partitioning strategy
• Evaluate the use of Compression for tables and indexes
  • sp_estimate_data_compression_savings (Transact-SQL)

Return to Table of Contents