{"id":17117,"date":"2024-06-10T09:04:50","date_gmt":"2024-06-10T07:04:50","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=17117"},"modified":"2025-10-01T12:12:11","modified_gmt":"2025-10-01T10:12:11","slug":"plotting-sql-server-performance-counters-with-python","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/plotting-sql-server-performance-counters-with-python\/","title":{"rendered":"Plotting SQL Server Performance counters with Python"},"content":{"rendered":"\n

As a consultant, I often use Performance Monitor<\/a> (Perfmon) to collect performance metrics for SQL Server. After running Perfmon for, say, a week to capture a baseline of server activity, you can end up with a large amount of data.<\/p>\n\n\n\n

Here\u2019s what you might end up with:<\/p>\n\n\n

\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n

Looking at so much information can be overwhelming if you don’t know exactly what you are looking for. So, how can we extract some useful information from that?<\/p>\n\n\n\n

Well, there are not many options:<\/p>\n\n\n\n

    \n
  1. A commonly used tool is PAL (Performance Analysis of Logs<\/a>) but I’ve never been a big fan. It’s not always easy to get relevant information from it.<\/li>\n\n\n\n
  2. Another way is to convert your Perfmon log (blg file) to a CSV file and then use Excel to perform data analysis using pivot tables and charts. However, this process can be tedious and time-consuming.<\/li>\n\n\n\n
  3. You can also use Power BI to display, filter, and graph the data from the CSV file. This might be the best option, but I’m not very comfortable with Power BI yet.<\/li>\n<\/ol>\n\n\n\n

    Not satisfied with these solutions, I decided to build a tool myself. <\/p>\n\n\n\n

    While I usually rely on PowerShell for database administration tasks, for this one I was ready to try something new. Python, often referred to as the most popular programming language, has seen exponential growth the past years thanks to its use in Data Engineering, Data Science, and Machine Learning.<\/p>\n\n\n\n

    Although I had never tried Python before, I believe it’s never too late to learn something new.
    So here it is. I wrote a small script two years ago (writing a blog post takes time, right?) to help me analyze Perfmon counters.
    I named it     blg2graph<\/a> and it’s available on GitHub.<\/p>\n\n\n\n

    How to use<\/h3>\n\n\n
    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    When you run the main.py script, several options are available:<\/p>\n\n\n\n

      \n
    1. Create CSV files from a Perfmon BLG file.<\/li>\n\n\n\n
    2. Get the counters list from a BLG file, mainly for troubleshooting.<\/li>\n\n\n\n
    3. Create graphs from a CSV file.<\/li>\n<\/ol>\n\n\n\n

      Here\u2019s how I use it:<\/p>\n\n\n\n


      First, I start with a single binary .BLG file containing data collected over several days.<\/p>\n\n\n

      \n
      \"\"<\/figure>\n<\/div>\n\n\n

      Using blg2graph, I transform this BLG file into multiple CSVs, one per day.<\/p>\n\n\n

      \n
      \"\"<\/figure>\n<\/div>\n\n\n

      Finally, I generate graphs for a specific CSV file, i.e., for a particular day.<\/p>\n\n\n

      \n
      \"\"<\/figure>\n<\/div>\n\n\n

      Output<\/h3>\n\n\n\n

      Here is the result, an HTML file containing a chart for each Perfmon counter for a given day.<\/p>\n\n\n

      \n
      \"\"<\/figure>\n<\/div>\n\n\n

      I can now search for the data I am interested in, investigate, try to identify anomalies, and finally use these graphs to illustrate a report or documentation.<\/p>\n\n\n

      \n
      \"\"<\/figure>\n<\/div>\n\n\n

      Configuration<\/h3>\n\n\n\n

      I can choose the time range to be used for generating the graphs by modifying the JSON configuration file: config.json.<\/p>\n\n\n

      \n"timeframe":{\n        "group_interval":"1Min",\n        "start_time":"06:00:00",\n        "end_time":"19:00:00"\n    }\n<\/pre><\/div>\n\n\n
      I can also choose to generate only specific graphs. To do this, I list the Perfmon counters in the JSON file under “includeCounters” and set the “Apply” property to TRUE.
      If set to FALSE, the script will generate graphs for all counters present in the CSV file.<\/p>\n\n\n
      \n"includeCounters":{\n        "Apply":false,\n        "counters":[\n            "Buffer Manager\\\\Buffer cache hit ratio",\n            "Transactions\/sec",\n            "Priviledged Time\/sec"\n        ]\n    }\n<\/pre><\/div>\n\n\n
      Alternatively, I can exclude certain counters using the “excludeCounters” object.<\/p>\n\n\n\n
      Installation<\/h3>\n\n\n\n
      Here are the steps to install Python and run the script on a Windows machine:<\/p>\n\n\n\n
        \n
      1. Install Python:<\/strong>\n