Retrieving SQL Server Runtime Statistics
You want to get information about your connection to SQL Server, including bytes sent, bytes received, connection time, and execution time.
The .NET Framework 2.0 added support for runtime statistics with the SQL Server data provider. Runtime statistics expose information about processing queries in the database.
You must enable statistics by setting the StatisticsEnabled property of the SqlConnection object to true after the connection has been created. Once statistics are enabled, they can be retrieved into an IDictionary instance using the RetrieveStatistics() method of the SqlConnection object. The values in the dictionary are the statistic counter values, and are all of the long data type. The .NET Framework 3.5 SQL Server data provider makes 18 statistics available. The ResetStatistics() method of the SqlConnection object resets the counters.
All statistics are gathered on a per-connection basis and are valid for the current point in time. If you continue using the connection after retrieving statistics, you have to retrieve statistics again to get the current values.
For demo purpose, I just create C# console application and VB.NET.
Here is my code:
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = @"Data Source=localhost\sqlexpress;" +
"Integrated Security=SSPI;Initial Catalog=Northwind";
conn.StatisticsEnabled = true;
// do some work with the connection
SqlDataAdapter da =
new SqlDataAdapter("SELECT * FROM Customers", conn);
DataTable dt = new DataTable();
da.Fill(dt);
// get the statistics
IDictionary d = conn.RetrieveStatistics();
// move the dictionary keys to an array
string[] keys = new string[d.Count];
d.Keys.CopyTo(keys, 0);
// iterate over the dictionary displaying the key-value pair
for (int i = 0; i < d.Count; i++)
Console.WriteLine("{0}\t{1}",
keys[i], (long)d[keys[i]]);
}
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey();
}
Using SqlCon As New SqlConnection
SqlCon.ConnectionString = "Data Source=localhost\sqlexpress;" _
+ "Integrated Security=SSPI;Initial Catalog=Northwind"
SqlCon.StatisticsEnabled = True
Dim dap As New SqlDataAdapter("SELECT * FROM Customers", SqlCon)
Dim dt As New DataTable
dap.Fill(dt)
Dim dic As IDictionary = SqlCon.RetrieveStatistics()
Dim key As String() = New String(dic.Count) {}
dic.Keys.CopyTo(key, 0)
For i As Integer = 0 To dic.Count - 1
Console.WriteLine("{0}" & vbTab & "{1}", key(i), DirectCast(dic(key(i)), Long))
Next
Console.Write(Environment.NewLine + "Press any key to continue.")
Console.Read()
End Using
The solution creates a Connection and enables statistics by setting the EnableStatistics property of the Connection object to true. A DataAdapter is created and used to fill a DataTableNorthwind. Statistics are retrieved into a dictionary using the RetrieveStatistics() method of the Connection object and then output to the console. with all records in the Cusomers table in Northwind. Statistics are retrieved into a dictionary using the RetrieveStatistics() method of the Connection object and then output to the console.
Regards,
StatisticsEnabled.rar (62.54 kb)
Subscribe
Facebook
twitter