SqlConnection 统计信息

 

connection.StatisticsEnabled = true;
connection.Open();


IDictionary dict = connection.RetrieveStatistics();
Hashtable ht = new Hashtable (dict);
var enumerator = ht.GetEnumerator();
while (enumerator.MoveNext())
{
    Console.WriteLine("{0} -- {1}", enumerator.Key, (long)enumerator.Value);
}

 

//执行时间
ExecutionTime -- 70

//准备执行
UnpreparedExecs -- 1

//行数
SelectRows -- 161

//准备
Prepares -- 0

//缓冲器发送
BuffersSent -- 1

//准备执行
PreparedExecs -- 0

//选择数
SelectCount -- 1

IduRows -- 0

//接收的字节数
BytesReceived -- 26838

//事务
Transactions -- 0

IduCount -- 0

//服务器往返
ServerRoundtrips -- 1

//游标是否打开
CursorOpens -- 0

//和结果集
SumResultSets -- 1

//网络服务器的时间
NetworkServerTime -- 0

//连接时间
ConnectionTime -- 1010

//发送字节数
BytesSent -- 126

//缓冲区收到
BuffersReceived -- 4

  

IDictionary dict = connection.RetrieveStatistics();
Hashtable ht = new Hashtable (dict);
foreach ( DictionaryEntry item in ht)
{
    Console.WriteLine("{0} -- {1}", item.Key, (long)item.Value);
}

 

源代码：

internal sealed class SqlStatistics
{
    // internal values that are not exposed through properties
    internal long _closeTimestamp;
    internal long _openTimestamp;
    internal long _startExecutionTimestamp;
    internal long _startFetchTimestamp;
    internal long _startNetworkServerTimestamp;

    // internal values that are exposed through properties
    internal long _buffersReceived;
    internal long _buffersSent;
    internal long _bytesReceived;
    internal long _bytesSent;
    internal long _connectionTime;
    internal long _cursorOpens;
    internal long _executionTime;
    internal long _iduCount;
    internal long _iduRows;
    internal long _networkServerTime;
    internal long _preparedExecs;
    internal long _prepares;
    internal long _selectCount;
    internal long _selectRows;
    internal long _serverRoundtrips;
    internal long _sumResultSets;
    internal long _transactions;
    internal long _unpreparedExecs;

    // these flags are required if statistics is turned on/off in the middle of command execution
    private bool _waitForDoneAfterRow;
    private bool _waitForReply;


    internal SqlStatistics()
    {
    }

    internal IDictionary GetHashtable()
    {
        Hashtable ht = new Hashtable();

        ht.Add("BuffersReceived", _buffersReceived);
        ht.Add("BuffersSent", _buffersSent);
        ht.Add("BytesReceived", _bytesReceived);
        ht.Add("BytesSent", _bytesSent);
        ht.Add("CursorOpens", _cursorOpens);
        ht.Add("IduCount", _iduCount);
        ht.Add("IduRows", _iduRows);
        ht.Add("PreparedExecs", _preparedExecs);
        ht.Add("Prepares", _prepares);
        ht.Add("SelectCount", _selectCount);
        ht.Add("SelectRows", _selectRows);
        ht.Add("ServerRoundtrips", _serverRoundtrips);
        ht.Add("SumResultSets", _sumResultSets);
        ht.Add("Transactions", _transactions);
        ht.Add("UnpreparedExecs", _unpreparedExecs);

        ht.Add("ConnectionTime", ADP.TimerToMilliseconds(_connectionTime));
        ht.Add("ExecutionTime", ADP.TimerToMilliseconds(_executionTime));
        ht.Add("NetworkServerTime", ADP.TimerToMilliseconds(_networkServerTime));

        return ht;
    }


    internal void Reset()
    {
        _buffersReceived = 0;
        _buffersSent = 0;
        _bytesReceived = 0;
        _bytesSent = 0;
        _connectionTime = 0;
        _cursorOpens = 0;
        _executionTime = 0;
        _iduCount = 0;
        _iduRows = 0;
        _networkServerTime = 0;
        _preparedExecs = 0;
        _prepares = 0;
        _selectCount = 0;
        _selectRows = 0;
        _serverRoundtrips = 0;
        _sumResultSets = 0;
        _transactions = 0;
        _unpreparedExecs = 0;
        _waitForDoneAfterRow = false;
        _waitForReply = false;
        _startExecutionTimestamp = 0;
        _startNetworkServerTimestamp = 0;
    }

}

 

/// <summary>
/// SQL Server 提供程序统计信息
/// https://msdn.microsoft.com/zh-cn/library/7h2ahss8
/// </summary>
public class RetrieveStatisticsEnabled
{
    //TDS = Tabular Data Stream = 表格化数据流

