Provider statistics for SQL Server

Microsoft.Data.SqlClient supports runtime statistics. Enable statistics by setting the StatisticsEnabled property on a valid SqlConnection object to True.

After you enable statistics, call RetrieveStatistics for a point-in-time snapshot in the IDictionary object. Enumerate its unordered name/value pairs to read the counters. To reset the counters, call ResetStatistics.

If you haven't enabled statistics, RetrieveStatistics returns each counter's initial value without an exception. After you disable statistics, the counters retain the values collected up to that point.

All statistical values gathered are on a per-connection basis.

Statistical Values Available

The Microsoft SQL Server provider exposes 18 statistical values. Read the dictionary's Count property to get the number of available entries. All counters use Int64, the 64-bit type named long in C# and Long in Visual Basic.

The maximum value, Int64.MaxValue, is (2^63)-1. At that limit, counters are no longer accurate. The greatest valid value for a statistic is therefore Int64.MaxValue - 1, or (2^63)-2.

Note

A dictionary is used for returning provider statistics because the number, names and order of the returned statistics may change in the future. Applications should not rely on a specific value being found in the dictionary, but should instead check whether the value is there and branch accordingly.

The following table describes the available statistical values. The key names for the individual values are not localized across regional versions of .NET Framework.

Key Name	Description
`BuffersReceived`	Returns the number of tabular data stream (TDS) packets received by the provider from SQL Server after the application has started using the provider and has enabled statistics.
`BuffersSent`	Returns the number of TDS packets sent to SQL Server by the provider after statistics have been enabled. Large commands can require multiple buffers. For example, if a large command is sent to the server and it requires six packets, `ServerRoundtrips` is incremented by one and `BuffersSent` is incremented by six.
`BytesReceived`	Returns the number of bytes of data in the TDS packets received by the provider from SQL Server once the application has started using the provider and has enabled statistics.
`BytesSent`	Returns the number of bytes of data sent to SQL Server in TDS packets after the application has started using the provider and has enabled statistics.
`ConnectionTime`	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).
`CursorOpens`	Returns the number of times a cursor was open through the connection once the application has started using the provider and has enabled statistics. Note that read-only/forward-only results returned by `SELECT` statements are not considered cursors and thus do not affect this counter.
`ExecutionTime`	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. The classes that include timing code are: `SqlConnection` `SqlCommand` `SqlDataReader` `SqlDataAdapter` `SqlTransaction` `SqlCommandBuilder` To keep performance-critical members as small as possible, the following members are not timed: `SqlDataReader` `this[]` operator (all overloads) `GetBoolean` `GetChar` `GetDateTime` `GetDecimal` `GetDouble` `GetFloat` `GetGuid` `GetInt16` `GetInt32` `GetInt64` `GetName` `GetOrdinal` `GetSqlBinary` `GetSqlBoolean` `GetSqlByte` `GetSqlDateTime` `GetSqlDecimal` `GetSqlDouble` `GetSqlGuid` `GetSqlInt16` `GetSqlInt32` `GetSqlInt64` `GetSqlMoney` `GetSqlSingle` `GetSqlString` `GetString` `IsDBNull`
`IduCount`	Returns the total number of `INSERT`, `DELETE`, and `UPDATE` statements executed through the connection once the application has started using the provider and has enabled statistics.
`IduRows`	Returns the total number of rows affected by `INSERT`, `DELETE`, and `UPDATE` statements executed through the connection once the application has started using the provider and has enabled statistics.
`NetworkServerTime`	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.
`PreparedExecs`	Returns the number of prepared commands executed through the connection once the application has started using the provider and has enabled statistics.
`Prepares`	Returns the number of statements prepared through the connection once the application has started using the provider and has enabled statistics.
`SelectCount`	Returns the number of `SELECT` statements executed through the connection after the application enables statistics. Includes `FETCH` statements that retrieve rows from cursors. For `SELECT` statements, the provider updates the count when the SqlDataReader reaches the end of the results.
`SelectRows`	Returns the number of rows selected once the application has started using the provider and has enabled statistics. This counter reflects all the rows generated by SQL statements, even those that were not actually consumed by the caller. For example, closing a data reader before reading the entire result set would not affect the count. This includes the rows retrieved from cursors through `FETCH` statements.
`ServerRoundtrips`	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.
`SumResultSets`	Returns the number of result sets that have been used once the application has started using the provider and has enabled statistics. For example this would include any result set returned to the client. For cursors, each fetch or block-fetch operation is considered an independent result set.
`Transactions`	Returns the number of user transactions started once the application has started using the provider and has enabled statistics, including rollbacks. If a connection is running with auto commit on, each command is considered a transaction. This counter increments the transaction count as soon as a `BEGIN TRAN` statement is executed, regardless of whether the transaction is committed or rolled back later.
`UnpreparedExecs`	Returns the number of unprepared statements executed through the connection once the application has started using the provider and has enabled statistics.

