Today, 26 July 2022 we released a new version adding around 20 new parameters and other improvements.
We usually work on service request that our customers want to check the spent time to connect to the database and the execution time invested on a query. In this article, I would like to share with you an example how to obtain both data. We developed this following PowerShell Script to obtain the following data:
- How the server name is resolved. Sometimes we could have some issues resolving the IP.
- Number of ports opened using the remote port 1433 and redirect ports of Azure SQL DB and Managed Instance.
- By Process how to know ports opened using the remote port 1433 and redirect ports
- Performance counter values of:
- "\Processor(_total)\*"
- "\Memory\*"
- "\Network Interface(*)\*"
- "\Network Adapter(*)\*"
- Based on this URL:
- NetworkServerTime (ms), that returns the cumulative amount of time (in milliseconds) that the provider spent waiting for replies from the server once the application has started using the provider and has enabled statistics.
- Execution Time (ms) , that Returns the cumulative amount of time (in milliseconds) that the provider has spent processing once statistics have been enabled, including the time spent waiting for replies from the server as well as the time spent executing code in the provider itself.
- Connection Time (ms) , that returns the amount of time (in milliseconds) that the connection has been opened after statistics have been enabled (total connection time if statistics were enabled before opening the connection).
- ServerRoundTrips (ms) , that returns the number of times the connection sent commands to the server and got a reply back once the application has started using the provider and has enabled statistics.
- Execution Plan , that returns the actual execution plan used for this query.
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 Results.Log in the destination folder chosen with all operations and values returned and will create a new file called Results_PerfCounter.Log with value of performance counters chosen . In every execution these files will be deleted and created a new ones.
This project is public and all the contributions to make more accesible, easy and effective will be very welcome.
Enjoy!
