Profiling SQL Server in .NET

The main tool for profiling SQL Server is, of course, SQL Server Profiler. You can, however, achieve more or less the same results using .NET, I'll show you how.

First, you need to have assemblies Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.ConnectionInfoExtended at hand, which are normally found in C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies (for SQL Server 2014), if you installed the Shared Management Objects package from the SQL Server Feature Pack (again, this link is for SQL Server 2014, but there are feature packs for each version of SQL Server).

Then, write something like this:

var connInfo = new SqlConnectionInfo(@".\SQLEXPRESS");

//connInfo.UserName = "";

//connInfo.Password = "";

using (var reader = new TraceServer())

{

    reader.InitializeAsReader(connInfo, @"C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Profiler\Templates\Microsoft SQL Server\120\Standard.tdf");

    while (reader.Read() == true)

    {

        for (var i = 1; i < reader.FieldCount; ++i)

        {

            Console.Write(reader.GetName(i));

            Console.Write(": ");

            Console.WriteLine(reader.GetValue(i));

        }

        Console.WriteLine();

    }

}

And this is it. You will get a new entry for each event that happens on the server, such as SQL being run, etc. Among others, you get the following information:

• ApplicationName: the name of the application that is executing the SQL, as supplied in its connection string;
• NTUserName: the Windows user name of the connected user;
• LoginName: the SQL Server login name of the connected user;
• Duration: the request duration;
• SPID: the executing Server Process ID;
• StartTime: the start timestamp of the request;
• EndTime: the end timestamp;
• TextData: textual data, like, SQL.

One thing to keep in mind is that you need to pass a valid SQL Server Profiler template for the InitializeAsReader method. You can add one of the existing templates to your Visual Studio project, set it to copy to the output folder, and reference it locally, if you prefer, otherwise, you will need to know the full path where it is installed (C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Profiler\Templates\Microsoft SQL Server\120 is for SQL Server 2014).