Retrieving a Value

The following console application shows how to enable statistics on a connection, retrieve four individual statistic values, and write them out to the console window.

Note

The following example uses the sample AdventureWorks database included with SQL Server. The connection string provided in the sample code assumes the database is installed and available on the local computer. Modify the connection string as necessary for your environment.

Option Strict On

Imports System
Imports System.Collections
Imports System.Data
Imports Microsoft.Data.SqlClient

Module Module1

  Sub Main()
    Dim connectionString As String = GetConnectionString()

    Using awConnection As New SqlConnection(connectionString)
      ' StatisticsEnabled is False by default.
      ' It must be set to True to start the
      ' statistic collection process.
      awConnection.StatisticsEnabled = True

      Dim productSQL As String = "SELECT * FROM Production.Product"
      Dim productAdapter As _
          New SqlDataAdapter(productSQL, awConnection)

      Dim awDataSet As New DataSet()

      awConnection.Open()

      productAdapter.Fill(awDataSet, "ProductTable")

      ' Retrieve the current statistics as
      ' a collection of values at this point
      ' and time.
      Dim currentStatistics As IDictionary = _
          awConnection.RetrieveStatistics()

      Console.WriteLine("Total Counters: " & _
          currentStatistics.Count.ToString())
      Console.WriteLine()

      ' Retrieve a few individual values
      ' related to the previous command.
      Dim bytesReceived As Long = _
          CLng(currentStatistics.Item("BytesReceived"))
      Dim bytesSent As Long = _
          CLng(currentStatistics.Item("BytesSent"))
      Dim selectCount As Long = _
          CLng(currentStatistics.Item("SelectCount"))
      Dim selectRows As Long = _
          CLng(currentStatistics.Item("SelectRows"))

      Console.WriteLine("BytesReceived: " & bytesReceived.ToString())
      Console.WriteLine("BytesSent: " & bytesSent.ToString())
      Console.WriteLine("SelectCount: " & selectCount.ToString())
      Console.WriteLine("SelectRows: " & selectRows.ToString())

      Console.WriteLine()
      Console.WriteLine("Press any key to continue")
      Console.ReadLine()
    End Using

  End Sub

  Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,
    ' you can retrieve it from a configuration file.
    Return "..."
  End Function
End Module

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;

