public int RowsAffected; // Total rows affected when the query was executed. /// <summary> /// Prints the Occurence Stats collected from the workload for each query. /// </summary> public static void PrintQueryOccurenceStats() { // Print output Headers var consoletable = new ConsoleTable("Query Text", "Total Occurences", "Query Identifier"); // Print declaration statement Console.WriteLine("###################### Printing Query Occurence Stats from the workload execution ####################"); foreach (KeyValuePair <Guid, SQLQueryStmt> qry in Utilities.DictWorkloadQueries) { consoletable.AddRow(qry.Value.QueryText.Replace(System.Environment.NewLine, " ").Substring(0, 35) + "...", qry.Value.NumOfOccurences, qry.Key); //consoletable.AddRow(qry.Value.QueryText.Replace(System.Environment.NewLine,""), qry.Value.NumOfOccurences, qry.Key); } consoletable.Write(Format.MarkDown); Console.WriteLine(); }
/// <summary> /// Executes the query agaisnt the database and benchmarks the execution parameters for each query. /// </summary> public static void BenchmarkWorload(string ServerName, string DBFilepath, string UserName, string Password, string DBName, Boolean IsBeforeSuggestions) { string ConnectionString = ""; if (UserName == "" && Password == "") { //Connection string example with Windows Authentication mode or Integrated Security mode. ConnectionString = @"Data Source=" + ServerName + @"; Initial Catalog=" + DBName + @"; Asynchronous Processing=True; Integrated Security=True; Connect Timeout=30"; } else { // Connection string example with UserName and Password: ConnectionString = @"Data Source=" + ServerName + @"; Initial Catalog=" + DBName + @"; User Id= " + UserName + @" ; Password= "******"; Asynchronous Processing=True; Connect Timeout=30"; } // Create and open the connection in a using block. This // ensures that all resources will be closed and disposed // when the code exits. using (SqlConnection connection = new SqlConnection(ConnectionString)) { string querytext = ""; SqlCommand cmd = new SqlCommand(querytext, connection); connection.Open(); if (IsBeforeSuggestions) { // Stage the SQLServer for execution. querytext = @"ALTER DATABASE AdventureWorks2016 SET QUERY_STORE CLEAR;"; cmd.CommandText = querytext; cmd.ExecuteNonQuery(); } querytext = @"ALTER DATABASE AdventureWorks2016 SET QUERY_STORE(QUERY_CAPTURE_MODE = All);"; cmd.CommandText = querytext; cmd.ExecuteNonQuery(); // Create Header for the data to be printed after benchmarking. var consoletable = new ConsoleTable("Query Id", "Query Text [Truncated]", "Rows Returned", "Successful Execution"); foreach (KeyValuePair <Guid, SQLQueryStmt> qry in Utilities.DictWorkloadQueries) { SqlDataAdapter adapter = new SqlDataAdapter(qry.Value.QueryText.ToString(), connection); DataSet dataSet = new DataSet(); // Open the connection in a try/catch block. try { adapter.Fill(dataSet); qry.Value.RowsAffected = dataSet.Tables[0].Rows.Count; consoletable.AddRow(qry.Key, qry.Value.QueryText.Replace(System.Environment.NewLine, " ").Substring(0, 35) + "...", dataSet.Tables[0].Rows.Count, (dataSet.Tables[0].Rows.Count != 0) ? "Execution Successful" : "Execution Successful but zero rows returned."); } catch (Exception ex) { Console.WriteLine(qry.Value.QueryText.ToString()); throw ex; } } querytext = @"ALTER DATABASE AdventureWorks2016 SET QUERY_STORE(QUERY_CAPTURE_MODE = None);"; cmd.CommandText = querytext; cmd.ExecuteNonQuery(); if (IsBeforeSuggestions) { querytext = @"select q.query_id, qt.query_sql_text, pl.plan_id, rs.count_executions, rs.first_execution_time, rs.last_execution_time, rs.min_logical_io_reads, rs.max_logical_io_reads, rs.min_physical_io_reads, rs.max_physical_io_reads, rs.min_logical_io_writes, rs.max_logical_io_writes, rs.min_cpu_time, rs.max_cpu_time from sys.query_store_query q inner join sys.query_store_query_text qt on q.query_id = qt.query_text_id inner join sys.query_store_plan pl on qt.query_text_id = pl.query_id inner join sys.query_store_runtime_stats rs on pl.plan_id = rs.plan_id"; } else { querytext = @"select * from (select q.query_id, qt.query_sql_text, pl.plan_id, rs.count_executions, rs.first_execution_time, rs.last_execution_time, rs.min_logical_io_reads, rs.max_logical_io_reads, rs.min_physical_io_reads, rs.max_physical_io_reads, rs.min_logical_io_writes, rs.max_logical_io_writes, rs.min_cpu_time, rs.max_cpu_time, Row_Number() OVER (PARTITION BY q.query_id ORDER BY pl.plan_id desc , rs.last_execution_time DESC) as RN from sys.query_store_query q inner join sys.query_store_query_text qt on q.query_id = qt.query_text_id inner join sys.query_store_plan pl on qt.query_text_id = pl.query_id inner join sys.query_store_runtime_stats rs on pl.plan_id = rs.plan_id ) as A where A.RN = 1"; } cmd.CommandText = querytext; // Fetch the query execution details SqlDataReader dataReader = cmd.ExecuteReader(); // Implement the recording of query execution details. while (dataReader.Read()) { string qrystring = dataReader[1].ToString(); qrystring += ";"; // Find the stmt that we recorded the details for. KeyValuePair <Guid, SQLQueryStmt> stmt = Utilities.DictWorkloadQueries.Where(x => x.Value.QueryText.Replace(System.Environment.NewLine, " ") == qrystring.Replace(System.Environment.NewLine, " ")).FirstOrDefault(); if (stmt.Value != null) { if (IsBeforeSuggestions) { stmt.Value.PhysicalReads_Before = Convert.ToInt64(dataReader[9]); stmt.Value.LogicalReads_Before = Convert.ToInt64(dataReader[7]); stmt.Value.LogicalWrites_Before = Convert.ToInt64(dataReader[11]); stmt.Value.OriginalQueryExecutionTime = Convert.ToInt64(dataReader[13]); stmt.Value.ExecutedFrom = dataReader[5].ToString(); stmt.Value.ExecutedTill = dataReader[6].ToString(); stmt.Value.TotalIOCost_Before = stmt.Value.LogicalReads_Before + stmt.Value.LogicalWrites_Before + stmt.Value.PhysicalReads_Before; } else { stmt.Value.PhysicalReads_After = Convert.ToInt64(dataReader[8]); stmt.Value.LogicalReads_After = Convert.ToInt64(dataReader[6]); stmt.Value.LogicalWrites_After = Convert.ToInt64(dataReader[10]); stmt.Value.IndexedQueryExecutionTime = Convert.ToInt64(dataReader[12]); stmt.Value.ExecutedFrom = dataReader[5].ToString(); stmt.Value.ExecutedTill = dataReader[6].ToString(); stmt.Value.TotalIOCost_After = stmt.Value.LogicalReads_After + stmt.Value.LogicalWrites_After + stmt.Value.PhysicalReads_After; } Utilities.DictWorkloadQueries[stmt.Key] = stmt.Value; } } connection.Close(); consoletable.Write(Format.MarkDown); Console.WriteLine(); } }
//public static List<NestedStmtWhereClauses> StatementWhereClauses = new List<NestedStmtWhereClauses>(); public static void ParseWorkload(String sqlFilename) { EDbVendor dbVendor = EDbVendor.dbvmssql; Console.WriteLine("\n"); Console.WriteLine("Selected SQL dialect: " + ((dbVendor.ToString() == "dbvmssql") ? "Transact SQL for Microsoft SQL Server" : "SQL for Oracle.")); Console.WriteLine("\n"); TGSqlParser sqlparser = new TGSqlParser(dbVendor); sqlparser.sqlfilename = sqlFilename; int ret = sqlparser.parse(); if (ret == 0) { Console.WriteLine("###################### Parsing Started : " + sqlFilename); // Print output Headers var consoletable = new ConsoleTable("Statement Number", "Statement Type", "Parse OK ?", "Query Text [Truncated]"); for (int i = 0; i < sqlparser.sqlstatements.size(); i++) { ESqlStatementType sqlStatementType = sqlparser.sqlstatements.get(i).sqlstatementtype; // Get the statementtype of the sql qwuery if (sqlStatementType == ESqlStatementType.sstselect || sqlStatementType == ESqlStatementType.sstupdate) // Take the statement in dictionary only if it is a DML(SELECT/UPDATE) statement { // We have taken iterate statement inside if block since parsong for "Use Database", "Go", "Set variable values", etc is not useful for our applicaiton scope. iterateStmt(sqlparser.sqlstatements.get(i)); // Create a new query object SQLQueryStmt query = new SQLQueryStmt(); // get the query text of the sql statement from the workload query.QueryText = sqlparser.sqlstatements.get(i).String; // check if this statement appeared for the first time and add it to the dictionary, else increment the count of already added statement from the dictionary. if (Utilities.DictWorkloadQueries.Where(qry => qry.Value.QueryText == query.QueryText).Any()) // If the statement is occuring again. { Guid guid = Utilities.DictWorkloadQueries.FirstOrDefault(qry => qry.Value.QueryText == query.QueryText).Key; // Get the queryId of the SQL already in the Dictionary. Utilities.DictWorkloadQueries[guid].NumOfOccurences += 1; // Increment the count of occurence. } else // If the statement occurs for the first time. { query.QueryId = Guid.NewGuid(); // New key for query Utilities.DictWorkloadQueries.Add(query.QueryId, query); // Add the query to Dictionary. Utilities.DictWorkloadQueries[query.QueryId].NumOfOccurences = 1; } consoletable.AddRow("Parsed Statement[" + i + "]", (sqlStatementType == ESqlStatementType.sstselect)?"SELECT": "OTHER", "Successful", query.QueryText.Replace(System.Environment.NewLine, " ").Substring(0, 35) + "..."); } } consoletable.Write(Format.MarkDown); Console.WriteLine(); Console.WriteLine("###################### " + sqlFilename + " Parsing Complete ####################"); } else { Console.WriteLine(sqlparser.Errormessage); } }
/// <summary> /// Checks the valididty of input parameters by connecting to the database using given credentials and also validating permissions of the user. /// </summary> /// <param name="DBFilepath">the access path of the actual Database file.</param> /// <param name="UserName">login username for the database.</param> /// <param name="Password">password for the the database login.</param> /// <param name="DBName">name of the database to be connected.</param> /// <returns>Boolean</returns> public Boolean CheckInputParameters(string ServerName, string DBFilepath, string UserName, string Password, string DBName) { bool GreenLights = false; /* * This implementation is pending * check the connection here and send true if all works fine. Check if all works fine by querying if the database exists. * check the user permission of the login to be of DBO or Sysadmin levels. * * The following code does a test run of the access to the database after the above mentioned initial checks are done. */ /* * The following code does a test run of the access to the database after the above mentioned initial checks are done. */ // AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL13.LEARNINGOWL\MSSQL\DATA\AdventureWorks2016_Data.mdf; string ConnectionString = ""; if (UserName == "" && Password == "") { //Connection string example with Windows Authentication mode or Integrated Security mode. ConnectionString = @"Data Source= " + ServerName + @"; Initial Catalog=" + DBName + @"; Asynchronous Processing=True; Integrated Security=True; Connect Timeout=30"; } else { // Connection string example with UserName and Password: ConnectionString = @"Data Source=" + ServerName + @"; Initial Catalog=" + DBName + @"; User Id= " + UserName + @" ; Password= "******"; Asynchronous Processing=True; Connect Timeout=30"; } string queryString = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = @FilterCondition"; // WHERE ColumnName > @FilterCondition // Create and open the connection in a using block. This // ensures that all resources will be closed and disposed // when the code exits. using (SqlConnection connection = new SqlConnection(ConnectionString)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); string paramValue = "BASE TABLE"; // Just an example. Could be anything. command.Parameters.AddWithValue("@FilterCondition", paramValue); // Open the connection in a try/catch block. // Create and execute the DataReader, writing the result // set to the console window. try { // Print output Headers var consoletable = new ConsoleTable("Database", "SchemaName", "TableName"); // Print declaration statement Console.WriteLine("###################### Printing schemas and tables that are accessible to provided user credential ####################"); connection.Open(); SqlDataReader OutputReader = command.ExecuteReader(); while (OutputReader.Read()) { consoletable.AddRow(OutputReader[0], OutputReader[1], OutputReader[2]); DBTable tab = new DBTable(); tab.Table_Schema_Name = OutputReader[1].ToString(); tab.name = OutputReader[2].ToString(); Utilities.DictTablesCatalog[tab.name] = tab; } OutputReader.Close(); consoletable.Write(Format.MarkDown); Utilities.ExportToPDF(consoletable, "PrintSchema"); Console.WriteLine(); GreenLights = true; } catch (Exception ex) { throw ex; } } return(GreenLights); }