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:
- 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.
- 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.
- 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 DMV call sys.query_store_runtime_stats.
- 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.
- 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.
- 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.
- Check the TSQL command execution timeouts. Provide the queries that given command execution timeouts and provide the execution plan.
- Check the top 10 of wait stats. Based on the results of sys.query_store_wait_stats and will create a new CSV file per database, example, PerfCheckerWaitStats_dbname.csv with the content the query and the top 10 of wait stats.
- Export all the results of Query Data Store to .bcp and .xml to be able to import in a consolidate database. It is very useful when you have multiple databases in Azure SQL Managed Instance or Elastic Database Pool.
- Obtain resource usage per database.
- Total amount of space and rows per schema and table name.
Basically, once you have download this PowerShell script, you need to provide the details of:
- Server Name
- Database Name (If you type ALL) the PowerShell Script will check all the databases of this server.
- User Name
- Password
- Destination folder where all files will be generated.
- DropExisting with value 1 or 0, if the previous files located on Destinatio folder will be deleted every time that you execute the process.
- ElasticDBPoolName. PowerShell Script will check all the databases that are associated with this elastic database pool (only for Azure SQL Database).
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 with all the operations done. In every execution this file will be deleted and created a new one.
- Every check done will save two files:
- Extension .Txt that contains the report of the operation done.
- Extension .task that contains a possible mitigation about the issue found.
- For the extraction of query data store this PowerShell script will generated two additional files per QDS table:
- Extension .bcp with the information exported.
- Extension .xml with the structure of this .bcp file.
This project is public and all the contributions to make more accesible, easy and effective will be very welcome.
Once you executed the PowerShell, please, consider to use Perf Collector Analyzer to review Query Data Store data aggregated at instance level and other topics.
In this video below you could see an example about how to execute this PowerShel script.
Enjoy!
