Monday, April 18, 2022

Lesson Learned #199:Collect data for performance comparison Azure SQL Managed Instance vs SQL Server

Today, I worked on a customer service request that they are comparing workload between Azure SQL Managed Instance and SQL Server. Our customer identified some performances issues and we provided this PowerShell Script. to collect some basic information that we need about SQL Server instance.


This PowerShell Script will execute two actions:


  • Read file PERF_Instruct.SQL that contains queries to execute in SQL Server instance to obtain basic information that we need to know, for example, what is the SQL Server environment details, like, instance and databases configuration, number of rows per table, missing indexes, information about the CPU, etc.. 
  • Copies all the ERRORLOG files that are saved on the SQL Server Instance. 


Once this PowerShell Script has been executed, a zip file will be generated automatically, PERF_Export.Zip that you could send to us. With this information, we have several important details about your environment. In every execution this file will be deleted and created a new one. 


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. 


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


Additionally, you could review article wrote by Jovan Popovic.



Posted at