//private IResultsTarget _resultsTarget; public void Start() { if (_trace != null) if (_trace.IsStarted) throw new InvalidOperationException("Cannot start a new trace as one is already running"); if (Status != QueryTraceStatus.Started) Status = QueryTraceStatus.Starting; _trace = GetTrace(); SetupTrace(_trace); _trace.Start(); // create timer to "ping" the server with DISCOVER_SESSION requests // until the trace events start to fire. if (_startingTimer == null) _startingTimer = new Timer(); _startingTimer.Interval = 300; //TODO - make time interval shorter _startingTimer.Elapsed += OnTimerElapsed; _startingTimer.Enabled = true; _startingTimer.Start(); utcPingStart = DateTime.UtcNow; // Wait for Trace to become active }
public void StartTest() { try { Dictionary<Aggregation, long> dictAggRowCount = new Dictionary<Aggregation, long>(); Dictionary<AggregationDesign, long> dictAggDesignRowCount = new Dictionary<AggregationDesign, long>(); AdomdConnection conn = new AdomdConnection("Data Source=" + _currentAggD.ParentServer.Name + ";Initial Catalog=" + _currentAggD.ParentDatabase.Name); conn.Open(); _sessionID = conn.SessionID; if (_cancelled) return; foreach (Partition p in _currentAggD.Parent.Partitions) { if (p.AggregationDesignID != _currentAggD.ID) continue; RaiseProgressEvent(0, "Retrieving list of processed aggs in partition " + p.Name + "..."); AdomdRestrictionCollection coll = new AdomdRestrictionCollection(); coll.Add("DATABASE_NAME", _currentAggD.ParentDatabase.Name); coll.Add("CUBE_NAME", _currentAggD.ParentCube.Name); coll.Add("MEASURE_GROUP_NAME", p.Parent.Name); coll.Add("PARTITION_NAME", p.Name); DataSet aggDS = conn.GetSchemaDataSet("DISCOVER_PARTITION_STAT", coll); foreach (DataRow row in aggDS.Tables[0].Rows) { if (!string.IsNullOrEmpty(Convert.ToString(row["AGGREGATION_NAME"]))) { Aggregation a = p.AggregationDesign.Aggregations.FindByName(Convert.ToString(row["AGGREGATION_NAME"])); if (a == null) throw new Exception("Couldn't find aggregation [" + row["AGGREGATION_NAME"] + "]"); long lngAggRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]); if (lngAggRowCount > 0) { if (!dictAggRowCount.ContainsKey(a)) dictAggRowCount.Add(a, lngAggRowCount); else dictAggRowCount[a] += lngAggRowCount; } } else { long lngPartitionRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]); if (!dictAggDesignRowCount.ContainsKey(p.AggregationDesign ?? _emptyAggregationDesign)) dictAggDesignRowCount.Add(p.AggregationDesign ?? _emptyAggregationDesign, lngPartitionRowCount); else dictAggDesignRowCount[p.AggregationDesign ?? _emptyAggregationDesign] += lngPartitionRowCount; } if (_cancelled) return; } } if (dictAggRowCount.Count == 0) return; //figure out any DefaultMember that aren't the all member string sDefaultMembersCalcs = ""; string sDefaultMembersCols = ""; foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions) { RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension; if (rmgd == null) continue; foreach (MeasureGroupAttribute mga in rmgd.Attributes) { if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled) { sDefaultMembersCalcs += "MEMBER [Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|] as iif([" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.Level.Name = \"(All)\", null, [" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.UniqueName)\r\n"; if (sDefaultMembersCols.Length > 0) sDefaultMembersCols += ","; sDefaultMembersCols += "[Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|]\r\n"; } } } RaiseProgressEvent(1, "Detecting DefaultMember on each dimension attribute..."); AdomdCommand cmd = new AdomdCommand(); cmd.Connection = conn; cmd.CommandText = "with\r\n" + sDefaultMembersCalcs + "select {\r\n" + sDefaultMembersCols + "} on 0\r\n" + "from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]"; CellSet cs = cmd.ExecuteCellSet(); int iCol = 0; _dictDefaultMembers.Clear(); foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions) { RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension; if (rmgd == null) continue; foreach (MeasureGroupAttribute mga in rmgd.Attributes) { if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled) { string sValue = Convert.ToString(cs.Cells[iCol++].Value); if (!string.IsNullOrEmpty(sValue)) { _dictDefaultMembers.Add(mga, sValue); } } } } conn.Close(false); if (_cancelled) return; RaiseProgressEvent(2, "Starting trace..."); Server s = new Server(); s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID); Server sAlt = new Server(); sAlt.Connect("Data Source=" + _currentAggD.ParentServer.Name); MeasureGroup mgAlt = sAlt.Databases.GetByName(_currentAggD.ParentDatabase.Name).Cubes.GetByName(_currentAggD.ParentCube.Name).MeasureGroups.GetByName(_currentAggD.Parent.Name); try { Database db = s.Databases.GetByName(_currentAggD.ParentDatabase.Name); string sTraceID = "BIDS Helper Aggs Performance Trace " + System.Guid.NewGuid().ToString(); _trc = s.Traces.Add(sTraceID, sTraceID); _trc.OnEvent += new TraceEventHandler(trace_OnEvent); _trc.Stopped += new TraceStoppedEventHandler(trace_Stopped); _trc.AutoRestart = false; TraceEvent te; te = _trc.Events.Add(TraceEventClass.QueryEnd); te.Columns.Add(TraceColumn.Duration); te.Columns.Add(TraceColumn.SessionID); te = _trc.Events.Add(TraceEventClass.GetDataFromAggregation); te.Columns.Add(TraceColumn.ObjectPath); te.Columns.Add(TraceColumn.TextData); te.Columns.Add(TraceColumn.SessionID); te.Columns.Add(TraceColumn.ConnectionID); _trc.Update(); _trc.Start(); if (_cancelled) return; s.BeginTransaction(); UnprocessOtherPartitions(s); int i = 0; if (_testAgg) { foreach (Aggregation a in dictAggRowCount.Keys) { RaiseProgressEvent(3 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with agg " + i + " of " + dictAggRowCount.Count + " (" + a.Name + ")..."); AggregationPerformance aggP = new AggregationPerformance(a); aggP.AggregationRowCount = dictAggRowCount[a]; aggP.PartitionRowCount = dictAggDesignRowCount[a.Parent]; aggP.MeasureGroupRowCount = aggP.PartitionRowCount; //if there are multiple aggregation designs, outside code will fix that ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n" + " <Object>" + "\r\n" + " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n" + " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n" + " </Object>" + "\r\n" + " </ClearCache>"); _queryEnded = false; //initialize the MDX script with a no-op query ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID); while (!this._queryEnded) //wait for session trace query end event { if (_cancelled) return; System.Threading.Thread.Sleep(100); } aggP.ScriptPerformanceWithAgg = _queryDuration; _queryEnded = false; //don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID); while (!this._queryEnded) //wait for session trace query end event { if (_cancelled) return; System.Threading.Thread.Sleep(100); } aggP.QueryPerformanceWithAgg = _queryDuration; if (_dictHitAggs.ContainsKey(a)) aggP.HitAggregation = true; aggP.AggHits = _dictHitAggs; _dictHitAggs = new Dictionary<Aggregation, int>(); _listAggPerf.Add(aggP); if (_cancelled) return; } } if (_testWithoutSomeAggs && _listAggPerf.Count > 0) { RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping some aggs inside a transaction..."); //build list of all aggs which were hit, and which are contained within another agg List<AggregationPerformance> allAggs = new List<AggregationPerformance>(); foreach (AggregationPerformance ap in _listAggPerf) { if (!ap.HitAggregation) continue; foreach (AggregationPerformance ap2 in _listAggPerf) { if (ap.Aggregation != ap2.Aggregation && ap.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(ap.Aggregation, ap2.Aggregation, false)) { allAggs.Add(ap); break; } } } allAggs.Sort(delegate(AggregationPerformance a, AggregationPerformance b) { int iCompare = 0; try { if (a == b || a.Aggregation == b.Aggregation) return 0; iCompare = a.AggregationRowCount.CompareTo(b.AggregationRowCount); if (iCompare == 0) { //if the aggs are the same rowcount, then sort by whether one is contained in the other if (SearchSimilarAggs.IsAggregationIncluded(a.Aggregation, b.Aggregation, false)) return -1; else if (SearchSimilarAggs.IsAggregationIncluded(b.Aggregation, a.Aggregation, false)) return 1; else return 0; } } catch { } return iCompare; }); List<AggregationPerformance> deletedAggregationPerfs = new List<AggregationPerformance>(); List<AggregationPerformance> nextAggs = new List<AggregationPerformance>(); List<AggregationPerformance> aggsToSkipTesting = new List<AggregationPerformance>(); System.Diagnostics.Stopwatch timerProcessIndexes = new System.Diagnostics.Stopwatch(); long lngLastProcessIndexesTime = 0; AggregationPerformance lastDeletedAggregationPerf = null; while (allAggs.Count > 0) { AggregationPerformance aggP = null; if (nextAggs.Count == 0) { aggP = allAggs[0]; allAggs.RemoveAt(0); } else { aggP = nextAggs[0]; nextAggs.RemoveAt(0); allAggs.Remove(aggP); } deletedAggregationPerfs.Add(aggP); //capture XMLA for deleting aggs AggregationDesign aggD = mgAlt.AggregationDesigns.GetByName(aggP.Aggregation.Parent.Name); aggD.ParentServer.CaptureXml = true; foreach (AggregationPerformance ap in deletedAggregationPerfs) { if (aggD.Aggregations.ContainsName(ap.Aggregation.Name)) { aggD.Aggregations.RemoveAt(aggD.Aggregations.IndexOfName(ap.Aggregation.Name)); } } aggD.Update(UpdateOptions.ExpandFull); string sAlterXMLA = aggD.ParentServer.CaptureLog[0]; aggD.ParentServer.CaptureLog.Clear(); aggD.ParentServer.CaptureXml = false; aggD.Refresh(true); //get the deleted aggs back ServerExecute(s, sAlterXMLA); if (_cancelled) return; RaiseProgressEvent(5 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Processing aggs without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")..."); timerProcessIndexes.Reset(); timerProcessIndexes.Start(); //process aggs to delete existing aggs ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n" + " <Parallel>" + "\r\n" + " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n" + " <Object>" + "\r\n" + " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n" + " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n" + " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n" + " </Object>" + "\r\n" + " <Type>ProcessIndexes</Type>" + "\r\n" + " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n" + " </Process>" + "\r\n" + " </Parallel>" + "\r\n" + "</Batch>" + "\r\n"); if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors); timerProcessIndexes.Stop(); //record time it took to process aggs... compare how long the prior one took, then you can determine how much incremental time was spent on the newly deleted agg if (lastDeletedAggregationPerf != null) lastDeletedAggregationPerf.ProcessIndexesDuration = lngLastProcessIndexesTime - timerProcessIndexes.ElapsedMilliseconds; lngLastProcessIndexesTime = timerProcessIndexes.ElapsedMilliseconds; lastDeletedAggregationPerf = aggP; if (_cancelled) return; int j = 0; foreach (AggregationPerformance deleteAP in deletedAggregationPerfs) { RaiseProgressEvent(6 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Testing performance without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + "\r\nTesting agg " + (++j) + " of " + deletedAggregationPerfs.Count + " (" + deleteAP.AggregationName + ")..."); if (aggsToSkipTesting.Contains(deleteAP)) continue; //skip this agg if we've already determined it won't hit another agg ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n" + " <Object>" + "\r\n" + " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n" + " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n" + " </Object>" + "\r\n" + " </ClearCache>"); _queryEnded = false; //initialize the MDX script with a no-op query ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID); while (!this._queryEnded) //wait for session trace query end event { if (_cancelled) return; System.Threading.Thread.Sleep(100); } long lngScriptDuration = _queryDuration; _queryEnded = false; //don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query ServerExecuteMDX(db, deleteAP.PerformanceTestMDX, _sessionID); while (!this._queryEnded) //wait for session trace query end event { if (_cancelled) return; System.Threading.Thread.Sleep(100); } long lngQueryDuration = _queryDuration; List<Aggregation> deletedAggregations = new List<Aggregation>(); foreach (AggregationPerformance a in deletedAggregationPerfs) { deletedAggregations.Add(a.Aggregation); } MissingAggregationPerformance missingAggPerf = new MissingAggregationPerformance(deleteAP, deletedAggregations.ToArray(), _dictHitAggs); _listMissingAggPerf.Add(missingAggPerf); missingAggPerf.QueryPerformance = lngQueryDuration; missingAggPerf.ScriptPerformance = lngScriptDuration; foreach (Aggregation a in missingAggPerf.AggHitsDiff) { foreach (AggregationPerformance ap in allAggs) { if (ap.Aggregation == a && !nextAggs.Contains(ap)) nextAggs.Add(ap); } } if (missingAggPerf.AggHitsDiff.Length == 0) { aggsToSkipTesting.Add(deleteAP); } else { bool bThisAggContainedInRemainingAgg = false; foreach (AggregationPerformance ap2 in allAggs) { if (deleteAP.Aggregation != ap2.Aggregation && deleteAP.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(deleteAP.Aggregation, ap2.Aggregation, false)) { bThisAggContainedInRemainingAgg = true; break; } } if (!bThisAggContainedInRemainingAgg) aggsToSkipTesting.Add(deleteAP); //stop testing this agg when it's not contained in any remaining aggs that need to be tested } _dictHitAggs = new Dictionary<Aggregation, int>(); } if (_cancelled) return; s.RollbackTransaction(); s.BeginTransaction(); UnprocessOtherPartitions(s); } } if (_testNoAggs) { //ensure the counter is where it's supposed to be since the "test with some aggs" test may not have done iterations for every agg i = Math.Max(i, dictAggRowCount.Count * (_totalIterations - 1)); RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping all aggs inside a transaction..."); //delete all aggs in all aggregation designs string sXMLA = "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:as=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:dwd=\"http://schemas.microsoft.com/DataWarehouse/Designer/1.0\">" + "\r\n" + " <Alter AllowCreate=\"true\" ObjectExpansion=\"ExpandFull\">" + "\r\n" + " <Object>" + "\r\n" + " <DatabaseID>" + _currentAggD.Parent.ParentDatabase.ID + "</DatabaseID>" + "\r\n" + " <CubeID>" + _currentAggD.Parent.Parent.ID + "</CubeID>" + "\r\n" + " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n" + " <AggregationDesignID>" + _currentAggD.ID + "</AggregationDesignID>" + "\r\n" + " </Object>" + "\r\n" + " <ObjectDefinition>" + "\r\n" + " <AggregationDesign>" + "\r\n" + " <ID>" + _currentAggD.ID + "</ID>" + "\r\n" + " <Name>" + _currentAggD.Name + "</Name>" + "\r\n" + " <Aggregations>" + "\r\n" + " </Aggregations>" + "\r\n" + " </AggregationDesign>" + "\r\n" + " </ObjectDefinition>" + "\r\n" + " </Alter>" + "\r\n" + "</Batch>" + "\r\n"; ServerExecute(s, sXMLA); RaiseProgressEvent(5 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Processing empty aggregation design..."); if (_cancelled) return; //process aggs to delete existing aggs ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n" + " <Parallel>" + "\r\n" + " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n" + " <Object>" + "\r\n" + " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n" + " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n" + " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n" + " </Object>" + "\r\n" + " <Type>ProcessIndexes</Type>" + "\r\n" + " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n" + " </Process>" + "\r\n" + " </Parallel>" + "\r\n" + "</Batch>" + "\r\n"); if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors); if (_cancelled) return; foreach (AggregationPerformance aggP in _listAggPerf) { RaiseProgressEvent(10 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with no aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")..."); ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n" + " <Object>" + "\r\n" + " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n" + " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n" + " </Object>" + "\r\n" + " </ClearCache>"); if (_cancelled) return; _queryEnded = false; //initialize the MDX script with a no-op query ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID); while (!this._queryEnded) //wait for session trace query end event { if (_cancelled) return; System.Threading.Thread.Sleep(100); } aggP.ScriptPerformanceWithoutAggs = _queryDuration; _queryEnded = false; ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID); while (!this._queryEnded) //wait for session trace query end event { if (_cancelled) return; System.Threading.Thread.Sleep(100); } aggP.QueryPerformanceWithoutAggs = _queryDuration; } } //end of testing with no aggs RaiseProgressEvent(100, "Finished measure group " + _currentAggD.Parent.Name); if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors); } finally { try { if (!s.Connected) { s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID); } } catch { try { if (!s.Connected) { s.Connect("Data Source=" + _currentAggD.ParentServer.Name); //can't connect to that session, so just reconnect } } catch { } } try { s.RollbackTransaction(); } catch { } try { _trc.Drop(); } catch { } try { s.Disconnect(); } catch { } } } catch (Exception ex) { if (!_cancelled) { _errors += ex.Message + "\r\n" + ex.StackTrace + "\r\n"; System.Windows.Forms.MessageBox.Show(_errors); } } }
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); } }
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); } }
public void Start() { try { if (_trace != null) if (_trace.IsStarted || Status == QueryTraceStatus.Starting) return; // exit here if trace is already started if (Status != QueryTraceStatus.Started) Status = QueryTraceStatus.Starting; Log.Verbose("{class} {method} {event}", "QueryTraceEngine", "Start", "Connecting to: " + _connectionString); _connection = new ADOTabular.ADOTabularConnection(_connectionString, _connectionType); _connection.Open(); _trace = GetTrace(); SetupTrace(_trace, _eventsToCapture); _trace.Start(); // create timer to "ping" the server with DISCOVER_SESSION requests // until the trace events start to fire. if (_startingTimer == null) _startingTimer = new Timer(); _startingTimer.Interval = 300; //TODO - make time interval shorter? _startingTimer.Elapsed += OnTimerElapsed; _startingTimer.Enabled = true; _startingTimer.Start(); utcPingStart = DateTime.UtcNow; // Wait for Trace to become active } catch (Exception ex) { Log.Error("{class} {method} {message}","QueryTraceEngine" , "Start", ex.Message); } }