Section 5.4. Get Database Statistics

5.4. Get Database Statistics

In tuning your application, it can be very helpful to know how many times you are going to the server, and how much time is being spent doing the work of your application.

---

Note: The RetrieveStatistics method allows you to obtain diagnostic details with a single line of code.

---

If you are using SQL Server, you can use the new RetrieveStatistics method to get diagnostic details.

5.4.1. How do I do that?

Create a new C# Windows application (named DataStats). On the form, drag a button and a listbox. Set the button text to Test and set the listbox name to lbStats.

Change the System.Collections.Generic using statement to System.Collections and add a System.Data.SqlClient using statement.

Double-click the button, and in the event handler add the code shown in Example 5-3.

Example 5-3. Measuring SQL statistics

private void button1_Click(object sender, EventArgs e) {    string connString =      "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";    SqlConnection conn = new SqlConnection(connString);    conn.StatisticsEnabled = true;    conn.Open( );    SqlCommand cmd = new SqlCommand( );    cmd.Connection = conn;         cmd.CommandText = "Select * from Customers";    SqlDataReader rdr = cmd.ExecuteReader( );    rdr.Close( );    cmd.CommandText = "Select * from Products";    rdr = cmd.ExecuteReader( );    rdr.Close( );      for (int i = 0; i < 999; i++)    {       cmd.CommandText = "Select * from Customers";       rdr = cmd.ExecuteReader( );       rdr.Close( );       cmd.CommandText = "Select * from Products";       rdr.Close( );       rdr = cmd.ExecuteReader( );       rdr.Close( );    }         conn.Close( );         IDictionary dict = conn.RetrieveStatistics( );    foreach (string key in dict.Keys)       {       this.lbStats.Items.Add(key + " = " + dict[key].ToString( ));    } }

The results of running this are shown in Figure 5-23.

Figure 5-23. SQL statistics

5.4.2. What just happened?

In the button-click handler, you hand-created an SqlConnection object, and you set its StatisticsEnabled property to true. Then you issued two commands, retrieving the results in an SqlDataReader (but you ignored the results).

Next, you repeated this sequence for a total of 1,000 iterations (it's hard to make SQL Server do enough work to register meaningful statistics!).

After you closed the connection you asked the connection for its statistics, which are returned as an IDictionary:

IDictionary dict = conn.RetrieveStatistics( );

You iterated through the dictionary, adding each key and its value to the listbox.

5.4.3. What about . . .

...resetting the statistics? How do I do that?

Call ResetStatistics on the connection object.

5.4.4. Where can I learn more?

Read through the description of the RetrieveStatistics method in the MSDN Library. For advanced SQL the definitive guide is The Guru's Guide to Transact SQL by Ken Henderson (Addison-Wesley).