} // end of GenerateFastSqlSingleRow function // Generates INSERT statement for a single data row - static dictionary private static string GenerateSqlStaticDict(int targetId, MetricGroup metricGroup, ProbeResultingData data, List <int> rowsNotInDict) { string sqlStmt = "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " ("; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ","; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma sqlStmt += ")" + Environment.NewLine; sqlStmt += "VALUES " + Environment.NewLine; foreach (int i in rowsNotInDict) { sqlStmt += "("; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[i, j]) + ","; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma sqlStmt += "),"; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma return(sqlStmt); } // end of GenerateSqlStaticDict method
} // end of CreateCacheTableSingleRowRealtime /// <summary> Returns cache key name /// For single-row metric cache is common for all targets (table name only, no schema) /// Multi-row metrics each have its own cache </summary> /// <param name="targetId">target id or -1 for single row metrics</param> /// <param name="metricGroup">metric group</param> /// <param name="CacheType">data/dictionary</param> public static string GetCacheKey(int targetId, MetricGroup metricGroup, CacheType cacheType = CacheType.Data) { switch (cacheType) { case CacheType.Data: if (metricGroup.isMultiRow) { return(SqlServerProbe.DataTableName(targetId, metricGroup)); } else { return(metricGroup.dataTableName); } case CacheType.Dictionary: return(SqlServerProbe.DictTableName(targetId, metricGroup)); default: throw new Exception("Unsupported cache type"); } } // end of GetCacheKey method
// Loads dictionary from repository into in-memory cache. Creates a new record in dictionaryCache public static void LoadDictionaryIntoCache(int targetId, MetricGroup metricGroup, bool allowReload, SqlConnection connection = null, SqlTransaction transaction = null) { string cacheKey; SqlConnection conn; int tryCount = 0; bool canExit = false; // create new in-memory cache for dictionary if (!ContainsKey(GetCacheKey(targetId, metricGroup, CacheType.Dictionary))) { Dictionary <int, Column> keyColumns = new Dictionary <int, Column>(); Dictionary <int, Column> attrColumns = new Dictionary <int, Column>(); // key columns for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { keyColumns.Add(i, metricGroup.multiRowKeys[i]); } // attribute columns for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { attrColumns.Add(i, metricGroup.multiRowKeyAttributes[i]); } TryAdd(GetCacheKey(targetId, metricGroup, CacheType.Dictionary), new CacheTable(keyColumns, attrColumns, false)); } cacheKey = GetCacheKey(targetId, metricGroup, CacheType.Dictionary); CacheTable tmpCache = _cache[cacheKey].CloneAndClear(); // don't reload cache unless allowReload is specified if (allowReload == false && tmpCache.loadedFromDatabase) { return; } string sqlStmt = "SELECT id, "; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { sqlStmt += metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ", "; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { sqlStmt += metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ", "; } if (metricGroup.multiRowKeyAttributesChangeSpeed == ChangeSpeed.Static) { sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma } else { sqlStmt += "startDate "; } sqlStmt += Environment.NewLine + "FROM " + SqlServerProbe.DictTableName(targetId, metricGroup); if (metricGroup.multiRowKeyAttributesChangeSpeed == ChangeSpeed.Slow) { sqlStmt += Environment.NewLine + "WHERE endDate IS NULL"; } _logger.Trace(sqlStmt); while (!canExit && tryCount < 2) { try { if (connection == null) { conn = new SqlConnection(Configuration.GetReposConnectionString("Cache")); conn.Open(); } else { conn = connection; } using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlStmt; cmd.CommandType = System.Data.CommandType.Text; if (transaction != null) { cmd.Transaction = transaction; } using (SqlDataReader dataReader = cmd.ExecuteReader()) { int id; object[] keys = new object[metricGroup.NumberOfMultiRowKeys]; object[] values = new object[metricGroup.NumberOfMultiRowKeyAttributes]; while (dataReader.Read()) { id = (int)dataReader["id"]; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { keys[i] = dataReader[1 + i]; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { values[i] = dataReader[1 + metricGroup.NumberOfMultiRowKeys + i]; } // add record to dictionary switch (metricGroup.multiRowKeyAttributesChangeSpeed) { case ChangeSpeed.Static: tmpCache.Add(id, keys, values); break; case ChangeSpeed.Slow: tmpCache.Add(id, keys, values, (DateTime)dataReader[1 + metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes]); break; default: throw new Exception("Only Static and Slow changing dictionaries are supported"); } } tmpCache.loadedFromDatabase = true; Replace(cacheKey, tmpCache); dataReader.Close(); } } if (connection == null) { conn.Close(); } } catch (SqlException e) { if (e.Number == 208) // Invalid object { // Do not create tables if target has been deleted if (!Configuration.targets.ContainsKey(targetId)) { return; } SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup); } else { _logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString()); } } // end of catch tryCount++; } // end of while } // end of LoadDictionaryIntoCache function
} // end of WriteFastSingleRowToRepository function /// <summary>Saves data into dictionary and data table for multi-value metrics</summary> private void WriteMultipleRowsToRepository(int targetId, MetricGroup metricGroup, ProbeResultingData data) { int id; CacheTable dictCache, dataCache; List <int> newDictRows; List <Tuple <int, int> > oldDictRows; object[] key, attributes; string dataTableName, dictTableName; byte tryCount = 0; bool canExit = false; SqlTransaction tran = null; string dictSqlStmt = string.Empty; string dataSqlStmt = string.Empty; newDictRows = new List <int>(); // ids of records that should be added to the dictionary (new rows or rows with updated attributes) oldDictRows = new List <Tuple <int, int> >(); // ids and dictionary ids of records that changed since last probe and need to be closed dataTableName = SqlServerProbe.DataTableName(targetId, metricGroup); _logger.Debug("Name of data table: " + dataTableName); dictTableName = SqlServerProbe.DictTableName(targetId, metricGroup); _logger.Debug("Name of dictionary: " + dictTableName); // load the dictionary cache table if it doesn't exist if (!InMemoryCache.ContainsKey(dictTableName)) { InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, false); } dictCache = Configuration.inMemoryCache[dictTableName]; // load the dictionary cache table if it doesn't exist if (!InMemoryCache.ContainsKey(dataTableName)) { InMemoryCache.LoadDataIntoCache(targetId, metricGroup, false); } /* * Checks for changed or new records in dictionary and if needed prepares SQL statement to update dictionary table */ switch (metricGroup.multiRowKeyAttributesChangeSpeed) { case ChangeSpeed.Static: // check whether all records are in the dictionary or some need to be added to it for (int i = 0; i < data.NumberOfRows; i++) { key = new object[metricGroup.NumberOfMultiRowKeys]; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { key[j] = data.values[i, j]; } if (dictCache.GetIdByKey(key) == -1) { newDictRows.Add(i); } } // generate SQL statements if there are any new dictionary records if (newDictRows.Count > 0) { dictSqlStmt = GenerateSqlStaticDict(targetId, metricGroup, data, newDictRows); } break; case ChangeSpeed.Slow: // check whether all records are in the dictionary or some need to be added to it for (int i = 0; i < data.NumberOfRows; i++) { key = new object[metricGroup.NumberOfMultiRowKeys]; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { key[j] = data.values[i, j]; } id = dictCache.GetIdByKey(key); if (id == -1) { newDictRows.Add(i); } else // check that attributes match { attributes = new object[metricGroup.NumberOfMultiRowKeyAttributes]; for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++) { attributes[j] = data.values[i, metricGroup.NumberOfMultiRowKeys + j]; } if (!dictCache.CompareAttributesForKey(id, attributes)) { oldDictRows.Add(new Tuple <int, int>(i, id)); // this is to close the old record - UPDATE } } } // generate SQL statements if there are any changes or new records in dictionary if (oldDictRows.Count > 0 || newDictRows.Count > 0) { dictSqlStmt = GenerateSqlSlowDict(targetId, metricGroup, data, oldDictRows, newDictRows); } break; default: throw new Exception("Unknown dictionary change speed"); } /* * Write new data into dictionary but don't close transaction yet */ if (dictSqlStmt.CompareTo(string.Empty) != 0) { _logger.Trace(dictSqlStmt); // If tables don't exist, will try to create them and rerun SQL statements while (!canExit && tryCount < 2) { try { // we will write to the dictionary first and then to the data table so we need to begin a transaction tran = this.reposConn.BeginTransaction(); if (dictSqlStmt.CompareTo(string.Empty) != 0) { // save dictionary changes using (SqlCommand cmd = this.reposConn.CreateCommand()) { cmd.Transaction = tran; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = dictSqlStmt; int rowCount = cmd.ExecuteNonQuery(); _logger.Debug("Rows affected: " + rowCount.ToString()); } } InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, true, this.reposConn, tran); canExit = true; } catch (SqlException e) { if (tran != null) { tran.Rollback(); tran.Dispose(); tran = null; } switch (e.Number) { case 208: // Invalid object // Do not create tables if target has been deleted if (!Configuration.targets.ContainsKey(targetId)) { return; } SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup); break; default: _logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString()); break; } } tryCount++; } } /* * Prepare SQL statement to save data with right references to the dictionary records */ switch (metricGroup.changeSpeed) { case ChangeSpeed.Fast: dataSqlStmt = "INSERT INTO " + dataTableName + " (dt,dictId,"; for (int i = 0; i < metricGroup.NumberOfMetrics; i++) { dataSqlStmt += metricGroup.metrics[i].name.Replace(' ', '_') + ","; } dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma dataSqlStmt += ")" + Environment.NewLine + "VALUES"; for (int i = 0; i < data.NumberOfRows; i++) { dataSqlStmt += Environment.NewLine + "('" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',"; // retrieve corresponding id from dictionary key = new object[metricGroup.NumberOfMultiRowKeys]; for (int k = 0; k < metricGroup.NumberOfMultiRowKeys; k++) { key[k] = data.values[i, k]; } id = dictCache.GetIdByKey(key); dataSqlStmt += id.ToString() + ","; // add metric values for (int j = 0; j < metricGroup.NumberOfMetrics; j++) { dataSqlStmt += SqlServerProbe.DataValueToString(metricGroup.metrics[j].type, data.values[i, metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes + j]) + ","; } dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma dataSqlStmt += "),"; } dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma _logger.Trace(dataSqlStmt); break; default: throw new Exception("Unsupported data change speed"); } /* * Executes SQL statements * If tables don't exist, will try to create them and rerun SQL statements */ try { // save data using (SqlCommand cmd = this.reposConn.CreateCommand()) { if (tran != null) // use same transaction as for the dictionary { cmd.Transaction = tran; } cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = dataSqlStmt; int rowCount = cmd.ExecuteNonQuery(); _logger.Debug("Rows affected: " + rowCount.ToString()); } if (tran != null) { tran.Commit(); } InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, true); dictCache = Configuration.inMemoryCache[dictTableName]; // Update in-memory data cache object[] newValues; dataCache = Configuration.inMemoryCache[dataTableName]; for (int i = 0; i < data.NumberOfRows; i++) { key = new object[metricGroup.NumberOfMultiRowKeys]; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { key[j] = data.values[i, j]; } id = dictCache.GetIdByKey(key); newValues = new object[metricGroup.NumberOfMetrics]; for (int j = 0; j < metricGroup.NumberOfMetrics; j++) { newValues[j] = data.values[i, metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes + j]; } dataCache.AddOrUpdateRowValues(id, new object[0], newValues); } canExit = true; } catch (SqlException e) { _logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString()); if (tran != null) { tran.Rollback(); InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, true); } } }
} // end of GenerateSqlStaticDict method // Generates UPDATE statement for closing records and INSERT statement for openning records private static string GenerateSqlSlowDict(int targetId, MetricGroup metricGroup, ProbeResultingData data, List <Tuple <int, int> > rowsChanged, List <int> rowsNotInDict) { CacheTable dict = Configuration.inMemoryCache[InMemoryCache.GetCacheKey(targetId, metricGroup, CacheType.Dictionary)]; string sqlStmt = string.Empty; // old rows where endDate need to be updated if (rowsChanged.Count > 0) { sqlStmt = "UPDATE " + SqlServerProbe.DictTableName(targetId, metricGroup) + Environment.NewLine; sqlStmt += "SET endDate = '" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "'" + Environment.NewLine; sqlStmt += "WHERE"; foreach (Tuple <int, int> ids in rowsChanged) { sqlStmt += Environment.NewLine + "(id = " + ids.Item2.ToString() + " AND "; sqlStmt += "startDate = '" + SqlServerProbe.DateTimeToString((DateTime)dict[ids.Item2][metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes]) + "')"; sqlStmt += " OR "; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 4); // remove last ' OR ' sqlStmt += ";" + Environment.NewLine; // new records for changed rows sqlStmt += "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " (id,"; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ","; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ","; } sqlStmt += "startDate,endDate)" + Environment.NewLine; sqlStmt += "VALUES " + Environment.NewLine; foreach (Tuple <int, int> ids in rowsChanged) { sqlStmt += "(" + ids.Item2.ToString() + ","; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[ids.Item1, j]) + ","; } for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeyAttributes[j].type, data.values[ids.Item1, metricGroup.NumberOfMultiRowKeys + j]) + ","; } // add startDate and endDate sqlStmt += "'" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',NULL),"; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma } // new rows if (rowsNotInDict.Count > 0) { sqlStmt += "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " (id,"; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ","; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ","; } sqlStmt += "startDate,endDate)" + Environment.NewLine; sqlStmt += "VALUES " + Environment.NewLine; foreach (int i in rowsNotInDict) { sqlStmt += "(NEXT VALUE FOR " + SqlServerProbe.SchemaName(targetId) + ".seq_" + metricGroup.dictTableName + ","; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[i, j]) + ","; } for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeyAttributes[j].type, data.values[i, metricGroup.NumberOfMultiRowKeys + j]) + ","; } // add startDate and endDate sqlStmt += "'" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',NULL),"; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma } return(sqlStmt); } // end of GenerateSqlSlowDict method
// returns a set of datetime-value tuples in a JSON string // param0: MetricGroupName - MetricGroup->Name // param1: TargetId - Targets->Id // param2: StartDateTime - YYYYMMDDHHMM // param3: EndDateTime - YYYYMMDDHHMM // param4: Interval - M in minutes // param5: MetricName - MetricGroup->metrics->name // param6: NumOfRowsToReturn - Number of records to return (TOP X) // param7: DictionaryKeyName - MetricGroup->multiRowKeys->name // param8: Optional. Dictionary keys to exclude // test string ok http://localhost:3128/ws/fastmulti/Range/SQL%20Server%20Wait%20Stats/0/201305201800/201305201900/5/Wait%20Time%20ms/5/Wait%20Type/ // test string not ok http://localhost:3128/ws/fastmulti/Range/SQL%20Server%20Wait%20Stats/0/201305201800/201305M900/5/Wait%20Time%20ms/10/Wait%20Type/ private WebServiceResult Range(string[] parameters) { if (parameters.Count() < 8) { return(WebServiceResult.ReturnError(GetType().Name + ".Range(): too few parameters. Format is MetricGroupName/TargetId/StartDateTime/EndDateTime/IntervalInMinutes/MetricName/DictionaryKeyName/[DictionaryKeysToExclude]")); } if (parameters.Count() > 9) { return(WebServiceResult.ReturnError(GetType().Name + ".Range(): too many parameters. Format is MetricGroupName/TargetId/StartDateTime/EndDateTime/IntervalInMinutes/MetricName/numOfRowsToReturn/DictionaryKeyName/[DictionaryKeysToExclude]")); } Regex r = new Regex("^[0-9]*$"); // check that supplied target id is valid if (!r.IsMatch(parameters[1]) || !Configuration.targets.ContainsId(Convert.ToInt32(parameters[1]))) { return(WebServiceResult.ReturnError(GetType().Name + ".Range(): TargetId is either not numeric or target with specified id doesn't exist")); } if (!r.IsMatch(parameters[2]) || !r.IsMatch(parameters[3]) || !r.IsMatch(parameters[4])) { return(WebServiceResult.ReturnError(GetType().Name + ".Range(): TargetId or StartDateTime or EndDateTime or Interval is not numeric. Format is MetricGroupName/TargetId/StartDateTime/EndDateTime/IntervalInMinutes/MetricName/DictionaryKeyName/[DictionaryKeysToExclude]")); } if (parameters[2].Length != 12 || parameters[3].Length != 12) { return(WebServiceResult.ReturnError(GetType().Name + ".Range(): StartDateTime and EndDateTime must be in YYYYMMDDHHMM format")); } if (!r.IsMatch(parameters[6])) { return(WebServiceResult.ReturnError(GetType().Name + ".Range(): NumOfRowsToReturn is not numeric")); } // look up metric group by name var metricGroup = Configuration.metricGroups[parameters[0]]; string metricColumn = parameters[5].Replace(' ', '_'); string numOfRowsToReturn = parameters[6]; string exclusionColumn = parameters[7].Replace(' ', '_'); string excludedValues = string.Empty; if (parameters.Count() == 9) { excludedValues = parameters[8]; } // prepare parameters SqlParameters sqlParameters = new SqlParameters { { "@dataTable", SqlServerProbe.DataTableName(Convert.ToInt32(parameters[1]), metricGroup) }, { "@dictionary", SqlServerProbe.DictTableName(Convert.ToInt32(parameters[1]), metricGroup) }, { "@start_dt", SqlServerProbe.FormatDate(parameters[2]) }, { "@end_dt", SqlServerProbe.FormatDate(parameters[3]) }, { "@interval", parameters[4] }, { "@metricColumn", metricColumn }, { "@numOfRowsToReturn", numOfRowsToReturn }, { "@exclusionColumn", exclusionColumn }, { "@excludedValues", excludedValues } }; // execute procedure and return results if (metricGroup.isCumulative) { return(GetData(System.Data.CommandType.StoredProcedure, "dbo.GetRangeMultiRowFastCumulative", sqlParameters)); } return(GetData(System.Data.CommandType.StoredProcedure, "dbo.GetRangeMultiRowFastNonCumulative", sqlParameters)); }