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.

Table 4-2: Available Statistical Values

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.

image from book

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 
image from book

image from book

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;          }       }    } } 
image from book

The result of the query was placed into the first DataGridView control, and the second DataGridView control contains the statistical information. The results are shown in Figure 4-6.

image from book
Figure 4-6: The statistics have been retrieved and are displayed in the second DataGridView control.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net