How do you get a SQL query statistics info from code in C#?

Question

I'm running queries from Visual Studio 2015 using SqlCommand class. I need to know the time elapsed to run the query and the size of the result set. I know I can get that info with SQL Server Management Studio, but I don't how to get it from Visual Studio.

Possible duplicate of [Calculate execution time of a SQL query?](http://stackoverflow.com/questions/595762/calculate-execution-time-of-a-sql-query) — MethodMan, Aug 17 '16 at 18:10
Did you try handling event info on the connection? http://stackoverflow.com/a/1880507/2707705 — Biscuits, Aug 17 '16 at 18:12

score 2 · Accepted Answer · answered Aug 17 '16 at 18:10

You can use a StopWatch to time things:

StopWatch sw = new StopWatch();
sw.Start();
// Run query here
sw.Stop();

// Check sw.ElapsedMilliseconds or some other property you prefer

To get the size of the result set, you can count the iterations of datareader.Read() or other alternatives.

score 0 · Answer 2 · answered Aug 17 '16 at 18:09

First, go into the SQL Server Object Explorer in Visual Studio (View > SQL Server Object Explorer). Then select to query the database of your choosing. Right click > New Query. Before running your query, select 'Include Actual Execution Plan' near the top of the page: Picture of button near top of page

You can then run the query, and at the bottom of the page you will see multiple tabs like Results, Message, and Execution Plan. Select Execution Plan and you'll be able to see the cost of your query in a percentage, but much more information can be found by hovering over the cost or the clustered index scan portions.

I hope this helps!

FLICKER · Answer 3 · 2016-08-17T18:40:18.903

You can use SMO. Please try code below.

Server myServer = new Server("ServerName");
myServer.ConnectionContext.LoginSecure = true;
myServer.ConnectionContext.Connect();

Database db = myServer.Databases["dbName"];

Table myTable = db.Tables["TableName", "SchemaName"];

Statistic stat = default(Statistic);
stat = new Statistic(myTable, "StatisticName");
StatisticColumn statcol = default(StatisticColumn);
statcol = new StatisticColumn(stat, "FieldName");
stat.StatisticColumns.Add(statcol);
stat.Refresh();
var x = stat.LastUpdated;

You can find other properties as well. You also can Update statistics or even Create statistics and many more.

To find all statistics for a table, use below code, so you can loop through all statistics and get information for each of them:

var allStat = myTable.Statistics;

Take a look at below link for more information:

Statistic Class on MSDN

How do you get a SQL query statistics info from code in C#?

3 Answers3