} // 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));
}