https://sqlmaster.mssqlplanner.com

Module 7 Exercises: SQL Server Advanced Performance Labs (10 Hands-On Exercises)

Written by

admin

in

Module 7 Exercises: SQL Server Advanced Performance Labs

5 guided labs, 3 challenge scenarios, and 2 break-it labs.

Guided Labs

Guided1. Create a MEMORY_OPTIMIZED_DATA filegroup and a simple memory-optimized table in a test database.
Guided2. Write and call a natively compiled stored procedure against that table.
Guided3. Create a resource pool capping CPU at 20%, a workload group, and a classifier function routing a specific test login into it.
Guided4. Check whether memory-optimized tempdb metadata is currently enabled on your instance.

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
Guided5. List which features from this entire course (Profiler, Resource Governor, tempdb files) would NOT be available if this workload were moved to Azure SQL Database.

Challenge Scenarios

Challenge6. A session-state table is experiencing the exact ever-increasing-key latch contention pattern from Module 5. Propose whether In-Memory OLTP or simply better key design is the more appropriate fix, and justify your choice.
Challenge7. A shared instance runs both a mission-critical OLTP app and an analyst’s ad-hoc reporting tool that occasionally runs 100% CPU for minutes. Design a Resource Governor configuration to protect the OLTP workload.
Challenge8. A team is migrating an on-prem SQL Server workload to Azure SQL Managed Instance and worried about losing Profiler and Resource Governor. Write a short migration note explaining what changes and what stays the same.

Break-It Labs

Break-It9. Deliberately run an unrestricted heavy query and observe its CPU consumption, then place it under a 10%-CPU-capped Resource Governor workload group and re-measure the difference in wall-clock duration.
Break-It10. In a disposable test instance, deliberately disable memory-optimized tempdb metadata (if enabled) or simulate its absence by inducing heavy tempdb system-table churn, observe PAGELATCH contention on system tables specifically, then enable it and compare.

Enjoyed this?

Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS  | 
Share on X  | 
Share on LinkedIn  | 
Share on Facebook

Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

More posts