Section 5.6. Collect Statistics on Your Data Connections

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."