- Lab
-
Libraries: If you want this lab, consider one of these libraries.
- Core Tech
Performance Diagnostic and Indexing
In this hands-on lab, you will diagnose and resolve SQL Server performance issues using execution plans and dynamic management views. You will analyze three slow-running queries to identify expensive operators, such as Table Scans and Key Lookups; compare estimated and actual row counts to detect outdated statistics; and examine query wait statistics to identify server bottlenecks. You will create covering indexes and composite indexes to eliminate scans, rewrite non-SARGable predicates for index seeks, and replace correlated subqueries with window functions. By the end of the lab, you will have reduced query execution time and documented the before-and-after performance metrics.
Lab Info
Table of Contents
-
Challenge
Analyze Query Execution Plans to Pinpoint Inefficiencies
- Execute three slow-running queries and capture baseline performance metrics using Statistics IO and Statistics Time.
- Read text execution plans to identify expensive operators such as Table Scans, Hash Match Joins, and Key Lookups.
- Compare estimated versus actual row counts to detect outdated or inaccurate statistics.
- Query wait statistics to determine whether performance is constrained by CPU, memory, or I/O.
-
Challenge
Implement Strategic Indexing for Read Optimization
- Review missing index recommendations.
- Create non-clustered covering indexes with INCLUDE columns to eliminate base table lookups.
- Design composite indexes following the equality-before-range column ordering rule.
- Identify and remove unused indexes to reduce write overhead.
-
Challenge
Refactor Inefficient SQL for Scalable Throughput
- Rewrite non-SARGable WHERE clauses to enable index seeks instead of scans.
- Replace a correlated subquery with a window function for single-pass processing.
- Implement parameterized queries to promote plan reuse.
- Run a final performance comparison documenting percentage improvement for each query.
Real skill practice before real-world application
Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.
Learn by doing
Engage hands-on with the tools and technologies you’re learning. You pick the skill, we provide the credentials and environment.
Follow your guide
All labs have detailed instructions and objectives, guiding you through the learning process and ensuring you understand every step.
Turn time into mastery
On average, you retain 75% more of your learning if you take time to practice. Hands-on labs set you up for success to make those skills stick.