void HandleTraceEvent(MyTraceEventArgs e)
{
if (e.EventClass == TraceEventClass.QueryBegin && e.DatabaseName == liveDB.Name)
{
if (MDXHelper.IsDrillthroughQuery(e.TextData))
{
lock (listDrillthroughQueries)
{
if (!listDrillthroughQueries.Contains(e.SessionIdOrSpid))
{
listDrillthroughQueries.Add(e.SessionIdOrSpid);
}
}
}
else
{
iQueries++;
}
}
else if (e.EventClass == TraceEventClass.QueryEnd && e.DatabaseName == liveDB.Name)
{
lock (listDrillthroughQueries)
{
if (listDrillthroughQueries.Contains(e.SessionIdOrSpid))
{
listDrillthroughQueries.Remove(e.SessionIdOrSpid);
}
}
}
else if (e.EventClass == TraceEventClass.QuerySubcubeVerbose && e.ObjectPath.StartsWith(sCubePath) && !listDrillthroughQueries.Contains(e.SessionIdOrSpid))
{
string sMeasureGroupID = e.ObjectPath.Substring(sCubePath.Length);
MeasureGroup mg = cloneCube.MeasureGroups.Find(sMeasureGroupID);
MeasureGroup liveMG = this.liveCube.MeasureGroups.Find(sMeasureGroupID);
if (liveMG == null)
{
return;
}
List <CubeAttribute> attributesSliced = GetCubeAttributesWithSlice(e.TextData, liveMG);
lock (dictHitIndexes)
{
foreach (CubeAttribute ca in attributesSliced)
{
if (!dictHitIndexes.ContainsKey(ca))
{
dictHitIndexes.Add(ca, 1);
}
else
{
dictHitIndexes[ca]++;
}
}
}
iQuerySubcubeVerboseEvents++;
}
}
void trc_OnEvent(object sender, TraceEventArgs e)
{
try
{
MyTraceEventArgs args = new MyTraceEventArgs(e);
HandleTraceEvent(args);
}
catch (Exception ex)
{
try
{
bStopped = true;
FinishExecute(false);
}
catch { }
MessageBox.Show("There was a problem receiving a trace event: " + ex.Message);
}
}
void trc_OnEvent(object sender, TraceEventArgs e)
{
try
{
lock (this) //will this help ensure we run the code in serial, not in parallel?? it's important for this code to load the trace events in serial and in order
{
MyTraceEventArgs args = new MyTraceEventArgs(e);
HandleTraceEvent(args);
}
}
catch (Exception ex)
{
try
{
bStopped = true;
FinishExecute(false);
}
catch { }
MessageBox.Show("There was a problem receiving a trace event: " + ex.Message);
}
}
private void Execute()
{
dictHitIndexes = new Dictionary <CubeAttribute, int>();
listDrillthroughQueries = new List <string>();
bExecuting = true;
this.radioTraceTypeLive.Enabled = false;
this.radioTraceTypeSQL.Enabled = false;
this.btnSQLConnection.Enabled = false;
this.dtTraceStarted = DateTime.Now;
this.Width = this.iWindowFullWidth;
this.Height = this.iWindowShortHeight;
this.buttonCancel.Visible = false;
this.buttonOK.Visible = false;
this.grpProgress.Visible = true;
this.treeViewAggregation.Visible = false;
this.lblUnusedAggregationsToDelete.Visible = false;
this.iQueries = 0;
this.dtTraceStarted = DateTime.Now;
timer1_Tick(null, null);
this.btnExecute.Enabled = false;
Application.DoEvents();
try
{
if (this.radioTraceTypeLive.Checked)
{
timer1.Enabled = true;
string sTraceID = "BIDS Helper Delete Unused Indexes Trace " + System.Guid.NewGuid().ToString();
trc = liveServer.Traces.Add(sTraceID, sTraceID);
trc.OnEvent += new TraceEventHandler(trc_OnEvent);
trc.Stopped += new TraceStoppedEventHandler(trc_Stopped);
trc.AutoRestart = true;
TraceEvent te;
te = trc.Events.Add(TraceEventClass.QueryBegin);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.SessionID);
te = trc.Events.Add(TraceEventClass.QueryEnd);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.SessionID);
te = trc.Events.Add(TraceEventClass.QuerySubcubeVerbose);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.ObjectPath);
te.Columns.Add(TraceColumn.SessionID);
trc.Update();
trc.Start();
this.btnExecute.Text = "Stop Trace";
this.btnExecute.Enabled = true;
}
else
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
bool bHasRowNumberColumn = false;
try
{
//test that this table has the RowNumber column
SqlCommand cmdCheckRowNumber = new SqlCommand();
cmdCheckRowNumber.Connection = conn;
cmdCheckRowNumber.CommandText = "select top 1 RowNumber from " + Table + " (nolock)";
cmdCheckRowNumber.CommandTimeout = 0;
cmdCheckRowNumber.ExecuteNonQuery();
bHasRowNumberColumn = true;
}
catch { }
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from " + Table + " (nolock) order by CurrentTime" + (bHasRowNumberColumn ? ", RowNumber" : ""); //just select everything and filter in .NET (which allows us to throw better error messages if a column is missing... must be ordered so that we can recognize drillthrough queries and skip the query subcube verbose events until query end... ordering by CurrentTime then RowNumber allows this to work in ASTrace archive tables which have overlapping RowNumber ranges
cmd.CommandTimeout = 0;
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
if (!ReaderContainsColumn(reader, "EventClass"))
{
MessageBox.Show("Table " + Table + " must contain EventClass column");
}
else if (!ReaderContainsColumn(reader, "TextData"))
{
MessageBox.Show("Table " + Table + " must contain TextData column");
}
else if (!ReaderContainsColumn(reader, "ObjectPath"))
{
MessageBox.Show("Table " + Table + " must contain ObjectPath column");
}
else if (!ReaderContainsColumn(reader, "SessionID") && !ReaderContainsColumn(reader, "SPID"))
{
MessageBox.Show("Table " + Table + " must contain SessionID or SPID column");
}
else
{
if (ReaderContainsColumn(reader, "SessionID"))
{
sSessionIDColumnName = "SessionID";
}
else
{
sSessionIDColumnName = "SPID";
}
this.dtTraceStarted = DateTime.Now;
string sDateColumnName = "CurrentTime";
DateTime dtMin = DateTime.MaxValue;
DateTime dtMax = DateTime.MinValue;
Int64 iRowCount = 0;
while (reader.Read())
{
MyTraceEventArgs arg = new MyTraceEventArgs(reader);
HandleTraceEvent(arg);
if (!string.IsNullOrEmpty(sDateColumnName) && !Convert.IsDBNull(reader[sDateColumnName]))
{
DateTime dt = Convert.ToDateTime(reader[sDateColumnName]);
if (dtMin > dt)
{
dtMin = dt;
}
if (dtMax < dt)
{
dtMax = dt;
}
long iSecondsDiff = Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Second, dtMin, dtMax, Microsoft.VisualBasic.FirstDayOfWeek.System, Microsoft.VisualBasic.FirstWeekOfYear.Jan1);
this.dtTraceStarted = Microsoft.VisualBasic.DateAndTime.DateAdd(Microsoft.VisualBasic.DateInterval.Second, -iSecondsDiff, DateTime.Now);
}
if (iRowCount++ % 500 == 0)
{
timer1_Tick(null, null);
Application.DoEvents();
}
}
timer1_Tick(null, null);
Application.DoEvents();
FinishExecute(true);
return;
}
FinishExecute(false);
}
finally
{
try
{
reader.Close();
}
catch { }
}
}
}
catch (Exception ex)
{
FinishExecute(false);
MessageBox.Show(ex.Message);
}
}
void HandleTraceEvent(MyTraceEventArgs e)
{
if (e.EventClass == TraceEventClass.QueryEnd &&
(e.EventSubclass == TraceEventSubclass.MdxQuery ||
e.EventSubclass == TraceEventSubclass.DAXQuery) &&
e.DatabaseName == liveDB.Name)
{
iQueries++;
}
else if (e.EventClass == TraceEventClass.GetDataFromAggregation && e.DatabaseName == liveDB.Name && e.ObjectPath.StartsWith(sCubePath))
{
string sMeasureGroupID = e.ObjectPath.Substring(sCubePath.Length);
string sPartitionID = sMeasureGroupID.Substring(sMeasureGroupID.IndexOf('.') + 1);
sMeasureGroupID = sMeasureGroupID.Substring(0, sMeasureGroupID.IndexOf('.'));
MeasureGroup mg = cloneCube.MeasureGroups.Find(sMeasureGroupID);
if (cloneMG == null) //if checking for hit aggs on entire cube
{
if (mg == null)
{
return;
}
}
else
{
if (mg == null || sMeasureGroupID != cloneMG.ID)
{
return;
}
}
MeasureGroup liveMG = this.liveCube.MeasureGroups.Find(sMeasureGroupID);
if (liveMG == null)
{
return;
}
Partition livePartition = liveMG.Partitions.Find(sPartitionID);
if (livePartition == null)
{
return;
}
string sAggID = e.TextData.Split(new char[] { '\r', '\n' })[0];
if (livePartition.AggregationDesign == null)
{
return;
}
Aggregation liveAgg = livePartition.AggregationDesign.Aggregations.Find(sAggID);
if (liveAgg == null)
{
return;
}
//found this aggregation on the live cube... now find the equivalent agg in the cloned (local) cube
AggregationDesign cloneAggDesign = mg.AggregationDesigns.Find(liveAgg.Parent.ID);
if (cloneAggDesign == null)
{
return;
}
Aggregation cloneAgg = cloneAggDesign.Aggregations.Find(sAggID);
if (cloneAgg == null)
{
return;
}
lock (dictHitAggs)
{
if (dictHitAggs.ContainsKey(cloneAgg))
{
dictHitAggs[cloneAgg]++;
}
else
{
dictHitAggs.Add(cloneAgg, 1);
}
}
iAggHits++;
}
}
void HandleTraceEvent(MyTraceEventArgs e)
{
if (e.EventClass == TraceEventClass.QueryBegin && e.DatabaseName == liveDB.Name)
{
if (MDXHelper.IsDrillthroughQuery(e.TextData))
{
lock (listDrillthroughQueries)
{
if (!listDrillthroughQueries.Contains(e.SessionIdOrSpid))
listDrillthroughQueries.Add(e.SessionIdOrSpid);
}
}
else
{
iQueries++;
}
}
else if (e.EventClass == TraceEventClass.QueryEnd && e.DatabaseName == liveDB.Name)
{
lock (listDrillthroughQueries)
{
if (listDrillthroughQueries.Contains(e.SessionIdOrSpid))
listDrillthroughQueries.Remove(e.SessionIdOrSpid);
}
}
else if (e.EventClass == TraceEventClass.QuerySubcubeVerbose && e.ObjectPath.StartsWith(sCubePath) && !listDrillthroughQueries.Contains(e.SessionIdOrSpid))
{
string sMeasureGroupID = e.ObjectPath.Substring(sCubePath.Length);
MeasureGroup mg = cloneCube.MeasureGroups.Find(sMeasureGroupID);
MeasureGroup liveMG = this.liveCube.MeasureGroups.Find(sMeasureGroupID);
if (liveMG == null) return;
List<CubeAttribute> attributesSliced = GetCubeAttributesWithSlice(e.TextData, liveMG);
lock (dictHitIndexes)
{
foreach (CubeAttribute ca in attributesSliced)
{
if (!dictHitIndexes.ContainsKey(ca))
dictHitIndexes.Add(ca, 1);
else
dictHitIndexes[ca]++;
}
}
iQuerySubcubeVerboseEvents++;
}
}
private void Execute()
{
dictHitAggs = new Dictionary <Aggregation, int>();
bExecuting = true;
this.radioTraceTypeLive.Enabled = false;
this.radioTraceTypeSQL.Enabled = false;
this.btnSQLConnection.Enabled = false;
this.dtTraceStarted = DateTime.Now;
this.Width = this.iWindowFullWidth;
this.Height = this.iWindowShortHeight;
this.buttonCancel.Visible = false;
this.buttonOK.Visible = false;
this.grpProgress.Visible = true;
this.treeViewAggregation.Visible = false;
this.lblUnusedAggregationsToDelete.Visible = false;
this.iAggHits = 0;
this.iQueries = 0;
this.dtTraceStarted = DateTime.Now;
timer1_Tick(null, null);
this.btnExecute.Enabled = false;
Application.DoEvents();
try
{
if (this.radioTraceTypeLive.Checked)
{
timer1.Enabled = true;
string sTraceID = "BIDS Helper Delete Unused Aggs Trace " + System.Guid.NewGuid().ToString();
trc = liveServer.Traces.Add(sTraceID, sTraceID);
trc.OnEvent += new TraceEventHandler(trc_OnEvent);
trc.Stopped += new TraceStoppedEventHandler(trc_Stopped);
trc.AutoRestart = true;
TraceEvent te;
te = trc.Events.Add(TraceEventClass.QueryEnd);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.EventSubclass);
te = trc.Events.Add(TraceEventClass.GetDataFromAggregation);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.ObjectPath);
trc.Update();
trc.Start();
this.btnExecute.Text = "Stop Trace";
this.btnExecute.Enabled = true;
}
else
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from " + Table; //just select everything and filter in .NET (which allows us to throw better error messages if a column is missing
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
if (!ReaderContainsColumn(reader, "EventClass"))
{
MessageBox.Show("Table " + Table + " must contain EventClass column");
}
else if (!ReaderContainsColumn(reader, "EventSubclass"))
{
MessageBox.Show("Table " + Table + " must contain EventSubclass column");
}
else if (!ReaderContainsColumn(reader, "TextData"))
{
MessageBox.Show("Table " + Table + " must contain TextData column");
}
else if (!ReaderContainsColumn(reader, "ObjectPath"))
{
MessageBox.Show("Table " + Table + " must contain ObjectPath column");
}
else
{
this.dtTraceStarted = DateTime.Now;
string sDateColumnName = "";
if (ReaderContainsColumn(reader, "StartTime"))
{
sDateColumnName = "StartTime";
}
else if (ReaderContainsColumn(reader, "CurrentTime"))
{
sDateColumnName = "CurrentTime";
}
DateTime dtMin = DateTime.MaxValue;
DateTime dtMax = DateTime.MinValue;
while (reader.Read())
{
MyTraceEventArgs arg = new MyTraceEventArgs(reader);
HandleTraceEvent(arg);
if (!string.IsNullOrEmpty(sDateColumnName) && !Convert.IsDBNull(reader[sDateColumnName]))
{
DateTime dt = Convert.ToDateTime(reader[sDateColumnName]);
if (dtMin > dt)
{
dtMin = dt;
}
if (dtMax < dt)
{
dtMax = dt;
}
long iSecondsDiff = (long)(dtMax - dtMin).TotalSeconds; // Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Second, dtMin, dtMax, Microsoft.VisualBasic.FirstDayOfWeek.System, Microsoft.VisualBasic.FirstWeekOfYear.Jan1);
this.dtTraceStarted = DateTime.Now.AddSeconds(-iSecondsDiff); // Microsoft.VisualBasic.DateAndTime.DateAdd(Microsoft.VisualBasic.DateInterval.Second, -iSecondsDiff, DateTime.Now);
}
timer1_Tick(null, null);
Application.DoEvents();
}
FinishExecute(true);
return;
}
FinishExecute(false);
}
finally
{
try
{
reader.Close();
}
catch { }
}
}
}
catch (Exception ex)
{
FinishExecute(false);
MessageBox.Show(ex.Message);
}
}
void trc_OnEvent(object sender, TraceEventArgs e)
{
try
{
lock (this) //will this help ensure we run the code in serial, not in parallel?? it's important for this code to load the trace events in serial and in order
{
MyTraceEventArgs args = new MyTraceEventArgs(e);
HandleTraceEvent(args);
}
}
catch (Exception ex)
{
try
{
bStopped = true;
FinishExecute(false);
}
catch { }
MessageBox.Show("There was a problem receiving a trace event: " + ex.Message);
}
}
private void Execute()
{
dictHitIndexes = new Dictionary<CubeAttribute, int>();
listDrillthroughQueries = new List<string>();
bExecuting = true;
this.radioTraceTypeLive.Enabled = false;
this.radioTraceTypeSQL.Enabled = false;
this.btnSQLConnection.Enabled = false;
this.dtTraceStarted = DateTime.Now;
this.Width = this.iWindowFullWidth;
this.Height = this.iWindowShortHeight;
this.buttonCancel.Visible = false;
this.buttonOK.Visible = false;
this.grpProgress.Visible = true;
this.treeViewAggregation.Visible = false;
this.lblUnusedAggregationsToDelete.Visible = false;
this.iQueries = 0;
this.dtTraceStarted = DateTime.Now;
timer1_Tick(null, null);
this.btnExecute.Enabled = false;
Application.DoEvents();
try
{
if (this.radioTraceTypeLive.Checked)
{
timer1.Enabled = true;
string sTraceID = "BIDS Helper Delete Unused Indexes Trace " + System.Guid.NewGuid().ToString();
trc = liveServer.Traces.Add(sTraceID, sTraceID);
trc.OnEvent += new TraceEventHandler(trc_OnEvent);
trc.Stopped += new TraceStoppedEventHandler(trc_Stopped);
trc.AutoRestart = true;
TraceEvent te;
te = trc.Events.Add(TraceEventClass.QueryBegin);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.SessionID);
te = trc.Events.Add(TraceEventClass.QueryEnd);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.SessionID);
te = trc.Events.Add(TraceEventClass.QuerySubcubeVerbose);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.ObjectPath);
te.Columns.Add(TraceColumn.SessionID);
trc.Update();
trc.Start();
this.btnExecute.Text = "Stop Trace";
this.btnExecute.Enabled = true;
}
else
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
bool bHasRowNumberColumn = false;
try
{
//test that this table has the RowNumber column
SqlCommand cmdCheckRowNumber = new SqlCommand();
cmdCheckRowNumber.Connection = conn;
cmdCheckRowNumber.CommandText = "select top 1 RowNumber from " + Table + " (nolock)";
cmdCheckRowNumber.CommandTimeout = 0;
cmdCheckRowNumber.ExecuteNonQuery();
bHasRowNumberColumn = true;
}
catch { }
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from " + Table + " (nolock) order by CurrentTime" + (bHasRowNumberColumn ? ", RowNumber" : ""); //just select everything and filter in .NET (which allows us to throw better error messages if a column is missing... must be ordered so that we can recognize drillthrough queries and skip the query subcube verbose events until query end... ordering by CurrentTime then RowNumber allows this to work in ASTrace archive tables which have overlapping RowNumber ranges
cmd.CommandTimeout = 0;
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
if (!ReaderContainsColumn(reader, "EventClass"))
MessageBox.Show("Table " + Table + " must contain EventClass column");
else if (!ReaderContainsColumn(reader, "TextData"))
MessageBox.Show("Table " + Table + " must contain TextData column");
else if (!ReaderContainsColumn(reader, "ObjectPath"))
MessageBox.Show("Table " + Table + " must contain ObjectPath column");
else if (!ReaderContainsColumn(reader, "SessionID") && !ReaderContainsColumn(reader, "SPID"))
MessageBox.Show("Table " + Table + " must contain SessionID or SPID column");
else
{
if (ReaderContainsColumn(reader, "SessionID"))
sSessionIDColumnName = "SessionID";
else
sSessionIDColumnName = "SPID";
this.dtTraceStarted = DateTime.Now;
string sDateColumnName = "CurrentTime";
DateTime dtMin = DateTime.MaxValue;
DateTime dtMax = DateTime.MinValue;
Int64 iRowCount = 0;
while (reader.Read())
{
MyTraceEventArgs arg = new MyTraceEventArgs(reader);
HandleTraceEvent(arg);
if (!string.IsNullOrEmpty(sDateColumnName) && !Convert.IsDBNull(reader[sDateColumnName]))
{
DateTime dt = Convert.ToDateTime(reader[sDateColumnName]);
if (dtMin > dt) dtMin = dt;
if (dtMax < dt) dtMax = dt;
long iSecondsDiff = Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Second, dtMin, dtMax, Microsoft.VisualBasic.FirstDayOfWeek.System, Microsoft.VisualBasic.FirstWeekOfYear.Jan1);
this.dtTraceStarted = Microsoft.VisualBasic.DateAndTime.DateAdd(Microsoft.VisualBasic.DateInterval.Second, -iSecondsDiff, DateTime.Now);
}
if (iRowCount++ % 500 == 0)
{
timer1_Tick(null, null);
Application.DoEvents();
}
}
timer1_Tick(null, null);
Application.DoEvents();
FinishExecute(true);
return;
}
FinishExecute(false);
}
finally
{
try
{
reader.Close();
}
catch { }
}
}
}
catch (Exception ex)
{
FinishExecute(false);
MessageBox.Show(ex.Message);
}
}
void HandleTraceEvent(MyTraceEventArgs e)
{
if (e.EventClass == TraceEventClass.QueryEnd && e.EventSubclass == TraceEventSubclass.MdxQuery && e.DatabaseName == liveDB.Name)
iQueries++;
else if (e.EventClass == TraceEventClass.GetDataFromAggregation && e.DatabaseName == liveDB.Name && e.ObjectPath.StartsWith(sCubePath))
{
string sMeasureGroupID = e.ObjectPath.Substring(sCubePath.Length);
string sPartitionID = sMeasureGroupID.Substring(sMeasureGroupID.IndexOf('.') + 1);
sMeasureGroupID = sMeasureGroupID.Substring(0, sMeasureGroupID.IndexOf('.'));
MeasureGroup mg = cloneCube.MeasureGroups.Find(sMeasureGroupID);
if (cloneMG == null) //if checking for hit aggs on entire cube
{
if (mg == null)
return;
}
else
{
if (mg == null || sMeasureGroupID != cloneMG.ID)
return;
}
MeasureGroup liveMG = this.liveCube.MeasureGroups.Find(sMeasureGroupID);
if (liveMG == null) return;
Partition livePartition = liveMG.Partitions.Find(sPartitionID);
if (livePartition == null) return;
string sAggID = e.TextData.Split(new char[] { '\r', '\n' })[0];
if (livePartition.AggregationDesign == null) return;
Aggregation liveAgg = livePartition.AggregationDesign.Aggregations.Find(sAggID);
if (liveAgg == null) return;
//found this aggregation on the live cube... now find the equivalent agg in the cloned (local) cube
AggregationDesign cloneAggDesign = mg.AggregationDesigns.Find(liveAgg.Parent.ID);
if (cloneAggDesign == null) return;
Aggregation cloneAgg = cloneAggDesign.Aggregations.Find(sAggID);
if (cloneAgg == null) return;
lock (dictHitAggs)
{
if (dictHitAggs.ContainsKey(cloneAgg))
{
dictHitAggs[cloneAgg]++;
}
else
{
dictHitAggs.Add(cloneAgg, 1);
}
}
iAggHits++;
}
}
void trc_OnEvent(object sender, TraceEventArgs e)
{
try
{
MyTraceEventArgs args = new MyTraceEventArgs(e);
HandleTraceEvent(args);
}
catch (Exception ex)
{
try
{
bStopped = true;
FinishExecute(false);
}
catch { }
MessageBox.Show("There was a problem receiving a trace event: " + ex.Message);
}
}
private void Execute()
{
dictHitAggs = new Dictionary<Aggregation, int>();
bExecuting = true;
this.radioTraceTypeLive.Enabled = false;
this.radioTraceTypeSQL.Enabled = false;
this.btnSQLConnection.Enabled = false;
this.dtTraceStarted = DateTime.Now;
this.Width = this.iWindowFullWidth;
this.Height = this.iWindowShortHeight;
this.buttonCancel.Visible = false;
this.buttonOK.Visible = false;
this.grpProgress.Visible = true;
this.treeViewAggregation.Visible = false;
this.lblUnusedAggregationsToDelete.Visible = false;
this.iAggHits = 0;
this.iQueries = 0;
this.dtTraceStarted = DateTime.Now;
timer1_Tick(null, null);
this.btnExecute.Enabled = false;
Application.DoEvents();
try
{
if (this.radioTraceTypeLive.Checked)
{
timer1.Enabled = true;
string sTraceID = "BIDS Helper Delete Unused Aggs Trace " + System.Guid.NewGuid().ToString();
trc = liveServer.Traces.Add(sTraceID, sTraceID);
trc.OnEvent += new TraceEventHandler(trc_OnEvent);
trc.Stopped += new TraceStoppedEventHandler(trc_Stopped);
trc.AutoRestart = true;
TraceEvent te;
te = trc.Events.Add(TraceEventClass.QueryEnd);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.EventSubclass);
te = trc.Events.Add(TraceEventClass.GetDataFromAggregation);
te.Columns.Add(TraceColumn.DatabaseName);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.ObjectPath);
trc.Update();
trc.Start();
this.btnExecute.Text = "Stop Trace";
this.btnExecute.Enabled = true;
}
else
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from " + Table; //just select everything and filter in .NET (which allows us to throw better error messages if a column is missing
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
if (!ReaderContainsColumn(reader, "EventClass"))
MessageBox.Show("Table " + Table + " must contain EventClass column");
else if (!ReaderContainsColumn(reader, "EventSubclass"))
MessageBox.Show("Table " + Table + " must contain EventSubclass column");
else if (!ReaderContainsColumn(reader, "TextData"))
MessageBox.Show("Table " + Table + " must contain TextData column");
else if (!ReaderContainsColumn(reader, "ObjectPath"))
MessageBox.Show("Table " + Table + " must contain ObjectPath column");
else
{
this.dtTraceStarted = DateTime.Now;
string sDateColumnName = "";
if (ReaderContainsColumn(reader, "StartTime"))
sDateColumnName = "StartTime";
else if (ReaderContainsColumn(reader, "CurrentTime"))
sDateColumnName = "CurrentTime";
DateTime dtMin = DateTime.MaxValue;
DateTime dtMax = DateTime.MinValue;
while (reader.Read())
{
MyTraceEventArgs arg = new MyTraceEventArgs(reader);
HandleTraceEvent(arg);
if (!string.IsNullOrEmpty(sDateColumnName) && !Convert.IsDBNull(reader[sDateColumnName]))
{
DateTime dt = Convert.ToDateTime(reader[sDateColumnName]);
if (dtMin > dt) dtMin = dt;
if (dtMax < dt) dtMax = dt;
long iSecondsDiff = Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Second, dtMin, dtMax, Microsoft.VisualBasic.FirstDayOfWeek.System, Microsoft.VisualBasic.FirstWeekOfYear.Jan1);
this.dtTraceStarted = Microsoft.VisualBasic.DateAndTime.DateAdd(Microsoft.VisualBasic.DateInterval.Second, -iSecondsDiff, DateTime.Now);
}
timer1_Tick(null, null);
Application.DoEvents();
}
FinishExecute(true);
return;
}
FinishExecute(false);
}
finally
{
try
{
reader.Close();
}
catch { }
}
}
}
catch (Exception ex)
{
FinishExecute(false);
MessageBox.Show(ex.Message);
}
}