    /// <summary>
    /// 从SQL Server接受的TDS包数
    /// </summary>
    public const string BuffersReceived = "BuffersReceived";

    /// <summary>
    /// 向SQL Server发送的TDS包数
    /// </summary>
    public const string BuffersSent = "BuffersSent";

    /// <summary>
    /// 接收的字节数
    /// 从SQL Server中接受的数据字节数
    /// </summary>
    public const string BytesReceived = "BytesReceived";

    /// <summary>
    /// 发送字节数
    /// 向SQL Server发送的数据字节数
    /// </summary>
    public const string BytesSent = "BytesSent";

    /// <summary>
    /// 打开连接的时间
    /// </summary>
    public const string ConnectionTime = "ConnectionTime";

    /// <summary>
    /// 执行时间
    /// 用于处理的总时间,包括等待服务器答复和执行代码的时间
    /// </summary>
    public const string ExecutionTime = "ExecutionTime";

    /// <summary>
    /// 等待服务器答复的总时间
    /// </summary>
    public const string NetworkServerTime = "NetworkServerTime";

    /// <summary>
    /// 通过连接执行SELECT语句的总数
    /// </summary>
    public const string SelectCount = "SelectCount";

    /// <summary>
    /// 行数
    /// 选中的行数,包括所有SQL语句生成的行数,即使调用者没有使用这些行
    /// </summary>
    public const string SelectRows = "SelectRows";

    /// <summary>
    /// 连接向服务器发送命令,并得到一个答复的次数
    /// </summary>
    public const string ServerRoundtrips = "ServerRoundtrips";

    /// <summary>
    /// 已使用的结果集总数
    /// </summary>
    public const string SumResultSets = "SumResultSets";

    /// <summary>
    /// 启动的用户事务总数。该值包括回传的事务个数
    /// </summary>
    public const string Transactions = "Transactions";

    /// <summary>
    /// 通过连接执行临时语句的总数
    /// </summary>
    public const string UnpreparedExecs = "UnpreparedExecs";



    /// <summary>
    /// 连接打开游标的次数
    /// </summary>
    public const string CursorOpens = "CursorOpens";
    /// <summary>
    /// 通过连接执行INSERT、DELETE和UPDATE语句的总数
    /// </summary>
    public const string IduCount = "IduCount";
    /// <summary>
    /// 通过连接执行INSERT、DELETE和UPDATE语句所影响的总行数
    /// </summary>
    public const string IduRows = "IduRows";
    /// <summary>
    /// 通过连接执行准备命令的个数
    /// </summary>
    public const string PreparedExecs = "PreparedExecs";
    /// <summary>
    /// 通过连接的准备命令个数
    /// </summary>
    public const string Prepares = "Prepares";


    /*
dict
Count = 18
["ExecutionTime"]: 15
["UnpreparedExecs"]: 1
["SelectRows"]: 22
["Prepares"]: 0
["BuffersSent"]: 1
["PreparedExecs"]: 0
["SelectCount"]: 3
["IduRows"]: 164
["BytesReceived"]: 943
["Transactions"]: 1
["IduCount"]: 1
["ServerRoundtrips"]: 1
["CursorOpens"]: 0
["SumResultSets"]: 1
["NetworkServerTime"]: 0
["ConnectionTime"]: 15293
["BytesSent"]: 123
["BuffersReceived"]: 1         
        */


    public static object GetStatisticsInfo(System.Collections.Hashtable ht)
    {
        object obj = new
        {
            BytesReceived = ht[BytesReceived].ToString(),
            BytesSent = ht[BytesSent].ToString(),
            BuffersReceived = ht[BuffersReceived].ToString(),
            BuffersSent = ht[BuffersSent].ToString(),

            ConnectionTime = ht[ConnectionTime].ToString(),
            ExecutionTime = ht[ExecutionTime].ToString(),
            NetworkServerTime = ht[NetworkServerTime].ToString(),

            SelectCount = ht[SelectCount].ToString(),
            SelectRows = ht[SelectRows].ToString(),
            ServerRoundtrips = ht[ServerRoundtrips].ToString(),
            SumResultSets = ht[SumResultSets].ToString(),
            Transactions = ht[Transactions].ToString(),
            UnpreparedExecs = ht[UnpreparedExecs].ToString(),

            CursorOpens = ht[CursorOpens].ToString(),
            IduCount = ht[IduCount].ToString(),
            IduRows = ht[IduRows].ToString(),
            PreparedExecs = ht[PreparedExecs].ToString(),
            Prepares = ht[Prepares].ToString()
        };

        return obj;
    }
}