Visual Basic 1002005 [A Developers Notebook] [Electronic resources] نسخه متنی

5.6. Collect Statistics on Your Data Connections

Most programmers like to
look at statistics. Considered carefully, they can suggest the
underlying cause of a long-standing problem, explain the performance
problems of an application, or suggest possible optimization
techniques. If you're using the SQL Server provider,
you can make use of a new SqlConnection.RetrieveStatistics() method to get a hashtable with a slew of diagnostic
details about your database connection.

Note: Want to find out what's really going on
while you're connected to a database? In . NET 2.0,
you can get ahold of much more information, but only if
you're using SQL Server.

5.6.1. How do I do that?

Before you can call RetrieveStatistics( ), you
need to instruct it to collect statistics by setting the

SqlConnection.StatisticsEnabled
property to TRue. Once you take this step, the
SqlConnection class will gather statistics for
every database command you execute over the connection. If you
perform multiple operations with the same connection, the statistics
will be cumulative, even if you close the connection between each
operation.

To take a look at the statistics at any time, you call the
RetrieveStatistics( ) method to retrieve a
hashtable containing the accumulated data. The hashtable indexes its
members with a descriptive name. For example, to retrieve the number
of transactions you've performed,
you'd write this code:

Dim Stats as Hashtable = con.RetrieveStatistics( )
Console.Writeline(Stats("Transactions"))

To get a good idea of the different statistics available, try running
Example 5-6, a console application that iterates
over the statistics collection and displays the key name and value of
each statistic it contains.

Example 5-6. Retrieving all the connection statistics

Imports System.Data.SqlClient
Module StatisticsTest
Private ConnectString As String = _
"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"
Private con As New SqlConnection(ConnectString)
Public Sub Main( )
' Turn on statistics collection.
con.StatisticsEnabled = True
' Perform two sample commands.
SampleCommand( )
SampleCommand( )
' Retrive the hashtable with statistics.
Dim Stats As Hashtable = con.RetrieveStatistics( )
' Display all the statistics.
For Each Key As String In Stats.Keys
Console.WriteLine(Key & " = " & Stats(Key))
Next
End Sub
Private Sub SampleCommand( )
con.Open( )
Dim cmd As New SqlCommand("SELECT * FROM Customers", con)
Dim reader As SqlDataReader = cmd.ExecuteReader( )
reader.Close( )
con.Close( )
End Sub
End Module

Here's the complete list of statistics produced by
this code:

NetworkServerTime = 18
BytesReceived = 46248
Transactions = 0
SumResultSets = 2
SelectCount = 2
PreparedExecs = 0
ConnectionTime = 13
CursorFetchCount = 0
CursorUsed = 0
Prepares = 0
CursorFetchTime = 0
UnpreparedExecs = 2
SelectRows = 182
ServerRoundtrips = 2
CursorOpens = 0
BuffersSent = 2
ExecutionTime = 725
BytesSent = 108
BuffersReceived = 6
IduRows = 0
IduCount = 0

To reset the values of the statistics collection to zero at any time,
simply call the ResetStatistics() method:

con.ResetStatistics( )

5.6.2. What about...

...making sense of the various statistics gathered and putting them
to use? Unfortunately, the MSDN Help doesn't yet
provide the full lowdown on the SQL Server statistics. However,
several statistics are particularly useful and not too difficult to
interpret:

BytesReceived

Gives a snapshot of the total number
of bytes retrieved from the database server.

ServerRoundtrips

Indicates the number of distinct
commands you've executed.

ConnectionTime

Indicates the cumulative amount of
time the connection has been open.

SumResultSets

Indicates the number of queries
you've performed.

SelectRows

Records
the total number of rows retrieved in every query
you've executed. (In the previous example this is
182, because each query retrieved 91 rows.)

And for an example where statistics are used to profile different
approaches to database code, refer to the next lab,
"Batch DataAdapter Commands for Better
Performance."