Lesson Learned #195: Performance Best Practices for Azure SQL Database and Managed Instance.

[Jose_Manuel_Jurado]

We usually work on performance issues that following the best practices might be resolved following them. In this article we would like to share a PowerShell Script that checks an example best practices for Azure SQL Database and Managed Instance.

We would like to suggest this PowerShell script that you could find out in this URL that contains initial checks we are facing a performance issue. This PowerShell Script is covering the following best practices:

1. Check if we have any automatic tuning recomendations . Based on the results of the execution  of sys.dm_db_tuning_recommendations DMV informs if your database has any recomendations to apply in your database based on the workload analysis done by the SQL Server engine. 
2. Check if we have have statistics that are out-of-dated Based on the results of the execution of sys.stats and sys.dm_db_stats_properties DMVs inform if any statistics, automatic , user created or associated with an index has the following conditions: If the rows sample is different than the number of rows (this situation may be indicated that the statistics has been updated automatic by SQL Server using a sample or if the statistics has been update more than 15 days ago. 
3. Check if you have any TSQL that was cancelled or aborted. For example, after expiring of execution command timeout. This check is based on QDS DVM call sys.query_store_runtime_stats.
4. Check if you have missing indexes. Based on the results of the execution of sys.dm_db_missing_index_xxx informs if you have any missing indexes that helps you to improve the execution of your query. 
5. Check the setting value of MAXDOP. Based on the results of the sys.database_scoped_configurations DMV check if the value of MAXDOP is 0. 
6. Check the fragmentation of the indexes. Based on the results of sys.dm_db_index_physical_stats DMV check if the fragmentation of the indexes is more than 50 percent. 

Basically, once you have download this PowerShell script, you need to provide the details of your server, database, user, password and destination folder. You could fill up directly these parameters in the PowerShell script if you need to perform more time this process. This PowerShell script only will ask those parameters if they are missing. Also, you could specify as a command line. 

Once the process is executed, this PowerShell script will save a file called PerfChecker.Log in the destination folder chosen. In every execution this file will be deleted and created a new one. 

This project is public and all the contributions to make more accesible, easy and effective will be very welcome. 

Enjoy!