Summary
In this module, you learned about monitoring server performance using SQL Server's wait statistics, which include resource waits, queue waits, and external waits. You have also learned how to use system views like sys.dm_os_wait_stats and sys.dm_db_wait_stats to get an overview of server performance and identify potential issues. The module also covered the use of Dynamic Management Views (DMVs) to understand and correlate performance problems with other database events. Additionally, you learned about common wait types and how the Query Store tracks waits associated with specific queries.
The main takeaways from this module include understanding how to tune T-SQL queries by evaluating and adjusting your indexing strategy. You learned that proper indexing can reduce IOs, improve memory utilization, and ease pressure on IO and storage systems. The module also discussed the importance of column order in indexes and the use of resumable index for large tables. Furthermore, you learned about query hints and their potential impact on database structure and performance. Lastly, the module covered how to optimize Azure SQL performance by identifying whether a performance issue is due to high CPU usage or waiting on a resource, and using appropriate tools and methods to diagnose and resolve these issues.