Starting with .NET Framework version 2.0, the .NET Framework Data Provider for SQL Server supports run-time statistics. You must enable statistics by setting the StatisticsEnabled property of the SqlConnection object to True after you have a valid connection object created.
After statistics are enabled, you can review them as a "snapshot in time" by retrieving an IDictionary reference via the RetrieveStatistics method of the SqlConnection object. You enumerate through the list as a set of name/value pair dictionary entries. These name/value pairs are unordered. At any time, you can call the ResetStatistics method of the SqlConnection object to reset the counters.
If statistic gathering has not been enabled, an exception is not generated. In addition, if RetrieveStatistics is called without StatisticsEnabled having been called first, the values retrieved are the initial values for each entry. If you enable statistics, run your application for a while, and then disable statistics, the values retrieved will reflect the values collected up to the point where statistics were disabled.
All statistical values gathered are on a per-connection basis.
Statistical Values Available
Currently there are 18 different items available from the Microsoft SQL Server provider. The number of items available can be accessed via the Count property of the IDictionary interface reference returned by RetrieveStatistics. All of the counters for provider statistics use the common language runtime Int64 type (long in C# and Visual Basic), which is 64 bits wide. The maximum value of the int64 data type, as defined by the int64.MaxValue field, is ((2^63)-1)). When the values for the counters reach this maximum value, they should no longer be considered accurate. This means that int64.MaxValue-1((2^63)-2) is effectively the greatest valid value for any statistic.
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 once the application has started using the provider and has enabled statistics. This includes FETCH statements to retrieve rows from cursors, and the count for SELECT statements is updated when the end of a SqlDataReader is reached.
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 System.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
C#
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespaceCS_Stats_Console_GetValue
{
classProgram
{
staticvoidMain(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();
}
}
privatestaticstringGetConnectionString()
{
// 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
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 System.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
C#
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespaceCS_Stats_Console_GetAll
{
classProgram
{
staticvoidMain(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();
}
}
privatestaticstringGetConnectionString()
{
// To avoid storing the connection string in your code,// you can retrieve it from a configuration file.return"...";
}
}
}
Relational databases store many different types of data for many different types of applications. Some databases have thousands of users and some have just one user. Some tables have tens of rows while others have millions of rows. Some data is unique while other data is duplicated. All of these variables mean that database management systems (DBMSs) need systems to understand the data and the queries that are running against this data to ensure optimum performance. When you perform tune a database, it's im