CODEWITHSUNDEEP
c#.netsql-serverperformanceado.net
Guru Kara
Guru Kara

Reputation: 6462

Get the execution time of a ADO.NET SQL Command

I have been searching over to find if there is any easy way to get the Execution time of a ADO.NET command object.

I know i can manually do a StopWatch start and stop. But wanted to if there are any easy way to do it in ADO.NET

Upvotes: 10

Views: 8436

Answers (3)

Evk
Evk

Reputation: 101483

There is a way, but using SqlConnection, not command object. Example:

using (var c = new SqlConnection(connectionString)) {
    // important
    c.StatisticsEnabled = true;
    c.Open();
    using (var cmd = new SqlCommand("select * from Error", c)) {
        cmd.ExecuteReader().Dispose();
    }       
    var stats = c.RetrieveStatistics();
    var firstCommandExecutionTimeInMs = (long) stats["ExecutionTime"];
    // reset for next command
    c.ResetStatistics();
    using (var cmd = new SqlCommand("select * from Code", c))
    {
        cmd.ExecuteReader().Dispose();
    }
    stats = c.RetrieveStatistics();
    var secondCommandExecutionTimeInMs = (long)stats["ExecutionTime"];
 }

Here you can find what other values are contained inside dictionary returned by RetrieveStatistics.

Note that those values represent client-side statistics (basically internals of ADO.NET measure them), but seems you asked for analog of Stopwatch - I think that's fine.

Upvotes: 11

Remus Rusanu
Remus Rusanu

Reputation: 294297

I suggest you move to SQL Server 2016 and use the Query Store feature. This will track execution time and performance changes over time for each query you submit. Requires no changes in your application. Track all queries, including those executed inside stored procedures. Track any application, not only your own. Is available in all editions, including Express, and including the Azure SQL DB Service.

If you track on the client side, you must measure the time yourself, using a wall clock. I would add and expose performance counters and then use the performance counters infrastructure to capture and store the measurements.

As a side not, simply tracking the execution time of a batch sent to SQL Server yields very coarse performance info and is seldom actionable. Read How to analyse SQL Server performance.

Upvotes: 2

user6996876
user6996876

Reputation:

The approach from the answer of @Evk is very interesting and smart: it's working client side and one of the main key of such statistics is in fact NetworkServerTime, which

Returns the cumulative amount of time (in milliseconds) that the provider spent waiting for replies from the server once the application has started using the provider and has enabled statistics.

so it includes the network time from the DB server to the ADO NET client.

An alternative, more DB server oriented, would be running SET STATISTICS TIME ON and then retrieve the InfoMessage.

A draft of the code of the delegate (where I'm simply writing to the debug console, but you may want to replace it with a StringBuilder Append)

internal static void TrackInfo(object sender, SqlInfoMessageEventArgs e)
{
    Debug.WriteLine(e.Message);
    foreach (var element in e.Errors) {
        Debug.WriteLine(element.ToString());
    }
}

and usage

conn.InfoMessage +=  TrackInfo;
using (var cmd = new SqlCommand(@"SET STATISTICS TIME ON", conn)) {
    cmd.ExecuteNonQuery();
}
using (var cmd = new SqlCommand(yourQuery, conn)) {
    var RD = cmd.ExecuteReader();
    while (RD.Read()) {
        // read the columns
    }
}

Upvotes: 5

Related Questions