namespace CS_Stats_Console_GetValue
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectionString = GetConnectionString();

      using (SqlConnection awConnection =
        new SqlConnection(connectionString))
      {
        // StatisticsEnabled is False by default.
        // It must be set to True to start the
        // statistic collection process.
        awConnection.StatisticsEnabled = true;

        string productSQL = "SELECT * FROM Production.Product";
        SqlDataAdapter productAdapter =
          new SqlDataAdapter(productSQL, awConnection);

        DataSet awDataSet = new DataSet();

        awConnection.Open();

        productAdapter.Fill(awDataSet, "ProductTable");
        // Retrieve the current statistics as
        // a collection of values at this point
        // and time.
        IDictionary currentStatistics =
          awConnection.RetrieveStatistics();

        Console.WriteLine("Total Counters: " +
          currentStatistics.Count.ToString());
        Console.WriteLine();

        // Retrieve a few individual values
        // related to the previous command.
        long bytesReceived =
            (long) currentStatistics["BytesReceived"];
        long bytesSent =
            (long) currentStatistics["BytesSent"];
        long selectCount =
            (long) currentStatistics["SelectCount"];
        long selectRows =
            (long) currentStatistics["SelectRows"];

        Console.WriteLine("BytesReceived: " +
            bytesReceived.ToString());
        Console.WriteLine("BytesSent: " +
            bytesSent.ToString());
        Console.WriteLine("SelectCount: " +
            selectCount.ToString());
        Console.WriteLine("SelectRows: " +
            selectRows.ToString());

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

    }
    private static string GetConnectionString()
    {
      // To avoid storing the connection string in your code,
      // you can retrieve it from a configuration file.
      return "...";
    }
  }
}

Retrieving All Values

The following console application shows how to enable statistics on a connection, retrieve all available statistic values using the enumerator, and write them to the console window.

Note

Option Strict On

Imports System
Imports System.Collections
Imports System.Data
Imports Microsoft.Data.SqlClient

Module Module1
  Sub Main()
    Dim connectionString As String = GetConnectionString()

    Using awConnection As New SqlConnection(connectionString)
      ' StatisticsEnabled is False by default.
      ' It must be set to True to start the
      ' statistic collection process.
      awConnection.StatisticsEnabled = True

      Dim productSQL As String = "SELECT * FROM Production.Product"
      Dim productAdapter As _
          New SqlDataAdapter(productSQL, awConnection)

      Dim awDataSet As New DataSet()

      awConnection.Open()

      productAdapter.Fill(awDataSet, "ProductTable")

      ' Retrieve the current statistics as
      ' a collection of values at this point
      ' and time.
      Dim currentStatistics As IDictionary = _
          awConnection.RetrieveStatistics()

      Console.WriteLine("Total Counters: " & _
          currentStatistics.Count.ToString())
      Console.WriteLine()

      Console.WriteLine("Key Name and Value")

      ' Note the entries are unsorted.
      For Each entry As DictionaryEntry In currentStatistics
        Console.WriteLine(entry.Key.ToString() & _
            ": " & entry.Value.ToString())
      Next

      Console.WriteLine()
      Console.WriteLine("Press any key to continue")
      Console.ReadLine()
    End Using

  End Sub

  Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,
    ' you can retrieve it from a configuration file.
    Return "..."
  End Function
End Module

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Data.SqlClient;

namespace CS_Stats_Console_GetAll
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectionString = GetConnectionString();

      using (SqlConnection awConnection =
        new SqlConnection(connectionString))
      {
        // StatisticsEnabled is False by default.
        // It must be set to True to start the
        // statistic collection process.
        awConnection.StatisticsEnabled = true;

        string productSQL = "SELECT * FROM Production.Product";
        SqlDataAdapter productAdapter =
            new SqlDataAdapter(productSQL, awConnection);

        DataSet awDataSet = new DataSet();

        awConnection.Open();

        productAdapter.Fill(awDataSet, "ProductTable");

        // Retrieve the current statistics as
        // a collection of values at this point
        // and time.
        IDictionary currentStatistics =
            awConnection.RetrieveStatistics();

        Console.WriteLine("Total Counters: " +
            currentStatistics.Count.ToString());
        Console.WriteLine();

        Console.WriteLine("Key Name and Value");

        // Note the entries are unsorted.
        foreach (DictionaryEntry entry in currentStatistics)
        {
          Console.WriteLine(entry.Key.ToString() +
              ": " + entry.Value.ToString());
        }

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

    }
    private static string GetConnectionString()
    {
      // To avoid storing the connection string in your code,
      // you can retrieve it from a configuration file.
      return "...";
    }
  }
}

Provider statistics for SQL Server

Statistical Values Available

Retrieving a Value

Retrieving All Values

See also

Additional resources