private void ReadEventsFromFile() { try { // get first trace rollover file var parentDir = Directory.GetParent(tracePath); var fileName = Path.GetFileNameWithoutExtension(tracePath) + "*" + Path.GetExtension(tracePath); List <string> files = Directory.GetFiles(parentDir.FullName, fileName).ToList(); files.Sort(); SqlTransformer transformer = new SqlTransformer(); foreach (string xeFile in files) { ExtendedEventsFileReader reader = new ExtendedEventsFileReader(xeFile, Events); reader.ReadEvents(); while (!reader.HasFinished) { Thread.Sleep(50); } } } catch (Exception ex) { logger.Error(ex.Message); if (ex.InnerException != null) { logger.Error(ex.InnerException.Message); } Dispose(); } }
private void CreateAddonConfig() { try { var sqlTrans = new SqlTransformer(); sqlTrans.DBTypeSign = this.AppSetting.DatabaseType; var sqlMap = new SQLMapFactory(this.AppSetting.DBServer, this.AppSetting.DBUser, this.AppSetting.DBPassword, "SBO-COMMON").GetSQLMap(sqlTrans.DBTypeSign); sqlMap.DefaultDatabase = string.Empty; sqlTrans.SetDB(new dbConnectionFactory(this.AppSetting.DBServer, this.AppSetting.DBUser, this.AppSetting.DBPassword, "SBO-COMMON").GetDBConnection(sqlMap)); var encoding = System.Text.Encoding.UTF8; string sqlStr; if (string.IsNullOrEmpty(this.AppSetting.IISAddress) || string.IsNullOrEmpty(this.AppSetting.IISPort)) { sqlStr = string.Format(@"delete from ""AVA_ADDON_CONFIG"" where ""dbName""='BSUi.BusinessSystemCenter.B1Addon.exe.config'"); } else { sqlStr = string.Format(@"delete from ""AVA_ADDON_CONFIG"" where ""dbName""='{0}.{1}.BSUi.BusinessSystemCenter.B1Addon.exe.config'", this.AppSetting.DBName, this.AppSetting.DBServer); } sqlTrans.AddScriptString(sqlStr); string AddonConfig = string.Format(@"<?xml version=""1.0"" encoding=""utf-8""?> <configuration> <appSettings> <!-- 开启业务仓库服务路由--> <add key=""BOServiceRouting"" value=""true"" /> </appSettings> <system.serviceModel> <bindings> <basicHttpBinding> <binding name=""BasicHttpBinding"" closeTimeout=""00:59:59"" openTimeout=""00:59:59"" receiveTimeout=""00:59:59"" sendTimeout=""00:59:59"" maxBufferSize=""2147483647"" maxReceivedMessageSize=""2147483647"" textEncoding=""utf-8"" transferMode=""Buffered""> </binding> </basicHttpBinding> </bindings> <client> <endpoint address=""{0}:{1}/SystemCenter/DataService/SystemCenter.svc"" binding=""basicHttpBinding"" bindingConfiguration=""BasicHttpBinding"" contract=""BORep.BusinessSystemCenter.BORepository.IBORepBusinessSystemCenterService"" name=""BasicHttpBinding_BORepBusinessSystemCenter"" /> </client> </system.serviceModel> </configuration>", this.AppSetting.IISAddress, this.AppSetting.IISPort); if (string.IsNullOrEmpty(this.AppSetting.IISAddress) || string.IsNullOrEmpty(this.AppSetting.IISPort)) { sqlStr = string.Format(@"insert into ""AVA_ADDON_CONFIG"" values ('BSUi.BusinessSystemCenter.B1Addon.exe.config',N'{2}',GETDATE())"); } else { sqlStr = string.Format(@"insert into ""AVA_ADDON_CONFIG"" values ('{0}.{1}.BSUi.BusinessSystemCenter.B1Addon.exe.config',N'{2}',GETDATE())", this.AppSetting.DBName, this.AppSetting.DBServer, AddonConfig); } sqlTrans.AddScriptString(sqlStr); sqlTrans.Run(); } catch (Exception err) { throw new Exception(string.Format("配置AddonConfig失败.{0}", err.Message)); } }
public void ReadEvents() { EventCount = 0; SqlTransformer transformer = new SqlTransformer(); using (QueryableXEventData eventstream = new QueryableXEventData(Path)) { var eventsEnumerator = eventstream.GetEnumerator(); while (!stopped && eventsEnumerator.MoveNext()) { PublishedEvent evt = eventsEnumerator.Current; ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent(); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "statement"); evnt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text"); evnt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { commandText = (string)TryGetValue(evt, FieldType.Action, "sql_text"); evnt.Type = WorkloadEvent.EventType.Timeout; } else { evnt.Type = WorkloadEvent.EventType.Unknown; continue; } try { evnt.ApplicationName = (string)TryGetValue(evt, FieldType.Action, "client_app_name"); evnt.DatabaseName = (string)TryGetValue(evt, FieldType.Action, "database_name"); evnt.HostName = (string)TryGetValue(evt, FieldType.Action, "client_hostname"); evnt.LoginName = (string)TryGetValue(evt, FieldType.Action, "server_principal_name"); object oSession = TryGetValue(evt, FieldType.Action, "session_id"); if (oSession != null) { evnt.SPID = Convert.ToInt32(oSession); } if (commandText != null) { evnt.Text = commandText; } evnt.StartTime = evt.Timestamp.LocalDateTime; if (evnt.Type == WorkloadEvent.EventType.Timeout) { evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); evnt.CPU = Convert.ToInt64(evnt.Duration); } else { evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value); evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value); evnt.CPU = Convert.ToInt64(evt.Fields["cpu_time"].Value); evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); } } catch (Exception e) { logger.Error(e, "Error converting XE data from the stream."); throw; } if (transformer.Skip(evnt.Text)) { continue; } evnt.Text = transformer.Transform(evnt.Text); Events.Enqueue(evnt); EventCount++; } finished = true; } }
public override void ReadEvents() { string sqlXE = @" DECLARE @filename nvarchar(max); SELECT @filename = CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]','nvarchar(max)') FROM sys.dm_xe_{3}session_targets AS t INNER JOIN sys.dm_xe_{3}sessions AS s ON t.event_session_address = s.address WHERE s.name = '{2}' AND target_name = 'event_file'; SELECT event_data, file_offset FROM sys.fn_xe_file_target_read_file(@filename, NULL, {0}, {1}); "; long lastEvent = -1; try { while (!stopped) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConnectionString; conn.Open(); string sql = ""; string databaseSuffix = ServerType == ExtendedEventsWorkloadListener.ServerType.AzureSqlDatabase ? "database_" : ""; if (lastEvent > 0) { sql = String.Format(sqlXE, "@filename", lastEvent, SessionName, databaseSuffix); } else { sql = String.Format(sqlXE, "NULL", "NULL", SessionName, databaseSuffix); } using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; SqlTransformer transformer = new SqlTransformer(); int rowsRead = 0; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader["file_offset"] != DBNull.Value) { lastEvent = (long)reader["file_offset"]; } ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent(); string xmldata = (string)reader["event_data"]; XmlDocument doc = new XmlDocument(); doc.LoadXml(xmldata); XmlNode eventNode = doc.DocumentElement.SelectSingleNode("/event"); string name = eventNode.Attributes["name"].InnerText; if (name == "sql_batch_completed") { evt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (name == "rpc_completed") { evt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (name == "attention") { evt.Type = WorkloadEvent.EventType.Timeout; } else if (name == "user_event") { evt.Type = WorkloadEvent.EventType.Error; } else { evt.Type = WorkloadEvent.EventType.Unknown; continue; } DateTimeOffset timestamp = DateTimeOffset.Parse(eventNode.Attributes["timestamp"].Value); evt.StartTime = timestamp.LocalDateTime; foreach (XmlNode node in eventNode.ChildNodes) { switch ((string)node.Attributes["name"].Value) { case "statement": if (evt.Type == WorkloadEvent.EventType.RPCCompleted) { evt.Text = (string)node.FirstChild.FirstChild.Value; } break; case "batch_text": if (evt.Type == WorkloadEvent.EventType.BatchCompleted) { evt.Text = (string)node.FirstChild.FirstChild.Value; } break; case "sql_text": if (evt.Type == WorkloadEvent.EventType.Timeout) { evt.Text = (string)node.FirstChild.FirstChild.Value; } break; case "client_app_name": evt.ApplicationName = (string)node.FirstChild.FirstChild.Value; break; case "database_name": evt.DatabaseName = (string)node.FirstChild.FirstChild.Value; break; case "client_hostname": evt.HostName = (string)node.FirstChild.FirstChild.Value; break; case "server_principal_name": evt.LoginName = (string)node.FirstChild.FirstChild.Value; break; case "username": evt.LoginName = (string)node.FirstChild.FirstChild.Value; break; case "session_id": evt.SPID = Convert.ToInt32(node.FirstChild.FirstChild.Value); break; case "cpu_time": evt.CPU = Convert.ToInt64(node.FirstChild.FirstChild.Value); break; case "duration": evt.Duration = Convert.ToInt64(node.FirstChild.FirstChild.Value); if (evt.Type == WorkloadEvent.EventType.Timeout) { evt.CPU = Convert.ToInt64(evt.Duration); } break; case "logical_reads": evt.Reads = Convert.ToInt64(node.FirstChild.FirstChild.Value); break; case "writes": evt.Writes = Convert.ToInt64(node.FirstChild.FirstChild.Value); break; case "user_data": evt.Text = (string)node.FirstChild.FirstChild.Value; break; default: break; } } if (evt.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(evt.Text)) { continue; } evt.Text = transformer.Transform(evt.Text); } Events.Enqueue(evt); rowsRead++; EventCount++; } } // Wait before querying the events file again if (rowsRead < DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD) { Thread.Sleep(DEFAULT_TRACE_INTERVAL_SECONDS * 1000); } } } } } catch (Exception ex) { logger.Error(ex.Message); logger.Error(ex.StackTrace); if (ex.InnerException != null) { logger.Error(ex.InnerException.Message); } } }
public override void ReadEvents() { EventCount = 0; SqlTransformer transformer = new SqlTransformer(); using (QueryableXEventData eventstream = new QueryableXEventData( ConnectionString, SessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache)) { var eventsEnumerator = eventstream.GetEnumerator(); while (!stopped && eventsEnumerator.MoveNext()) { PublishedEvent evt = eventsEnumerator.Current; ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent(); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "statement"); evnt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text"); evnt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { object value = TryGetValue(evt, FieldType.Action, "sql_text"); try { if (value is string) { commandText = (string)value; } else if (value is byte[]) { commandText = Encoding.Unicode.GetString((byte[])value); } else { throw new ArgumentException("Argument is of the wrong type"); } } catch (Exception e) { logger.Error(e, $"Unable to extract sql_text from attention event. Value is of type ${value.GetType().FullName}"); } evnt.Type = WorkloadEvent.EventType.Timeout; } else if (evt.Name == "user_event") { int num = (int)TryGetValue(evt, FieldType.Field, "event_id"); if (num == 83) { commandText = (string)TryGetValue(evt, FieldType.Field, "user_data"); evnt.Type = WorkloadEvent.EventType.Error; } } else { evnt.Type = WorkloadEvent.EventType.Unknown; continue; } try { evnt.ApplicationName = (string)TryGetValue(evt, FieldType.Action, "client_app_name"); evnt.DatabaseName = (string)TryGetValue(evt, FieldType.Action, "database_name"); evnt.HostName = (string)TryGetValue(evt, FieldType.Action, "client_hostname"); evnt.LoginName = (string)TryGetValue(evt, FieldType.Action, "server_principal_name"); object oSession = TryGetValue(evt, FieldType.Action, "session_id"); if (oSession != null) { evnt.SPID = Convert.ToInt32(oSession); } if (commandText != null) { evnt.Text = commandText; } evnt.StartTime = evt.Timestamp.LocalDateTime; if (evnt.Type == WorkloadEvent.EventType.Error) { // do nothing } else if (evnt.Type == WorkloadEvent.EventType.Timeout) { evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); evnt.CPU = Convert.ToInt64(evnt.Duration); } else { evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value); evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value); evnt.CPU = Convert.ToInt64(evt.Fields["cpu_time"].Value); evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); } } catch (Exception e) { logger.Error(e, "Error converting XE data from the stream."); throw; } if (evnt.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(evnt.Text)) { continue; } evnt.Text = transformer.Transform(evnt.Text); } Events.Enqueue(evnt); EventCount++; } } }
private void ReadTraceData(SqlConnection conn, ReadIteration currentIteration) { string sqlReadTrace = @" SELECT EventSequence ,Error ,TextData ,BinaryData ,DatabaseID ,HostName ,ApplicationName ,LoginName ,SPID ,Duration ,StartTime ,EndTime ,Reads ,Writes ,CPU ,EventClass ,DatabaseName FROM fn_trace_gettable(@path, @number_files) "; if (currentIteration.StartSequence > 0) { sqlReadTrace += "WHERE EventSequence > @event_offset"; } logger.Debug("Reading Trace data..."); TraceEventParser parser = new TraceEventParser(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlReadTrace; var paramPath = cmd.Parameters.Add("@path", System.Data.SqlDbType.NVarChar, 260); paramPath.Value = currentIteration.StartFileName; var paramNumberFiles = cmd.Parameters.Add("@number_files", System.Data.SqlDbType.Int); paramNumberFiles.Value = currentIteration.Files; var paramInitialSequence = cmd.Parameters.Add("@event_offset", System.Data.SqlDbType.BigInt); paramInitialSequence.Value = currentIteration.StartOffset; // don't pass initial file name and offset // read directly from the initial file // until we have some rows read already if ( EventCount == 0 || currentIteration.StartOffset <= 0 || currentIteration.StartOffset == currentIteration.MinOffset ) { paramPath.Value = currentIteration.StartFileName; paramNumberFiles.Value = 0; paramInitialSequence.Value = 0; } logger.Debug($"paramPath : {paramPath.Value}"); logger.Debug($"paramNumberFiles : {paramNumberFiles.Value}"); logger.Debug($"paramInitialSequence: {paramInitialSequence.Value}"); SqlTransformer transformer = new SqlTransformer(); try { using (SqlDataReader reader = cmd.ExecuteReader()) { int skippedRows = 0; while (reader.Read()) { if (reader["EventSequence"] != DBNull.Value) { currentIteration.EndSequence = (long)reader["EventSequence"]; } // read the event from the sqldatareader var evt = parser.ParseEvent(reader); // skip invalid events if (evt.Type == WorkloadEvent.EventType.Unknown) { continue; } // skip to the correct event in case we're reading again // from the same file and we have a reference sequence if ((currentIteration.RowsRead == 0) && (currentIteration.StartSequence > 0)) { // skip rows until we encounter the reference event_sequence if (evt.EventSequence != currentIteration.StartSequence) { skippedRows++; continue; } else { // skip one more row... skippedRows++; currentIteration.RowsRead++; continue; } } // this is only to print out a message, so consider // getting rid of it if (skippedRows > 0) { logger.Debug($"Skipped rows: {skippedRows}"); skippedRows = 0; } // now we have an event, no matter if good or bad => increment rows read currentIteration.RowsRead++; if (evt.EventSequence != null) { currentIteration.EndSequence = (long)evt.EventSequence; } if (evt.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(evt.Text)) { continue; } if (!Filter.Evaluate(evt)) { continue; } evt.Text = transformer.Transform(evt.Text); } // it's a "good" event: add it to the queue Events.Enqueue(evt); EventCount++; } logger.Debug($"currentIteration.EndSequence : {currentIteration.EndSequence}"); } } catch (Exception) { throw; } // Wait before querying the events file again if (currentIteration.RowsRead < ReadIteration.DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD && currentIteration.StartFileName == currentIteration.EndFileName) { Thread.Sleep(ReadIteration.DEFAULT_TRACE_INTERVAL_SECONDS * 1000); } } }
private void ReadEventsFromStream() { try { QueryableXEventData eventstream = new QueryableXEventData( ConnectionInfo.ConnectionString, "sqlworkload", EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache); int rowsRead = 0; SqlTransformer transformer = new SqlTransformer(); foreach (PublishedEvent evt in eventstream) { ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent(); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = evt.Fields["statement"].Value.ToString(); evnt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = evt.Fields["batch_text"].Value.ToString(); evnt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { commandText = evt.Actions["sql_text"].Value.ToString(); evnt.Type = WorkloadEvent.EventType.Timeout; } else { evnt.Type = WorkloadEvent.EventType.Unknown; continue; } if (evt.Actions["client_app_name"].Value != null) { evnt.ApplicationName = (string)evt.Actions["client_app_name"].Value; } if (evt.Actions["database_name"].Value != null) { evnt.DatabaseName = (string)evt.Actions["database_name"].Value; } if (evt.Actions["client_hostname"].Value != null) { evnt.HostName = (string)evt.Actions["client_hostname"].Value; } if (evt.Actions["server_principal_name"].Value != null) { evnt.LoginName = (string)evt.Actions["server_principal_name"].Value; } if (evt.Actions["session_id"].Value != null) { evnt.SPID = Convert.ToInt32(evt.Actions["session_id"].Value); } if (commandText != null) { evnt.Text = commandText; } evnt.StartTime = evt.Timestamp.LocalDateTime; if (evnt.Type == WorkloadEvent.EventType.Timeout) { evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); evnt.CPU = Convert.ToInt32(evnt.Duration / 1000); } else { evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value); evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value); evnt.CPU = Convert.ToInt32(evt.Fields["cpu_time"].Value); evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); } if (transformer.Skip(evnt.Text)) { continue; } evnt.Text = transformer.Transform(evnt.Text); Events.Enqueue(evnt); rowsRead++; } } catch (Exception ex) { logger.Error(ex.Message); logger.Error(ex.StackTrace); if (ex.InnerException != null) { logger.Error(ex.InnerException.Message); } Dispose(); } }
private void ModuleToDB(ibasModule module) { try { var args = new Core.ServiceEventArgs(string.Format("开始创建模块[{0}]的数据结构", module.ModuleDescription)); this.MessageManager.OnWriteMessageLog(this, args); var dsGetter = new DataStructuresGetter(); dsGetter.WorkFolder = module.ModuleInstallPath; var dsItems = dsGetter.Get(); dsGetter.AutoSelected(this.B1Included, this.GetCurrentDBType(), dsItems); if (dsItems == null) { return; } var logPath = string.Format(@"{0}Log\data_structures_{1}.txt", System.AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString("yyyyMMddhhmmss")); if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(logPath))) { System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(logPath)); } var gpDSItems = new Dictionary <string, List <DataStructureItem> >(); foreach (var item in dsItems.Where(c => c.Selected)) { if (gpDSItems.ContainsKey(item.Group)) { gpDSItems[item.Group].Add(item); } else { gpDSItems.Add(item.Group, new List <DataStructureItem>() { item }); } } using (var logFile = System.IO.File.CreateText(logPath)) { logFile.WriteLine(string.Format("folder:{0}", this.AppSetting.InstallDiraddress)); foreach (var gpKey in gpDSItems.Keys) { List <string> files = null; logFile.WriteLine(string.Format(" group:{0}", gpKey)); #region 数据结构创建 #region ibas args = new Core.ServiceEventArgs(string.Format("正在创建模块[{0}]的ibas数据结构", module.ModuleDescription)); this.MessageManager.OnWriteMessageLog(this, args); files = new List <string>(); foreach (var item in gpDSItems[gpKey].Where(c => c.ItemType == emDSItemType.data_structure && c.Platform == emPlatform.ibas)) { logFile.WriteLine(string.Format(" file:{0}", item.FilePath)); files.Add(item.FilePath); } if (files.Count > 0) { var xmlTrans = new XmlTransformer(); var ibasModel = xmlTrans.ToDomainModel(files.ToArray()); if (xmlTrans.HasError) { throw xmlTrans.ErrorLogs.FirstOrDefault(); } var dbTrans = new DbTransformer(); dbTrans.DBTypeSign = this.AppSetting.DatabaseType; dbTrans.SetMySQLMap(new SQLMapFactory(this.AppSetting.DBServer, this.AppSetting.DBUser, this.AppSetting.DBPassword, this.AppSetting.DBName).GetSQLMap(dbTrans.DBTypeSign)); dbTrans.SetDB(new dbConnectionFactory(this.AppSetting.DBServer, this.AppSetting.DBUser, this.AppSetting.DBPassword, this.AppSetting.DBName).GetDBConnection(dbTrans.MySQLMap)); dbTrans.ToDB(ibasModel); if (dbTrans.HasError) { throw new Exception(dbTrans.Errors.ToString()); } } #endregion #region b1 args = new Core.ServiceEventArgs(string.Format("正在创建模块[{0}]的b1数据结构", module.ModuleDescription)); this.MessageManager.OnWriteMessageLog(this, args); files = new List <string>(); foreach (var item in gpDSItems[gpKey].Where(c => c.ItemType == emDSItemType.data_structure && c.Platform == emPlatform.b1)) { logFile.WriteLine(string.Format(" file:{0}", item.FilePath)); files.Add(item.FilePath); } if (files.Count > 0) { var xmlTrans = new XmlTransformer(); var b1Model = xmlTrans.ToDomainModel(files.ToArray()); if (xmlTrans.HasError) { throw xmlTrans.ErrorLogs.FirstOrDefault(); } var b1Company = this.GetNewCompany(); var b1Trans = new B1DBTransformer(); b1Trans.SetDB(b1Company); b1Trans.ToDB(b1Model); b1Company.Disconnect(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(b1Company); if (b1Trans.HasError) { throw new Exception(b1Trans.Errors.ToString()); } } #endregion #endregion #region sql脚本 args = new Core.ServiceEventArgs(string.Format("正在执行模块[{0}]的sql脚本", module.ModuleDescription)); this.MessageManager.OnWriteMessageLog(this, args); files = new List <string>(); foreach (var item in gpDSItems[gpKey].Where(c => c.ItemType == emDSItemType.sql_script)) { logFile.WriteLine(string.Format(" file:{0}", item.FilePath)); files.Add(item.FilePath); } if (files.Count > 0) { foreach (var item in files) { try { var sqlTrans = new SqlTransformer(); sqlTrans.DBTypeSign = this.AppSetting.DatabaseType; var sqlMap = new SQLMapFactory(this.AppSetting.DBServer, this.AppSetting.DBUser, this.AppSetting.DBPassword, this.AppSetting.DBName).GetSQLMap(sqlTrans.DBTypeSign); sqlMap.DefaultDatabase = string.Empty; sqlTrans.SetDB(new dbConnectionFactory(this.AppSetting.DBServer, this.AppSetting.DBUser, this.AppSetting.DBPassword, this.AppSetting.DBName).GetDBConnection(sqlMap)); sqlTrans.AddScriptFile(item); sqlTrans.Run(); } catch (Exception error) { if (!item.Contains(string.Format(@"DataStructures\SQLs\SQL_SYS_mssql_Initialization.sql"))) { throw new Exception(string.Format("执行[{0}]脚本,错误:{1}", item, error.ToString())); } } } } #endregion logFile.WriteLine(); } logFile.Close(); } } catch (Exception ex) { throw; } }
public override void ReadEvents() { EventCount = 0; SqlTransformer transformer = new SqlTransformer(); using (QueryableXEventData eventstream = new QueryableXEventData( ConnectionString, SessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.CacheToDisk)) { var eventsEnumerator = eventstream.GetEnumerator(); while (!stopped && eventsEnumerator.MoveNext()) { PublishedEvent evt = eventsEnumerator.Current; ExecutionWorkloadEvent workloadEvent = new ExecutionWorkloadEvent(); try { workloadEvent.EventSequence = Convert.ToInt64(TryGetValue(evt, FieldType.Action, "event_sequence")); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "statement"); workloadEvent.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text"); workloadEvent.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { workloadEvent = new ErrorWorkloadEvent(); object value = TryGetValue(evt, FieldType.Action, "sql_text"); if (value == null) { continue; } try { if (value is string) { commandText = (string)value; } else if (value is byte[]) { commandText = Encoding.Unicode.GetString((byte[])value); } else { throw new ArgumentException("Argument is of the wrong type"); } } catch (Exception e) { logger.Error(e, $"Unable to extract sql_text from attention event. Value is of type ${value.GetType().FullName}"); } workloadEvent.Text = commandText; workloadEvent.Type = WorkloadEvent.EventType.Timeout; } else if (evt.Name == "user_event") { workloadEvent = new ErrorWorkloadEvent(); int num = (int)TryGetValue(evt, FieldType.Field, "event_id"); if (num == 83 || num == 82) { if (TryGetString(evt, FieldType.Field, "user_info").StartsWith("WorkloadTools.")) { commandText = TryGetString(evt, FieldType.Field, "user_data"); workloadEvent.Text = commandText; if (num == 83) { workloadEvent.Type = WorkloadEvent.EventType.Error; } else { workloadEvent.Type = WorkloadEvent.EventType.Timeout; } } else { workloadEvent.Type = WorkloadEvent.EventType.Unknown; continue; } } } else { workloadEvent.Type = WorkloadEvent.EventType.Unknown; continue; } try { workloadEvent.ApplicationName = TryGetString(evt, FieldType.Action, "client_app_name"); workloadEvent.DatabaseName = TryGetString(evt, FieldType.Action, "database_name"); workloadEvent.HostName = TryGetString(evt, FieldType.Action, "client_hostname"); workloadEvent.LoginName = TryGetString(evt, FieldType.Action, "server_principal_name"); workloadEvent.SPID = TryGetInt32(evt, FieldType.Action, "session_id"); if (commandText != null) { workloadEvent.Text = commandText; } workloadEvent.StartTime = evt.Timestamp.LocalDateTime; if (workloadEvent.Type == WorkloadEvent.EventType.Error) { workloadEvent.Duration = 0; workloadEvent.CPU = 0; } else if (workloadEvent.Type == WorkloadEvent.EventType.Timeout) { workloadEvent.Duration = TryGetInt64(evt, FieldType.Field, "duration"); workloadEvent.CPU = Convert.ToInt64(workloadEvent.Duration); } else { workloadEvent.Reads = TryGetInt64(evt, FieldType.Field, "logical_reads"); workloadEvent.Writes = TryGetInt64(evt, FieldType.Field, "writes"); workloadEvent.CPU = TryGetInt64(evt, FieldType.Field, "cpu_time"); workloadEvent.Duration = TryGetInt64(evt, FieldType.Field, "duration"); } } catch (Exception e) { logger.Error(e, "Error converting XE data from the stream."); throw; } if (workloadEvent.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(workloadEvent.Text)) { continue; } workloadEvent.Text = transformer.Transform(workloadEvent.Text); } Events.Enqueue(workloadEvent); EventCount++; } catch (Exception ex) { logger.Error($"Error converting XE data from the stream: {ex.Message}"); try { logger.Error($" event type : {workloadEvent.Type}"); logger.Error($" client_app_name : {TryGetString(evt, FieldType.Action, "client_app_name")}"); logger.Error($" database_name : {TryGetString(evt, FieldType.Action, "database_name")}"); logger.Error($" client_hostname : {TryGetString(evt, FieldType.Action, "client_hostname")}"); logger.Error($" server_principal_name : {TryGetString(evt, FieldType.Action, "server_principal_name")}"); logger.Error($" session_id : {TryGetString(evt, FieldType.Action, "session_id")}"); logger.Error($" duration : {TryGetString(evt, FieldType.Field, "duration")}"); logger.Error($" logical_reads : {TryGetString(evt, FieldType.Field, "logical_reads")}"); logger.Error($" writes : {TryGetString(evt, FieldType.Field, "writes")}"); logger.Error($" cpu_time : {TryGetString(evt, FieldType.Field, "cpu_time")}"); } catch (Exception) { //ignore, it is only logging } throw; } } } }
private void ReadEventsFromFile() { try { // get first trace rollover file var parentDir = Directory.GetParent(tracePath); var fileName = Path.GetFileNameWithoutExtension(tracePath) + "*" + Path.GetExtension(tracePath); List <string> files = Directory.GetFiles(parentDir.FullName, fileName).ToList(); files.Sort(); SqlTransformer transformer = new SqlTransformer(); int rowsRead = 0; foreach (string traceFile in files) { using (TraceFileWrapper reader = new TraceFileWrapper()) { reader.InitializeAsReader(traceFile); Dictionary <string, string> ColumnNames = null; while (reader.Read() && !stopped) { try { if (ColumnNames == null) { ColumnNames = new Dictionary <string, string>(); string[] colNames = { "EventClass", "ApplicationName", "HostName", "LoginName", "SPID", "TextData", "StartTime", "Reads", "Writes", "CPU", "Duration" }; foreach (var s in colNames) { if (reader.HasAttribute(s)) { ColumnNames.Add(s, s); } } } ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent(); if (reader.GetValue("EventClass").ToString() == "RPC:Completed") { evt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (reader.GetValue("EventClass").ToString() == "SQL:BatchCompleted") { evt.Type = WorkloadEvent.EventType.BatchCompleted; } else { evt.Type = WorkloadEvent.EventType.Unknown; continue; } if (ColumnNames.ContainsKey("ApplicationName")) { evt.ApplicationName = (string)reader.GetValue("ApplicationName"); } if (ColumnNames.ContainsKey("DatabaseName")) { evt.DatabaseName = (string)reader.GetValue("DatabaseName"); } if (ColumnNames.ContainsKey("HostName")) { evt.HostName = (string)reader.GetValue("HostName"); } if (ColumnNames.ContainsKey("LoginName")) { evt.LoginName = (string)reader.GetValue("LoginName"); } if (ColumnNames.ContainsKey("SPID")) { evt.SPID = (int?)reader.GetValue("SPID"); } if (ColumnNames.ContainsKey("TextData")) { evt.Text = (string)reader.GetValue("TextData"); } if (ColumnNames.ContainsKey("StartTime")) { evt.StartTime = (DateTime)reader.GetValue("StartTime"); } if (ColumnNames.ContainsKey("Reads")) { evt.Reads = (long?)reader.GetValue("Reads"); } if (ColumnNames.ContainsKey("Writes")) { evt.Writes = (long?)reader.GetValue("Writes"); } if (ColumnNames.ContainsKey("CPU")) { evt.CPU = (long?)Convert.ToInt64(reader.GetValue("CPU")) * 1000; // SqlTrace captures CPU as milliseconds => convert to microseconds } if (ColumnNames.ContainsKey("Duration")) { evt.Duration = (long?)reader.GetValue("Duration"); } if (transformer.Skip(evt.Text)) { continue; } if (!Filter.Evaluate(evt)) { continue; } evt.Text = transformer.Transform(evt.Text); Events.Enqueue(evt); rowsRead++; } catch (Exception ex) { logger.Error(ex.Message); if (ex.InnerException != null) { logger.Error(ex.InnerException.Message); } } } // while (Read) } // using reader } // foreach file finished = true; } catch (Exception ex) { logger.Error(ex.Message); if (ex.InnerException != null) { logger.Error(ex.InnerException.Message); } Dispose(); } }
private void ReadEventsFromTDS() { string sqlReadTrace = @" SELECT EventSequence ,Error ,TextData ,BinaryData ,DatabaseID ,HostName ,ApplicationName ,LoginName ,SPID ,Duration ,StartTime ,EndTime ,Reads ,Writes ,CPU ,EventClass ,DatabaseName FROM fn_trace_gettable(@path, {0}) "; string sqlPath = @" SELECT path FROM sys.traces WHERE id = @traceId; "; long lastEvent = -1; string lastTraceFile = ""; try { while (!stopped) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConnectionInfo.ConnectionString; conn.Open(); SqlCommand cmdPath = conn.CreateCommand(); cmdPath.CommandText = sqlPath; if (traceId == -1) { traceId = GetTraceId(conn, Path.Combine(tracePath, "sqlworkload")); if (traceId == -1) { throw new InvalidOperationException("The SqlWorkload capture trace is not running."); } } var paramTraceId = cmdPath.Parameters.Add("@traceId", System.Data.SqlDbType.Int); paramTraceId.Value = traceId; string currentTraceFile = null; try { currentTraceFile = (string)cmdPath.ExecuteScalar(); } catch (Exception e) { logger.Error(e.StackTrace); throw; } string filesParam = "1"; string pathToTraceParam = currentTraceFile; // check if file has changed if (lastTraceFile != currentTraceFile && !String.IsNullOrEmpty(lastTraceFile)) { // when the rollover file changes, read from the last read file // up to the end of all rollover files (this is what DEFAULT does) filesParam = "DEFAULT"; pathToTraceParam = lastTraceFile; // Check if the previous file still exists using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = String.Format(@" SET NOCOUNT ON; DECLARE @t TABLE (FileExists bit, FileIsADicrectory bit, ParentDirectoryExists bit); INSERT @t EXEC xp_fileexist '{0}'; SELECT FileExists FROM @t; ", lastTraceFile); if (!(bool)cmd.ExecuteScalar()) { pathToTraceParam = Path.Combine(tracePath, "sqlworkload.trc"); } } } lastTraceFile = currentTraceFile; String sql = String.Format(sqlReadTrace, filesParam); if (lastEvent > 0) { sql += "WHERE EventSequence > @lastEvent"; } using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; var paramPath = cmd.Parameters.Add("@path", System.Data.SqlDbType.NVarChar, 255); paramPath.Value = pathToTraceParam; var paramLastEvent = cmd.Parameters.Add("@lastEvent", System.Data.SqlDbType.BigInt); paramLastEvent.Value = lastEvent; int rowsRead = 0; SqlTransformer transformer = new SqlTransformer(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader["EventSequence"] != DBNull.Value) { lastEvent = (long)reader["EventSequence"]; } ExecutionWorkloadEvent evt = new ExecutionWorkloadEvent(); int eventClass = (int)reader["EventClass"]; if (eventClass == (int)EventClassEnum.RPC_Completed) { evt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (eventClass == (int)EventClassEnum.SQL_BatchCompleted) { evt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (eventClass == (int)EventClassEnum.Timeout) { if (reader["TextData"].ToString().StartsWith("WorkloadTools.Timeout[")) { evt.Type = WorkloadEvent.EventType.Timeout; } } else { evt.Type = WorkloadEvent.EventType.Unknown; continue; } if (reader["ApplicationName"] != DBNull.Value) { evt.ApplicationName = (string)reader["ApplicationName"]; } if (reader["DatabaseName"] != DBNull.Value) { evt.DatabaseName = (string)reader["DatabaseName"]; } if (reader["HostName"] != DBNull.Value) { evt.HostName = (string)reader["HostName"]; } if (reader["LoginName"] != DBNull.Value) { evt.LoginName = (string)reader["LoginName"]; } evt.SPID = (int?)reader["SPID"]; if (reader["TextData"] != DBNull.Value) { evt.Text = (string)reader["TextData"]; } evt.StartTime = (DateTime)reader["StartTime"]; if (evt.Type == WorkloadEvent.EventType.Timeout) { if (reader["BinaryData"] != DBNull.Value) { byte[] bytes = (byte[])reader["BinaryData"]; evt.Text = Encoding.Unicode.GetString(bytes); } evt.Duration = ExtractTimeoutDuration(reader["TextData"]); evt.CPU = Convert.ToInt64(evt.Duration); } else { evt.Reads = (long?)reader["Reads"]; evt.Writes = (long?)reader["Writes"]; evt.CPU = (long?)Convert.ToInt64(reader["CPU"]) * 1000; // SqlTrace captures CPU as milliseconds => convert to microseconds evt.Duration = (long?)reader["Duration"]; } if (transformer.Skip(evt.Text)) { continue; } if (!Filter.Evaluate(evt)) { continue; } evt.Text = transformer.Transform(evt.Text); Events.Enqueue(evt); rowsRead++; } } // Wait before querying the trace file again if (rowsRead < TraceRowsSleepThreshold) { Thread.Sleep(TraceIntervalSeconds * 1000); } } } } } catch (Exception ex) { logger.Error(ex.Message); logger.Error(ex.StackTrace); if (ex.InnerException != null) { logger.Error(ex.InnerException.Message); } Dispose(); } }
private void ReadXEData(SqlConnection conn, ReadIteration currentIteration) { string sqlXE = @" SELECT event_data, file_name, file_offset FROM sys.fn_xe_file_target_read_file( @filename, NULL, @initial_file_name, @initial_offset ) "; logger.Debug("Reading XE data..."); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlXE; var paramPath = cmd.Parameters.Add("@filename", System.Data.SqlDbType.NVarChar, 260); if (ServerType != ExtendedEventsWorkloadListener.ServerType.AzureSqlDatabase) { paramPath.Value = currentIteration.GetXEFilePattern(); } else { // Azure SqlDatabase does not support wildcards in file names // Specify an exact file name paramPath.Value = currentIteration.StartFileName; } var paramInitialFile = cmd.Parameters.Add("@initial_file_name", System.Data.SqlDbType.NVarChar, 260); paramInitialFile.Value = currentIteration.StartFileName; var paramInitialOffset = cmd.Parameters.Add("@initial_offset", System.Data.SqlDbType.BigInt); paramInitialOffset.Value = currentIteration.GetInitialOffset(); // don't pass initial file name and offset // read directly from the initial file // until we have some rows read already if ( EventCount == 0 || currentIteration.StartOffset <= 0 || currentIteration.StartOffset == currentIteration.MinOffset ) { if (ServerType != ExtendedEventsWorkloadListener.ServerType.LocalDB) { paramPath.Value = currentIteration.StartFileName; } paramInitialFile.Value = DBNull.Value; paramInitialOffset.Value = DBNull.Value; } retryWithNULLS: logger.Debug($"paramPath : {paramPath.Value}"); logger.Debug($"paramInitialFile : {paramInitialFile.Value}"); logger.Debug($"paramInitialOffset: {paramInitialOffset.Value}"); // in case we don't have any data in the xe file // GetInitialOffset returns -1 and we need to wait a bit // to let events flow to the file target if (currentIteration.GetInitialOffset() > 0) { SqlTransformer transformer = new SqlTransformer(); using (var reader = cmd.ExecuteReader()) { try { int skippedRows = 0; while (reader.Read()) { if (reader["file_name"] != DBNull.Value) { currentIteration.EndFileName = (string)reader["file_name"]; } if (reader["file_offset"] != DBNull.Value) { currentIteration.EndOffset = (long)reader["file_offset"]; } string xmldata = (string)reader["event_data"]; XmlDocument doc = new XmlDocument(); doc.LoadXml(xmldata); var evt = parseEvent(doc); // skip to the correct event in case we're reading again // from the same file and we have a reference sequence if ((currentIteration.RowsRead == 0) && (currentIteration.StartSequence > 0)) { // skip rows until we encounter the reference event_sequence if (evt.EventSequence != currentIteration.StartSequence) { skippedRows++; continue; } else { // skip one more row... skippedRows++; currentIteration.RowsRead++; continue; } } // this is only to print out a message, so consider // getting rid of it if (skippedRows > 0) { logger.Debug($"Skipped rows: {skippedRows}"); skippedRows = 0; } // now we have an event, no matter if good or bad => increment rows read currentIteration.RowsRead++; if (evt.EventSequence != null) { currentIteration.EndSequence = (long)evt.EventSequence; } if (evt.Type == WorkloadEvent.EventType.Unknown) { continue; } if (evt.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(evt.Text)) { continue; } evt.Text = transformer.Transform(evt.Text); } // it's a "good" event: add it to the queue Events.Enqueue(evt); EventCount++; } logger.Debug($"currentIteration.EndSequence : {currentIteration.EndSequence}"); } catch (Exception xx) { if (xx.Message.Contains("Specify an offset that exists in the log file")) { // retry the query without specifying the offset / file pair paramInitialFile.Value = DBNull.Value; paramInitialOffset.Value = DBNull.Value; goto retryWithNULLS; } else { throw; } } } } // Wait before querying the events file again if (currentIteration.RowsRead < ReadIteration.DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD) { Thread.Sleep(ReadIteration.DEFAULT_TRACE_INTERVAL_SECONDS * 1000); } } }
public override WorkloadEvent Read() { WorkloadEvent result = null; long commandOffset = 0; // first I need to return the event that // contains the total number of events in the file // once this is done I can start sending the actual events if (!totalEventsMessageSent) { totalEventsMessageSent = true; return(totalEventsMessage); } // process actual events from the file try { if (reader == null) { return(null); } bool validEventFound = false; SqlTransformer transformer = new SqlTransformer(); do { if (!reader.Read()) { stopped = true; return(null); } result = ReadEvent(reader); // Handle replay sleep for synchronization mode // The sleep cannot happen here, but it has to // happen later in the replay workflow, because // it would only delay the insertion in the queue // and it would not separate the events during the replay if (result is ExecutionWorkloadEvent) { ExecutionWorkloadEvent execEvent = result as ExecutionWorkloadEvent; if (SynchronizationMode) { if (startTime != DateTime.MinValue) { commandOffset = (long)((result.StartTime - startTime).TotalMilliseconds); if (commandOffset > 0) { execEvent.ReplayOffset = commandOffset; } } else { startTime = execEvent.StartTime; } } else { // Leave it at 0. The replay consumer will interpret this // as "do not wait for the requested offset" and will replay // the event without waiting execEvent.ReplayOffset = 0; } // preprocess and filter events if (execEvent.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(execEvent.Text)) { continue; } execEvent.Text = transformer.Transform(execEvent.Text); } } // Filter events if (result is ExecutionWorkloadEvent) { validEventFound = Filter.Evaluate(result); } else { validEventFound = true; } }while (!validEventFound); } catch (Exception e) { if (stopped) { return(null); } DateTime?eventDate = null; if (result != null) { eventDate = result.StartTime; } logger.Error(e); logger.Error($"Unable to read next event. Current event date: {eventDate}"); throw; } return(result); }