Today, I worked on a service request where our customer reported several performance issues in their application connecting to Azure SQL Database. After an in-depth analysis, we found that the issue could be related to the server running the application, including resources assigned, network issues, etc.,. Aside from other tools offered by Azure, following, I would like to share the lessons learned using logman that is part of performance monitor tool (perfmon). 

Tools Overview

perfmon

The Performance Monitor (perfmon) is a Windows tool that provides a visual interface for monitoring system performance. It can track various performance metrics like CPU usage, memory usage, disk activity, and network activity in real-time.

logman

logman is a command-line tool in Windows that allows you to create and manage performance data collection sets. It can be used to automate the collection of performance data, making it an excellent tool for scheduled and long-term monitoring.

Step-by-Step Guide

Step 1: Create a Data Collector Set with logman

The following script shows how to create a data collector set using logman, configure it to collect various performance counters, and store the data in a CSV file.

Save this script as a Windows Command Batch file (.cmd) and execute it as Administrator:

logman create counter MyDataCollector -f csv -o “C:CountersMyDataCollector”
logman update MyDataCollector -c “MemoryAvailable MBytes”
logman update MyDataCollector -c “TCP(*)*”
logman update MyDataCollector -c “Network Interface(*)*”
logman update MyDataCollector -c “Process(*)*”
logman update MyDataCollector -c “Process(_Total)*”
logman update MyDataCollector -c “Processor Information(*)*”
logman update MyDataCollector -c “Processor(_Total)*”
logman update MyDataCollector -c “Processor(0)*”
logman update MyDataCollector -si 00:00:05
logman start MyDataCollector
timeout /t 30
logman stop MyDataCollector
logman delete MyDataCollector

Once the application finished, I checked the CSV file generated and found useful information saved in this file, such as network activity, process activity, and resource usage. In this case, we have generated the file C:CountersMyDataCollector_000001.csv

Step 2: Import Data into Power BI

Once the data is collected and saved in a CSV file, you can import it into Power BI for analysis.

Open Power BI Desktop.
Get Data:

Click on “Get Data” and select “Text/CSV”.
Browse to C:CountersMyDataCollector_000001.csv and import the file.

Transform Data:

Power BI will load a preview of the data. Click on “Transform Data” to clean and format the data if necessary.
You might want to rename columns, change data types, or filter out unnecessary data.

Create Visualizations:

Once the data is loaded, you can start creating visualizations. Use charts, graphs, and tables to analyze the performance data.
For example, you can create line charts to visualize memory usage over time, bar charts for network traffic, and pie charts for process usage.

Disclaimer

The use of this application and the provided scripts is intended for educational and informational purposes only. The scripts and methods demonstrated in this guide are provided “as is” without any warranties or guarantees. It is the user’s responsibility to ensure the accuracy, reliability, and suitability of these tools for their specific needs.

​Microsoft Tech Community – Latest Blogs –Read More