SET SHOWPLAN_ALL

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi all,

I want to retrieve the execution plan for a SQL statement, so need to
prepend the SET SHOWPLAN_ALL ON command to the statement before submission,
but am not sure how to do so. Any ideas?

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SET SHOWPLAN_ALL ON; SELECT * FROM some_table; SET
SHOWPLAN_ALL OFF";

IDataReader reader = command.ExecuteReader();
etc...doesn't work, because SHOWPLAN_ALL must be the only command in a
batch?

Thanks in advance,
Ben
 
Hi all,

Ok, just make it part of a transaction...:)

My first effort:


SqlConnection connection = new SqlConnection(this.connectionString);
connection.Open();
SqlTransaction transaction =
connection.BeginTransaction(IsolationLevel.ReadUncommitted, "Request Query
Plan");

using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "set showplan_all on;";
command.CommandType = CommandType.Text;
command.Transaction = transaction;
command.ExecuteNonQuery();
}

DataTable dt = new DataTable();

using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = statement;
command.CommandType = CommandType.Text;
command.Transaction = transaction;
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
}

using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "set showplan_all off;";
command.CommandType = CommandType.Text;
command.Transaction = transaction;
command.ExecuteNonQuery();
}

transaction.Commit();
connection.Close();
return dt;

Stay cool,
Ben
 
Back
Top