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