SQL: SQL Bandwidth Meter Log Reader


I have updated my SQL Bandwidth Meter Log Reader application. It now has a GUI interface which allows for customizations of the reported data by setting user defined packet size, the bandwidth units to display (bytes, kilobytes, etc), the ability to exclude specific users, and group the summary results by login or by hour.

The application and its associated source code can be found on codeplex here.

Posted in SQL. Tags: , . Leave a Comment »

SQL: Monitor Bandwidth used by a SQL Server Instance


We are in the process of migrating the database servers for one of our applications from an onsite local server to a third party hosted server on the internet. One of the key factors that goes into which hosting package to purchase is how much bandwidth will be required for communication between the client and server. To determine the most cost effective solution we needed to figure out how much bandwidth each Microsoft SQL Server instance is using on a daily and monthly basis. Unfortunately Microsoft SQL Server 2005 doesn’t have a built in tool to track this so I turned to the trusty internet to try and find one.

I found a ton of tools that were way overkill for the kind of data that we wanted. After some serious digging I finally found a simple application developed by the guys at Hosts Tools that is amazingly easy to configure and setup and does just want I needed. They call it the SQL Bandwidth Meter. It requires the Windows Packet Capture (WinPCap) library so make sure you install that first (it comes in the download package).

Once the application is setup, it will log the number of packets received and the number of packets sent by a given Microsoft SQL Server instance on a per user level every hour. It is a Windows service so it can run in the background without any user having to be logged in.

The log file is just a csv file in the format of [Hour],[SQL_Login],[Packets_In],[Packets_Out] (see sample below).

0,,0,0
1,,0,0
2,,0,0
3,,0,0
4,,0,0
5,,0,0
6,,0,0
7,,0,0
8,administrator,29120,29119
8,Shea,1678,2236
8,nick,1637,2181
9,Jaalisa,3591,4928
9,Shea,7532,8972
...

The default packet size set by Microsoft SQL Server is 4,096 bytes or 4 Kilobytes as indicated in the documentation here. Since the application logs the number of packets sent and received and not the number of bytes a bit of simple math is required to calculate bandwidth used. As a true computer programmer, instead of just opening the log file up in Excel and writing a simple summation formula, I wrote an application that parses the log files and performs the calculations and conversions automatically. We are monitoring multiple instances and thus have multiple log files so when the application starts up, you are prompted to enter the path to the directory that contains the log files.

static void Main(string[] args)
{
    int totalOutPackets = 0;
    int totalInPackets = 0;
    int outPacketsIndex = 2;
    int inPacketsIndex = 3;
    float kbInMb = 1024;

    // Get the directory containing the log files from the user
    Console.Write("Log file directory: ");
    string logFileDirectory = Console.ReadLine();
    Console.WriteLine();

    DirectoryInfo dirInfo = new DirectoryInfo(logFileDirectory);
    
    // Loop through each log file
    foreach (FileInfo file in dirInfo.GetFiles())
    {
        int outPackets = 0;
        int inPackets = 0;
        float outMB = 0;
        float inMB = 0;
        
        StreamReader reader = new StreamReader(file.FullName);

        while (reader.Peek() != -1)
        {
            string[] line = reader.ReadLine().Split(',');

            // Get the out and in packet counts
            if (line.Length > 3)
            {
                outPackets += int.Parse(line[outPacketsIndex]);
                inPackets += int.Parse(line[inPacketsIndex]);                        
            }
        }

        reader.Close();

        totalOutPackets += outPackets;
        totalInPackets += inPackets;

        outMB = (outPackets * 4) / kbInMb;
        inMB = (inPackets * 4) / kbInMb;

        // Display the results for this instance
        Console.WriteLine(file.Name);
        for (int i = 0; i < file.Name.Length; i++)
            Console.Write("-");
        Console.WriteLine();
        Console.WriteLine(string.Format("{0:0,0}", outPackets) + " Packets Out");
        Console.WriteLine(string.Format("{0:0,0}", inPackets) + " Packets In");
        Console.WriteLine(string.Format("{0:0,0}", outPackets + inPackets) + " Total Packets");
        Console.WriteLine();

        Console.WriteLine(string.Format("{0:0,0.##}", outMB) + " MB Out");
        Console.WriteLine(string.Format("{0:0,0.##}", inMB) + " MB In");
        Console.WriteLine(string.Format("{0:0,0.##}", outMB + inMB) + " Total MB");
        Console.WriteLine();
        Console.WriteLine();
        Console.WriteLine();                
    }

    float totalOutMB = (totalOutPackets * 4) / kbInMb;
    float totalInMB = (totalInPackets * 4) / kbInMb;
        

    // Display the results for all instances
    Console.WriteLine("Totals");
    Console.WriteLine("------");
    Console.WriteLine(string.Format("{0:0,0}", totalOutPackets) + " Packets Out");
    Console.WriteLine(string.Format("{0:0,0}", totalInPackets) + " Packets In");
    Console.WriteLine(string.Format("{0:0,0}", totalOutPackets + totalInPackets) + " Total Packets");
    Console.WriteLine();

    Console.WriteLine(string.Format("{0:0,0.##}", totalOutMB) + " MB Out");
    Console.WriteLine(string.Format("{0:0,0.##}", totalInMB) + " MB In");
    Console.WriteLine(string.Format("{0:0,0.##}", totalOutMB + totalInMB) + " Total MB");
    Console.WriteLine();
    Console.WriteLine();
    Console.WriteLine();

    Console.WriteLine("Press any key to close...");
    Console.ReadLine();
}

// Log file directory: C:\SQLBWLogs

// SBVR.log
// --------
// 59,641 Packets Out
// 68,895 Packets In
// 128,536 Total Packets 

// 232.97 MB Out
// 269.12 MB In
// 502.09 Total MB


// SCM.log
// -------
// 86,256 Packets Out
// 95,607 Packets In
// 181,863 Total Packets

// 336.94 MB Out
// 373.46 MB In
// 710.4 Total MB


// SFGP.log
// --------
// 59,541 Packets Out
// 65,206 Packets In
// 124,747 Total Packets

// 232.58 MB Out
// 254.71 MB In
// 487.29 Total MB


// Totals
// ------
// 205,438 Packets Out
// 229,708 Packets In
// 435,146 Total Packets

// 802.49 MB Out
// 897.3 MB In
// 1,699.79 Total MB
 
// Press any key to close...

The code and executable for the parser can be found on codeplex here.

Posted in SQL. Tags: , . 1 Comment »
Follow

Get every new post delivered to your Inbox.

Join 68 other followers