START FROM THE OUTSIDE IN

• Environment

• VM - Disk Size, Storage Size

• Azure Monitor

• Dynamic Management Views (DMV's) and Wait Stats

• Query Store

• Extended Events (XEvents)

  

1. Look over performance guidelines for the SQL VM and going through the check list

   • https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices

2. Enable the Query Store if you are running SQL VM (Query Store is enabled by default in Azure SQL DB)

   • Monitoring performance by using the Query Store

   • Query Performance Insight for Azure SQL Database

   • Best practices with Query Store

3. Leveraging the Performance Dashboard if you are running SQL VM or SQL Managed Instance

   • https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-dashboard?view=sql-server-ver15

4. Using Log Analytics

   • SQL Assessment Solution for SQL VMs (SQL Health Check) - https://docs.microsoft.com/en-us/azure/azure-monitor/insights/sql-assessment

     • The sql assessement solution supports SQL Server version 2012, 2014, 2016, 2017, and 2019.

   • Azure SQL Analytics Solution for Azure SQL DBs and Managed Instance - https://docs.microsoft.com/en-us/azure/azure-monitor/insights/azure-sql

     • The sql assessement solution supports SQL Server version 2012, 2014, 2016, 2017, and 2019.

5. Verify Deployment

   • What Version are you running
   • What Engine Edition
   • What is the DB name, creation date, compatibility level, collation
   • Verify server memory configuration options
   • Gets back DTU limits, CPU limits, Memory limits, Size limits, Log limits
   • Verify installed physical memory, memory usage, memory model, service startup time
   • Verify Deployment.md

6. Azure SQL Database Tips (If you're running Azure SQL DB)

   • Improving your databases with Azure SQL Database tips 
   • Azure SQL Tips
   • Azure SQL Tips Notebook.md

7. Run Wait Stats (Good for SQL VM, MI and Azure SQL DBs)

   • SQL Server Wait Statistics (or please tell me where it hurts…)
   • Get Wait Stats.ipynb

8. Monitor Performance with DMVs

   • SQL Performance DMVs.ipynb

9. Monitor and Optimize Indexes

   • Index Optimization.ipynb

10. What bucket does your issue fall into? 

• Issue is either running alot or waiting alot
• Running alot - You have a CPU issue and your queries are running hot or you have a query compilation problem
• Waiting alot - You have Blocking isses, IO issues because of a lack of indexes, or you have a tempdb contention.