- Lab
-
Libraries: If you want this lab, consider one of these libraries.
- Data
Tune a Slow Data Warehouse Query in SQL Server
In order to realize the most value from your data warehouse analytics, queries must execute efficiently and reliably. In this lab, you will learn how to investigate slow queries, establish a performance baseline, apply warehouse-appropriate and query-level optimizations, and finally validate that the changes have made an improvement.
Lab Info
Table of Contents
-
Challenge
Analyze a Slow Warehouse Query and Establish a Performance Baseline
Having identified a critical query that is running slowly, your first task is to investigate it and establish a baseline.
- You will start with a populated data warehouse database, GlobomanticsDW.
- Capture baseline execution metrics for the original query in order to establish a starting point (example query in Additional Resources).
- View the query's execution plan and identify the most expensive operations.
-
Challenge
Apply Warehouse-Appropriate Optimizations
With a clear picture of what needs to be addressed, you will now optimize the warehouse table and query in order to improve performance.
- Implement columnstore indexing on the relevant fact table.
- Ensure that batch mode execution is being utilized.
- Rewrite the query to address inefficient patterns that were identified during your analysis.
-
Challenge
Validate Performance Improvements
No tuning is complete without verifying that improvements have actually been gained. With your optimizations in place, it's time to validate performance.
- Run the optimized query and capture new performance metrics.
- Compare improved metrics against the recorded baseline.
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.