private static QueryStats ExecuteTestQuery(string test, string connectionString, string query, int maxdop)
{
QueryStats result = new QueryStats();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SET STATISTICS IO ON; SET STATISTICS TIME ON;";
cmd.ExecuteNonQuery();
if (test == "bcr")
{
cmd.CommandText = "DBCC DROPCLEANBUFFERS;";
cmd.ExecuteNonQuery();
}
messages.Clear();
conn.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);
cmd.CommandText = query + string.Format(" OPTION (RECOMPILE, MAXDOP {0})", maxdop);
cmd.CommandTimeout = 60 * 60;
cmd.ExecuteNonQuery();
conn.Close();
Regex reIO = new Regex(@"(?:Table )'(?<TABLE>.*)'. (?:Scan count )(?<SCAN>\d*), (?:logical reads )(?<LOGICAL>\d*), (?:physical reads )(?<PHYSICAL>\d*), (?:read-ahead reads )(?<AHEAD>\d*)");
Regex reTIME = new Regex(@"(?:CPU time = )(?<CPU>\d*).*(?:elapsed time = )(?<ELAPSED>\d*)");
bool ioStatFound = false;
foreach (string message in messages)
{
MatchCollection mcIO = reIO.Matches(message);
foreach (Match mIO in mcIO)
{
if (mIO.Success)
{
//foreach (string groupName in reIO.GetGroupNames())
//{
// if (groupName != "0") Console.WriteLine("{0}: {1}", groupName, mIO.Groups[groupName]);
//}
result.LogicalReads += Convert.ToInt32(mIO.Groups["LOGICAL"].Value);
result.PhysicalReads += Convert.ToInt32(mIO.Groups["PHYSICAL"].Value);
result.ReadAheadReads += Convert.ToInt32(mIO.Groups["AHEAD"].Value);
ioStatFound = true;
}
}
if (ioStatFound == true)
{
MatchCollection mcTIME = reTIME.Matches(message);
foreach (Match mTIME in mcTIME)
{
if (mTIME.Success)
{
//foreach (string groupName in reTIME.GetGroupNames())
//{
// if (groupName != "0") Console.WriteLine("{0}: {1}", groupName, mTIME.Groups[groupName]);
//}
result.ElapsedMs += Convert.ToInt32(mTIME.Groups["ELAPSED"].Value);
}
}
}
}
return result;
}
public static void Main(string[] args)
{
string connectionString = string.Empty;
string commandFile = "CommandFile.txt";
Console.WriteLine("T-SQL Query Benchmark");
Console.WriteLine("(c) Davide Mauri 2019");
Console.WriteLine("Beta Version, Use at your own risk!");
Console.WriteLine("Version: " + Assembly.GetExecutingAssembly().GetName().Version.ToString());
switch (args.Length)
{
case 0: break;
case 1: commandFile = args[0]; break;
default:
Console.WriteLine("Unknown command line arguments. Use:");
Console.WriteLine("dotnet run -- [command file]");
return;
}
List <string> commandLines = new List <string>();
try
{
Console.Write($"Loading command file '{commandFile}'...");
commandLines = LoadCommandFile(commandFile);
}
catch (Exception e)
{
Console.WriteLine("Failed.");
Console.WriteLine("!!! Unable to parse command line.");
Console.WriteLine("!!! " + e.Message);
return;
}
Console.WriteLine("Done.");
StreamWriter sw = null;
string dbEdition = string.Empty;
string slo = string.Empty;
string dbVersion = string.Empty;
foreach (string commandLine in commandLines)
{
Dictionary <string, string> command = DecodeCommandLine(commandLine);
if (command["command"].ToLower() == "connectionstring")
{
foreach (KeyValuePair <string, string> kvp in command)
{
if (kvp.Key != "command")
{
connectionString += kvp.Key + "=" + kvp.Value + ";";
}
}
Console.Write("Testing connection string...");
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
SqlConnection connTest = new SqlConnection(builder.ConnectionString);
try
{
connTest.Open();
}
catch (Exception e)
{
Console.WriteLine("Failed.");
Console.WriteLine("!!! Connection to DB cannot be opened.");
Console.WriteLine("!!! " + e.Message);
return;
}
finally
{
connTest.Close();
}
Console.WriteLine("Done.");
Console.WriteLine("Getting database info...");
var cmd = connTest.CreateCommand();
cmd.CommandText = "SELECT ISNULL(DATABASEPROPERTYEX(DB_NAME(DB_ID()), 'Edition'),'') AS [Database Edition], ISNULL(DATABASEPROPERTYEX(DB_NAME(DB_ID()), 'ServiceObjective'), '') AS [Service Objective], DATABASEPROPERTYEX(DB_NAME(DB_ID()), 'Version') AS [Version]";
connTest.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
dbEdition = reader.GetString(0);
slo = reader.GetString(1);
dbVersion = (reader.GetInt32(2)).ToString();
}
connTest.Close();
Console.WriteLine("Initializing output file...");
sw = File.CreateText(string.Format("TQB-TestResult-{0}-{1:yyyyMMddHHmm}.txt", builder.InitialCatalog, DateTime.Now));
sw.WriteLine("Type, Test, Maxdop, TestNum, TestMaxNum, LogicalReads, Physical, ReadAhead, CpuMs, ElapsedMs, DBEdition, DBVersion, SLO");
}
if (command["command"].ToLower() == "query")
{
string test = command["test"].ToLower();
string type = command["query"].ToLower();
string query = LoadQuery(type);
int cycle = Convert.ToInt32(command["cycle"].ToLower());
int maxdop = Convert.ToInt32(command["maxdop"].ToLower());
if (test == "mcr")
{
Console.WriteLine("Warming up...");
ExecuteWarmUpQuery(connectionString, query);
}
for (int c = 1; c <= cycle; c++)
{
//Console.Write("{0}, {1}, {2}, {3}, {4}, ", type, test, maxdop, c, cycle);
Console.Write("Executing \"{0}\" test, {1} out of {2}, with \"{3}\" query and {4} maxdop...", test, c, cycle, type, maxdop);
QueryStats result = ExecuteTestQuery(test, connectionString, query, maxdop);
Console.WriteLine("Done. [{0} Logical I/O, {1}/{2} ms]", result.LogicalReads, result.CpuMs, result.ElapsedMs);
sw.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}", type, test, maxdop, c, cycle, result.LogicalReads, result.PhysicalReads, result.ReadAheadReads, result.CpuMs, result.ElapsedMs, dbEdition, dbVersion, slo);
sw.Flush();
}
}
}
sw.Close();
Console.WriteLine("Finished. Press any key to exit.");
Console.ReadLine();
}
public static void Main(string[] args)
{
string connectionString = string.Empty;
string commandFile = "CommandFile.txt";
Console.WriteLine("T-SQL Query Benchmark");
Console.WriteLine("(c) Davide Mauri 2013");
Console.WriteLine("Beta Version, Use at your own risk!");
Console.WriteLine("Version: " + Assembly.GetExecutingAssembly().GetName().Version.ToString());
switch (args.Length)
{
case 0: break;
case 1: commandFile = args[1]; break;
default:
Console.WriteLine("Unknown command line arguments. Use:");
Console.WriteLine("TQB [command file]");
return;
}
List <string> commandLines = new List <string>();
try
{
Console.Write("Loading command line...");
commandLines = LoadCommandFile(commandFile);
}
catch (Exception e)
{
Console.WriteLine("Failed.");
Console.WriteLine("!!! Unable to parse command line.");
Console.WriteLine("!!! " + e.Message);
return;
}
Console.WriteLine("Done.");
StreamWriter sw = File.CreateText(string.Format("TQB-TestResult-{0:yyyyMMddHHmm}.txt", DateTime.Now));
sw.WriteLine("Type, Test, Maxdop, TestNum, TestMaxNum, LogicalReads, Physical, ReadAhead, ElapsedMs");
foreach (string commandLine in commandLines)
{
Dictionary <string, string> command = DecodeCommandLine(commandLine);
if (command["command"] == "connectionstring")
{
foreach (KeyValuePair <string, string> kvp in command)
{
if (kvp.Key != "command")
{
connectionString += kvp.Key + "=" + kvp.Value + ";";
}
}
Console.Write("Testing connection string...");
SqlConnection connTest = new SqlConnection(connectionString);
try
{
connTest.Open();
}
catch (Exception e)
{
Console.WriteLine("Failed.");
Console.WriteLine("!!! Connection to DB cannot be opened.");
Console.WriteLine("!!! " + e.Message);
return;
}
finally
{
connTest.Close();
}
Console.WriteLine("Done.");
}
if (command["command"] == "query")
{
string test = command["test"];
string type = command["query"];
string query = LoadQuery(type);
int cycle = Convert.ToInt32(command["cycle"]);
int maxdop = Convert.ToInt32(command["maxdop"]);
for (int c = 1; c <= cycle; c++)
{
//Console.Write("{0}, {1}, {2}, {3}, {4}, ", type, test, maxdop, c, cycle);
Console.Write("Executing \"{0}\" test, {1} out of {2}, with \"{3}\" query and {4} maxdop...", test, c, cycle, type, maxdop);
QueryStats result = ExecuteTestQuery(test, connectionString, query, maxdop);
Console.WriteLine("Done. [{0} Logical I/O, {1} ms]", result.LogicalReads, result.ElapsedMs);
sw.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}", type, test, maxdop, c, cycle, result.LogicalReads, result.PhysicalReads, result.ReadAheadReads, result.ElapsedMs);
sw.Flush();
}
}
}
sw.Close();
Console.WriteLine("Finished. Press any key to exit.");
Console.ReadLine();
}
private static QueryStats ExecuteTestQuery(string test, string connectionString, string query, int maxdop)
{
QueryStats result = new QueryStats();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SET STATISTICS IO ON; SET STATISTICS TIME ON;";
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT SERVERPROPERTY('Edition') AS Edition";
var edition = (string)(cmd.ExecuteScalar());
if (test == "bcr" && edition != "SQL Azure")
{
cmd.CommandText = "DBCC DROPCLEANBUFFERS;";
cmd.ExecuteNonQuery();
}
messages.Clear();
conn.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);
cmd.CommandText = query + string.Format(" OPTION (RECOMPILE, MAXDOP {0})", maxdop);
cmd.CommandTimeout = 60 * 60;
cmd.ExecuteNonQuery();
conn.Close();
Regex reIO = null;
Regex reTIME = new Regex(@"(?:CPU time = )(?<CPU>\d*).*(?:elapsed time = )(?<ELAPSED>\d*)");
if (edition != "SQL Azure")
{
reIO = new Regex(@"(?:Table )'(?<TABLE>.*)'. (?:Scan count )(?<SCAN>\d*), (?:logical reads )(?<LOGICAL>\d*), (?:physical reads )(?<PHYSICAL>\d*), (?:read-ahead reads )(?<AHEAD>\d*)");
}
else
{
reIO = new Regex(@"(?:Table )'(?<TABLE>.*)'. (?:Scan count )(?<SCAN>\d*), (?:logical reads )(?<LOGICAL>\d*), (?:physical reads )(?<PHYSICAL>\d*), (?:page server reads )(?<PAGESERVER>\d*), (?:read-ahead reads )(?<AHEAD>\d*)");
}
bool ioStatFound = false;
foreach (string message in messages)
{
MatchCollection mcIO = reIO.Matches(message);
foreach (Match mIO in mcIO)
{
if (mIO.Success)
{
result.LogicalReads += Convert.ToInt32(mIO.Groups["LOGICAL"].Value);
result.PhysicalReads += Convert.ToInt32(mIO.Groups["PHYSICAL"].Value);
result.ReadAheadReads += Convert.ToInt32(mIO.Groups["AHEAD"].Value);
ioStatFound = true;
}
}
if (ioStatFound == true)
{
MatchCollection mcTIME = reTIME.Matches(message);
foreach (Match mTIME in mcTIME)
{
if (mTIME.Success)
{
result.CpuMs += Convert.ToInt32(mTIME.Groups["CPU"].Value);
result.ElapsedMs += Convert.ToInt32(mTIME.Groups["ELAPSED"].Value);
}
}
}
}
return(result);
}
private static QueryStats ExecuteTestQuery(string test, string connectionString, string query, int maxdop)
{
QueryStats result = new QueryStats();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SET STATISTICS IO ON; SET STATISTICS TIME ON;";
cmd.ExecuteNonQuery();
if (test == "bcr")
{
cmd.CommandText = "DBCC DROPCLEANBUFFERS;";
cmd.ExecuteNonQuery();
}
messages.Clear();
conn.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);
cmd.CommandText = query + string.Format(" OPTION (RECOMPILE, MAXDOP {0})", maxdop);
cmd.CommandTimeout = 60 * 60;
cmd.ExecuteNonQuery();
conn.Close();
Regex reIO = new Regex(@"(?:Table )'(?<TABLE>.*)'. (?:Scan count )(?<SCAN>\d*), (?:logical reads )(?<LOGICAL>\d*), (?:physical reads )(?<PHYSICAL>\d*), (?:read-ahead reads )(?<AHEAD>\d*)");
Regex reTIME = new Regex(@"(?:CPU time = )(?<CPU>\d*).*(?:elapsed time = )(?<ELAPSED>\d*)");
bool ioStatFound = false;
foreach (string message in messages)
{
MatchCollection mcIO = reIO.Matches(message);
foreach (Match mIO in mcIO)
{
if (mIO.Success)
{
//foreach (string groupName in reIO.GetGroupNames())
//{
// if (groupName != "0") Console.WriteLine("{0}: {1}", groupName, mIO.Groups[groupName]);
//}
result.LogicalReads += Convert.ToInt32(mIO.Groups["LOGICAL"].Value);
result.PhysicalReads += Convert.ToInt32(mIO.Groups["PHYSICAL"].Value);
result.ReadAheadReads += Convert.ToInt32(mIO.Groups["AHEAD"].Value);
ioStatFound = true;
}
}
if (ioStatFound == true)
{
MatchCollection mcTIME = reTIME.Matches(message);
foreach (Match mTIME in mcTIME)
{
if (mTIME.Success)
{
//foreach (string groupName in reTIME.GetGroupNames())
//{
// if (groupName != "0") Console.WriteLine("{0}: {1}", groupName, mTIME.Groups[groupName]);
//}
result.ElapsedMs += Convert.ToInt32(mTIME.Groups["ELAPSED"].Value);
}
}
}
}
return(result);
}