Working with SQL Server Provider Statistics
Working with SQL Server Provider Statistics
The SQL Server provider has built in run-time statistics that can be enabled on a valid connection object. Currently, there are more than 20 statistical values available that can help you understand what's happening in your application. They are described in Table 4-2.
| Statistic Name | Description |
|---|---|
| BuffersReceived | The number of Tabular Data Stream (TDS) packets that this provider received from SQL Server after the application has started and statistics have been enabled. |
| BuffersSent | The number of TDS packets that this provider sent to SQL Server after statistics have been enabled. |
| BytesReceived | The number of data bytes that this provider received from SQL Server after the application has started and statistics have been enabled. |
| BytesSent | The number of data bytes that this provider sent to SQL Server after the application started and statistics have been enabled. |
| ConnectionTime | The time that the connection has been opened after statistics have been enabled. |
| CursorFetchCount | The number of fetches done against server cursors after the application has started and the statistics have been enabled. |
| CursorFetchTime | The total time it took fetches against server cursors to complete after the application has started and statistics have been enabled. |
| CursorOpens | Number of times a cursor was open through the connection after the application has started and statistics have been enabled. |
| CursorUsed | Number of rows actually retrieved through the driver from cursors after the application has started and statistics have been enabled. |
| ExecutionTime | The total time that the provider spent processing after the 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. |
| IduCount | Total quantity of INSERT, DELETE, and UPDATE statements executed through the connection after the application has started and statistics have been enabled. |
| IduRows | Total quantity of rows affected by INSERT, DELETE, and UPDATE statements executed through the connection after the application has started and statistics have been enabled. |
| NetworkServerTime | The total time the provider spent waiting for replies from the server after the application has started and statistics have been enabled. |
| PreparedExecs | The number of prepared commands executed through the connection after the application has started and statistics have been enabled. |
| Prepares | The number of statements prepared through the connection after the application has started and statistics have been enabled. |
| SelectCount | Quantity of SELECT statements executed through the connection after the application has started and statistics have been enabled. |
| SelectRows | Quantity of rows selected after the application has started and statistics have been enabled. This includes all rows generated by SQL statements, even if they weren't consumed by the caller. |
| ServerRoundtrips | The number of times the connection sent commands to the server and got a reply back after the application has started and statistics have been enabled. |
| SumResultSets | Quantity of result sets that have been used after the application has started and statistics have been enabled. |
| Transactions | Quantity of user transactions started after the application has started and statistics have been enabled. This count includes rollbacks. |
| UnpreparedExecs | The number of unprepared statements executed through the connection after the application has started and statistics have been enabled. |
To access the statistics, you must have a valid connection object, and the statistics must be enabled. To demonstrate the use statistics, create a Windows application, and add a Button control and two DataGridView controls. Change the Text property of the Button control to "Statistic Test". Double-click the Button control and add the following code to enable the statistics, run a simple query, and retrieve the statistics.
Visual Basic
Imports System.Configuration Imports System.Data Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim authors As New DataTable() Dim pubSettings As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("PubsString") Using cn As New SqlConnection() cn.ConnectionString = pubSettings.ConnectionString cn.StatisticsEnabled = True Using cmd As SqlCommand = cn.CreateCommand() cn.Open() cmd.CommandText = "SELECT * FROM AUTHORS" authors.Load(cmd.ExecuteReader()) DataGridView1.DataSource = authors End Using Dim stats As New ArrayList(cn.RetrieveStatistics()) DataGridView2.DataSource = stats End Using End Sub End Class
C#
using System; using System.Data; using System.Windows.Forms; using System.Configuration; using System.Data.SqlClient; using System.Collections; namespace StatisticsTest { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Button1_Click(object sender, EventArgs e) { DataTable authors = new DataTable(); ConnectionStringSettings pubSettings = ConfigurationManager.ConnectionStrings["PubsString"]; using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = pubSettings.ConnectionString; cn.StatisticsEnabled = true; using (SqlCommand cmd = cn.CreateCommand()) { cn.Open(); cmd.CommandText = "SELECT * FROM AUTHORS"; authors.Load(cmd.ExecuteReader()); DataGridView1.DataSource = authors; } ArrayList stats = new ArrayList(cn.RetrieveStatistics()); DataGridView2.DataSource = stats; } } } } 
