private static string ExecuteSQL(string label, string sql) { string queryTag = Guid.NewGuid().ToString(); //This should move to GetAndPrepSqlText? if (appendStatementLabel) { //sql = sql.Replace("~##LABEL##~", queryTag); //sql = sql.TrimEnd().TrimEnd(';') + String.Format(" OPTION (LABEL = 'Tag: {0}');", queryTag); sql = sql.TrimEnd().TrimEnd(';') + String.Format(" --'Tag: {0}'", queryTag); } SimpleLoggingSqlHelper telemetryHelper = new SimpleLoggingSqlHelper(); string testRunId = null; Console.WriteLine("BEGIN: " + label); SqlConnection connection = new SqlConnection(connectionString); try { //using (SqlConnection connection = new SqlConnection(connectionString)) //{ connection.Open(); testRunId = telemetryHelper.LogTestRunStart(loggingConnectionString, _testId); using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandTimeout = 0; DateTime startTime = DateTime.Now; command.ExecuteNonQuery(); TimeSpan duration = DateTime.Now.Subtract(startTime); telemetryHelper.LogTestRunEnd(loggingConnectionString, _testId, label, (int)duration.TotalMilliseconds, 1, queryTag, null); } //} } catch (SqlException e) { telemetryHelper.LogTestRunEnd(loggingConnectionString, _testId, label, 0, 1, queryTag, e); Console.WriteLine("*********** ERROR: " + e.ToString()); int sleepFor = 1000; Console.WriteLine("Pausing for {0} msec", sleepFor); System.Threading.Thread.Sleep(sleepFor); } finally { connection.Close(); connection.Dispose(); Console.WriteLine("END: " + label); } return(label); } // Execute SQL
private static async Task MainLoopAsync(int concurrencyLevel, int testDurationInMinutes, Dictionary <string, string> sqlScripts) { //Create header record for test exec times _testId = SimpleLoggingSqlHelper.LogTestPassStart(loggingConnectionString, "Concurrent Test"); //Extract a copy of the filenames (dictionary keys) as an array to be used for randomly picking the next test string[] filenames = sqlScripts.Keys.ToArray <string>(); //This list will hold Task objects to kick off individual queries List <Task <string> > executeSQLTasks = new List <Task <string> >(); //Calculate the time at which the test should end DateTime runUntilTime = DateTime.Now.AddMinutes(testDurationInMinutes); int fileListIndex = 0; //Load the initial set of tasks (up to CONCURRENCY_LEVEL) into a list //int nextIndex = 0; for (int i = 0; i < concurrencyLevel; i++) //while (nextIndex < concurrencyLevel) { string nextFilename = ""; if (fileSelectionMode == FileSelectionModeEnum.Random) { nextFilename = GetRandomElement(filenames); } else { nextFilename = filenames[fileListIndex]; } string sqlText = GetAndPrepSqlText(new StringBuilder(sqlScripts[nextFilename])); executeSQLTasks.Add(ExecuteSQLAsync(nextFilename, sqlText)); fileListIndex = GetNextFileIndex(fileListIndex, filenames.Length); if (fileListIndex == -1) { break; } //nextIndex++; } /** MAIN TEST LOOP **/ while (executeSQLTasks.Count > 0 && DateTime.Now.CompareTo(runUntilTime) <= 0) { try { Task <string> sqlTask = await Task.WhenAny(executeSQLTasks); string filename = sqlTask.Result; executeSQLTasks.Remove(sqlTask); completedTasks++; Console.WriteLine("Popped {0}", filename); } catch (Exception exc) { Console.WriteLine(exc); } if (DateTime.Now.CompareTo(runUntilTime) <= 0) { string nextFilename = ""; //Depending on file selection mode, get the next filename to load into the executeSQLTasks List //For Random and SequentialLoop, we always expect a 'next file'. //For SinglePass, when the list runs out we set the 'next file' to empty string. switch (fileSelectionMode) { case FileSelectionModeEnum.Random: nextFilename = GetRandomElement(filenames); break; case FileSelectionModeEnum.SequentialLoop: case FileSelectionModeEnum.SinglePass: if (fileListIndex >= 0) { nextFilename = filenames[fileListIndex]; fileListIndex = GetNextFileIndex(fileListIndex, filenames.Length); } else { nextFilename = ""; } break; } //If the nextFileName is blank, we have nothing more to add to the executeSQLTasks List if (!String.IsNullOrEmpty(nextFilename)) { string sqlText = GetAndPrepSqlText(new StringBuilder(sqlScripts[nextFilename])); executeSQLTasks.Add(ExecuteSQLAsync(nextFilename, sqlText)); } else { Console.WriteLine("No new file to add to work list. {0} - {1} - {2}.", fileListIndex, nextFilename, fileSelectionMode); } //if (nextIndex <= filenames.Length) //{ // string nextFilename = filenames[nextIndex]; // nextIndex++; // string sqlText = GetAndPrepSqlText(new StringBuilder(sqlScripts[nextFilename])); // executeSQLTasks.Add(ExecuteSQLAsync(nextFilename, sqlText)); //} } } /** END MAIN TEST LOOP **/ //Test period is now complete; there are probably a few running tasks still finishing Console.WriteLine("TEST TIME COMPLETE, completedTasks = {0}", completedTasks); SimpleLoggingSqlHelper.LogTestPassEnd(loggingConnectionString, _testId, 1); //Wait for any unfinished tasks await Task.WhenAll(executeSQLTasks); }
static async Task Main(string[] args) { /*** READ CONFIGURATION ***/ //Get test target connection string from app.config connectionString = ConfigurationManager.ConnectionStrings["targetDatabase"].ConnectionString; //Get database connection string from app.config for logging query times loggingConnectionString = ConfigurationManager.ConnectionStrings["telemetryDb"].ConnectionString; //Get the number of concurrent test connections to be generated from app.config int CONCURRENCY_LEVEL = GetAppSettingAsInt("concurrencyLimit"); //Get the number of minutes the test should run int TEST_DURATION_IN_MINUTES = GetAppSettingAsInt("testDurationInMinutes"); //Get the local path containing the SQL test script files. There should only be files with SQL text in this folder. string SCRIPT_PATH = ConfigurationManager.AppSettings["scriptFolder"]; //Get setting indicating whether Synapse DMVs info should be saved after the test string SAVE_DMVS = ConfigurationManager.AppSettings["saveSystemViews"]; //Get setting indicating whether to add text to the end of SQL statements to assist in correlation between driver telemetry and SQL Pool stystem views string APPEND_LABEL = ConfigurationManager.AppSettings["appendStatementLabel"]; if (!String.IsNullOrEmpty(APPEND_LABEL) && APPEND_LABEL == "true") { appendStatementLabel = true; } else { appendStatementLabel = false; } //Get fileSelectionMode switch (ConfigurationManager.AppSettings["fileSelectionMode"]) { case "SinglePass": fileSelectionMode = FileSelectionModeEnum.SinglePass; break; case "SequentialLoop": fileSelectionMode = FileSelectionModeEnum.SequentialLoop; break; default: fileSelectionMode = FileSelectionModeEnum.Random; break; } /*** RUN TEST ***/ //Read the test SQL scripts from local folder Dictionary <string, string> sqlScripts = LoadSqlFromFiles(SCRIPT_PATH); //Now do the real work await MainLoopAsync(CONCURRENCY_LEVEL, TEST_DURATION_IN_MINUTES, sqlScripts); //Persist System Views for Analysis if (SAVE_DMVS == "true") { SimpleLoggingSqlHelper.PersistSystemViews(connectionString, _testId); } //All done Console.WriteLine("FINISHED"); Console.ReadLine(); }