Connection statistics

SqlConnection object in ADO.Net 2.0 is packed with additional handy feature to get the statistics data of the object. We can obtain ServerRoundtrips, BytesSent, ExecutionTime, ConnectionTime, etc taken for a database operation. This data will help us to fine tune the performance of the underlying data access layer operation. By default, this feature is not enabled for a connection object. We can enable it by setting StatisticsEnabled property of connection object to true. The RetrieveStatistics() method of the SqlConnection exposes all the statistics values through a Dictonary object.

When this feature is switched on it will hit the performance to some extent and it should be disabled once we done with the fine tuning or analysis.

Implementation

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL Connection String"].ConnectionString);

            con.StatisticsEnabled = true;

            con.Open();

//DB Code goes here

 IDictionary dict = con.RetrieveStatistics();         

            foreach (DictionaryEntry entry in dict)

            {

                Response.Write(entry.Key.ToString() + ":" + entry.Value.ToString() + "<BR>");          

            }

The output will be (For a select query),

NetworkServerTime:15

BytesReceived:6066

UnpreparedExecs:2

SumResultSets:1

SelectCount:1

PreparedExecs:0

ConnectionTime:15

ExecutionTime:15

Prepares:0

BuffersSent:2

SelectRows:42

ServerRoundtrips:2

CursorOpens:0

Transactions:1

BytesSent:468

BuffersReceived:2

IduRows:1

IduCount:1

TransactionScope Object

TransactionScope is one of the new objects that are packed with ADO.Net 2.0 which is used for distributed transaction. With this object we can maintain the transaction across multiple databases or servers with very less effort. Using TransactionScope object we can maintain implicit transaction where transactions are automatically maintained. Implicit transaction are maintained between TransactionScope object instantiation and TransactionScope.Dispose() method. So, the best way of implementing TransactionScope object will be with the use of “using” statement. If we use this construct we no need to worry of calling Dispose explicitly.

using (TransactionScope scope = new TransactionScope())

{

//Code

}

If there are any exception in the using block the transaction will be automatically rolled back else we should call Complete() method to commit the transaction. The best way is to use Complete() method as the last statement in using block. Calling this method indicates the operations are consistent and it is ready to be committed. The real commit will take place when Dispose() method is called and indicates the distributed transaction is complete and it cannot be used after that. However, failing to call this method will abort the transaction because the transaction manager will interpret that there is a system failure or exception.

Implementation

  string connectString1 = "Data Source=BABULIVES;Initial Catalog=master;Integrated Security=True";

    string connectString2 = "Data Source=BABULIVES;Initial Catalog=tempdb;Integrated Security=True";

    string commandText1 = "Delete from test";

    string commandText2 = "Delete from test";

    using (TransactionScope scope = new TransactionScope())

    {

        using (SqlConnection connection1 = new SqlConnection(connectString1))

        {

            try

            {

                connection1.Open();                                         

                SqlCommand command1 = new SqlCommand(commandText1, connection1);

                command1.ExecuteNonQuery();

                using (SqlConnection connection2 = new SqlConnection(connectString2))

                    try

                    { 

                        SqlCommand command2 = new SqlCommand(commandText2, connection2);

                        command2.ExecuteNonQuery();                      

                    }

                    catch (Exception ex)

                    {

                        Response.Write("Exception Message2: {0}", ex.Message);

                    }

            }

            catch (Exception ex)

            {

                Response.Write("Exception Message1: {0}", ex.Message);

            }

        }

        // The Complete method commits the transaction. If an exception has been thrown,

        // Complete is not called and the transaction is rolled back.

        scope.Complete();

    }

In the above code, when the first connection (connection1) is opened the transaction will be a lightweight transaction. On successful completion and moving to next connection (connection2) the transaction will be a full distributed transaction.

Error and Resolution

When we use the distributed transaction we may get the following error,

MSDTC on server 'SERVERNAME' is unavailable.

Cause

The MSDTC service is not started on the server.

Resolution

Go to services and start Distributed Transaction Coordinator service.

Steps

CONTROL PANEL à ADMINISTRATIVE TOOLS à SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK on it and select Start.

Else

Type Services.msc on RUN command. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) à Start.