public void Test_Sp_Reset_Connection()
{
string app = "Test " + Guid.NewGuid();
SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(TestEnvironment.DbConnectionString);
b.ApplicationName = app;
// important
b.Pooling = true;
using (SqlTraceReader reader = new SqlTraceReader())
{
reader.Start(TestEnvironment.MasterConnectionString, TestEnvironment.TracePath, TraceColumns.All, TraceRowFilter.CreateByApplication(app));
int nQueries = 42;
for (int i = 0; i < nQueries; i++)
{
using (SqlConnection con = new SqlConnection(b.ConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("Select @@version", con))
{
cmd.ExecuteNonQuery();
}
}
}
// Details
var details = reader.ReadDetailsReport();
Console.WriteLine("Trace summary is " + details.Summary);
Assert.AreEqual(details.Count, nQueries);
}
}
public void Test_Sandbox()
{
using (SqlTraceReader reader = new SqlTraceReader())
{
var filterByProcess = TraceRowFilter.CreateByClientProcess(Process.GetCurrentProcess().Id);
var filterLikeSqlTrace = TraceRowFilter.CreateLikeApplication("SqlTrace");
reader.Start(TestEnvironment.MasterConnectionString, TestEnvironment.TracePath, TraceColumns.All, filterByProcess, filterLikeSqlTrace);
using (SqlConnection con = new SqlConnection(TestEnvironment.DbConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(SqlBatch, con))
{
cmd.ExecuteNonQuery();
}
for (int i = 1; i < 10; i++)
{
using (SqlCommand cmd = new SqlCommand($"Insert {Table1Name}(id) Values(@i)", con))
{
cmd.Parameters.Add("@i", i);
cmd.ExecuteNonQuery();
}
}
using (SqlCommand cmd = new SqlCommand($"Select * From {Table1Name}", con))
{
cmd.ExecuteNonQuery();
}
using (SqlCommand cmd = new SqlCommand("sp_server_info", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
reader.Stop();
var rptGroups = reader.ReadGroupsReport <string>(TraceColumns.ClientHost);
var bySql = reader.ReadGroupsReport <string>(TraceColumns.Sql);
var rptSummary = reader.ReadSummaryReport();
var rpt = reader.ReadDetailsReport();
Console.WriteLine("Statements: " + rpt.Count);
DumpCounters(rpt);
Console.WriteLine("");
Console.WriteLine("My Process: " + Process.GetCurrentProcess().Id);
Console.WriteLine("Summary: " + rptSummary);
Console.WriteLine("Details Summary " + rpt.Summary);
}
}
public void Test_Empty_Session()
{
using (SqlTraceReader reader = new SqlTraceReader())
{
reader.Start(TestEnvironment.MasterConnectionString, TestEnvironment.TracePath, TraceColumns.All);
// summary
SqlCounters summary = reader.ReadSummaryReport();
Assert.Zero(summary.Requests);
Console.WriteLine("Summary of empty session is " + summary);
// details. Summary query above should not be present in details report below.
var details = reader.ReadDetailsReport();
CollectionAssert.IsEmpty(details, "Details Collection");
Assert.Zero(details.Summary.Requests);
CollectionAssert.IsEmpty(details.GroupByApplication(), "Groups by application");
reader.Stop();
reader.Dispose();
}
}
public void Exec1(StressCounters counters)
{
SqlTraceReader rdr = new SqlTraceReader();
try
{
var connection = ConnectionFactory();
connection.Open();
if (UseTrace)
{
// int? spid = SqlServerUtils.GetCurrentSpid(connection);
int?spid = connection.Manage().CurrentSPID;
rdr.Start(
TestEnvironment.MasterConnectionString,
TestEnvironment.TracePath,
TraceColumns.None,
TraceRowFilter.CreateByServerProcess(spid.Value)
);
}
Work(connection);
connection.Close();
if (UseTrace)
{
rdr.Stop();
var summary = rdr.ReadSummaryReport();
counters.Server_CPU += summary.CPU;
counters.Server_Duration += summary.Duration;
counters.Server_Reads += summary.Reads;
counters.Server_Writes += summary.Writes;
}
}
finally
{
rdr.Dispose();
}
}
public void Single_StoredProcedure_Is_Captured()
{
string sql = @"SELECT @@version, @parameter;";
using (SqlTraceReader reader = new SqlTraceReader())
{
reader.Start(TestEnvironment.MasterConnectionString, TestEnvironment.TracePath, TraceColumns.All);
using (SqlConnection con = new SqlConnection(TestEnvironment.DbConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.Add("@parameter", SqlDbType.Int).Value = 0;
cmd.ExecuteNonQuery();
}
}
var summary = reader.ReadSummaryReport();
var groups = reader.ReadGroupsReport <int>(TraceColumns.ClientProcess);
var details = reader.ReadDetailsReport();
DumpCounters(details);
int idProcess = Process.GetCurrentProcess().Id;
Console.WriteLine("Trace summary is " + details.Summary);
bool isCaught = details.Any(x =>
x.SpName == "sp_executesql" &&
x.Sql.IndexOf(sql) >= 0 &&
x.ClientProcess == idProcess);
if (!isCaught)
{
Assert.Fail("Expected sql proc '{0}' call by process {1}", "sp_executesql", idProcess);
}
}
}
public void RowCounts_Of_Insert(string masterConnectionString)
{
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
if (con.Manage().IsAzure)
{
Console.WriteLine("Tracing for Azure is not yet implemented");
return;
}
}
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
Console.WriteLine($"Version of [{masterConnectionString}]: {con.Manage().ShortServerVersion}");
}
var table = $"#T_{Guid.NewGuid().ToString("N")}";
string[] sqlCommands = new[]
{
$"Create Table {table}(id int);",
$"Insert {table} Values(42); Insert {table} Values(43); Insert {table} Values(44); Insert {table} Values(45);",
};
TraceTetsEnv env = new TraceTetsEnv(masterConnectionString);
using (env)
{
using (SqlTraceReader reader = new SqlTraceReader())
{
Console.WriteLine($@"
Master Connection: {env.MasterConnectionString}
TraceDir: {env.TraceDirectory}
TableName: {env.TableName}");
reader.Start(env.MasterConnectionString, env.TraceDirectory,
TraceColumns.Sql | TraceColumns.ClientProcess,
TraceRowFilter.CreateByClientProcess(Process.GetCurrentProcess().Id));
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
con.Open();
foreach (var sql in sqlCommands)
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.ExecuteNonQuery();
}
}
}
Console.WriteLine($"Trace File: {reader.TraceFile}");
reader.Stop();
var detailsReport = reader.ReadDetailsReport();
DumpCounters(detailsReport);
Assert.Greater(detailsReport.Count, 0, "At least one sql command should be caught");
var rowCountsOfLastStatements = detailsReport.Last().Counters.RowCounts;
Assert.AreEqual(4, rowCountsOfLastStatements, "Insert 4x should result RowCounts==4");
}
}
}
public void Error_Is_Captured(string masterConnectionString)
{
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
if (con.Manage().IsAzure)
{
Console.WriteLine("Tracing for Azure is not yet implemented");
return;
}
}
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
Console.WriteLine($"Version of [{masterConnectionString}]: {con.Manage().ShortServerVersion}");
}
TraceTetsEnv env = new TraceTetsEnv(masterConnectionString);
using (env)
{
string sql = "Select 42 / 0;";
sql = sql + sql + sql;
using (SqlTraceReader reader = new SqlTraceReader())
{
Console.WriteLine($@"
Master Connection: {env.MasterConnectionString}
TraceDir: {env.TraceDirectory}
TableName: {env.TableName}");
reader.Start(env.MasterConnectionString, env.TraceDirectory,
TraceColumns.Sql | TraceColumns.ClientProcess, TraceRowFilter.CreateByClientProcess(Process.GetCurrentProcess().Id));
Exception caught = null;
try
{
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
// Console.WriteLine("Divide by zero IS REQUIRED: " + ex);
caught = ex;
}
reader.Stop();
var detailsReport = reader.ReadDetailsReport();
DumpCounters(detailsReport);
Assert.AreEqual(1, detailsReport.Count, "Exactly one statement is expected");
Assert.Greater(detailsReport.Count, 0, "At least one sql command should be caught");
foreach (SqlStatementCounters report in detailsReport)
{
if (report.SqlErrorCode != 8134)
{
Assert.Fail("SQL ERROR 8134 expected. Caught Exception is " + caught);
}
}
}
}
}
public void Single_SqlBatch_Is_Captured(string masterConnectionString)
{
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
if (con.Manage().IsAzure)
{
Console.WriteLine("Tracing for Azure is not yet implemented");
return;
}
}
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
Console.WriteLine($"Version of [{masterConnectionString}]: {con.Manage().ShortServerVersion}");
}
TraceTetsEnv env = new TraceTetsEnv(masterConnectionString);
using (env)
{
string sql = "Set NOCOUNT ON; SELECT @@version, 'Hello, World!'; Exec sp_server_info;";
sql = sql + sql + sql;
using (SqlTraceReader reader = new SqlTraceReader())
{
Console.WriteLine($@"
Master Connection: {env.MasterConnectionString}
TraceDir: {env.TraceDirectory}
TableName: {env.TableName}");
reader.Start(env.MasterConnectionString, env.TraceDirectory,
TraceColumns.Sql | TraceColumns.ClientProcess);
using (SqlConnection con = new SqlConnection(masterConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.ExecuteNonQuery();
}
}
reader.Stop();
var detailsReport = reader.ReadDetailsReport();
DumpCounters(detailsReport);
Assert.Greater(detailsReport.Count, 0, "At least one sql command should be caught");
int idProcess = Process.GetCurrentProcess().Id;
foreach (SqlStatementCounters report in detailsReport)
{
if (report.SqlErrorCode.HasValue)
{
Assert.Fail("All the statement are successful, but '{0}' produces error {1}", report.Sql,
report.SqlErrorCode);
}
if (report.Sql == sql && report.ClientProcess == idProcess)
{
return;
}
}
Assert.Fail("Expected sql statement {0} by process {1}", sql, idProcess);
}
}
}
public static void RunStress()
{
// TestEnvironment.TracePath = "R:\\Temp\\";
Func <SqlConnection> connectionFactory =
delegate
{
SqlConnection connection = new SqlConnection(TestEnvironment.DbConnectionString);
return(connection);
};
using (SqlConnection con = new SqlConnection(TestEnvironment.DbConnectionString))
using (SqlTraceReader rdr = new SqlTraceReader())
{
rdr.Start(TestEnvironment.DbConnectionString, TestEnvironment.TracePath, TraceColumns.All);
SqlUnitOfWork.UnitOfWork(con, 1);
}
long clientDuration0;
long clientCpu0;
StressController.Run(1, delegate { }, out clientDuration0, out clientCpu0);
foreach (int threads in StressController.ThreadsCount)
{
var resultFormat = "{0,-4} {1,-41} Commands {4,6} CPU {2,5} Duration {3,5}";
Stopwatch watch = new Stopwatch();
// Сценарий 1. Одиночный запрос без трассировки
{
long clientDuration;
long clientCpu;
long count = 0;
StressController.Run(
threads,
delegate
{
lock (watch)
if (!watch.IsRunning)
{
watch.Start();
}
while (watch.ElapsedMilliseconds < ScenarioDuration)
{
using (var connection = connectionFactory())
{
connection.Open();
int?spid = connection.Manage().CurrentSPID;
const string sql = "Select NULL";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
object scalar = cmd.ExecuteScalar();
}
}
Interlocked.Increment(ref count);
}
},
out clientDuration,
out clientCpu);
Console.WriteLine(resultFormat, threads, "No Trace:", clientCpu, clientDuration, count);
}
// Сценарий 2. Сеанс трассировки на каждый запрос
{
var systemDrive = Environment.SystemDirectory.Substring(0, 3);
var scenarios =
new[]
{
new { TraceDrive = systemDrive, Title = "Trace Session Per Request via Sys Drive" },
new { TraceDrive = RamDriveInfo.RamDrivePath, Title = "Trace Session Per Request via Ram Drive" },
};
foreach (var scenario in scenarios)
{
if (scenario.TraceDrive != null)
{
watch = new Stopwatch();
long clientDuration;
long clientCpu;
long count = 0;
StressController.Run(
threads,
delegate
{
lock (watch)
if (!watch.IsRunning)
{
watch.Start();
}
var traceConnection =
new SqlConnectionStringBuilder(TestEnvironment.MasterConnectionString);
traceConnection.Pooling = true;
while (watch.ElapsedMilliseconds < ScenarioDuration)
{
using (var connection = connectionFactory())
{
connection.Open();
int?spid = connection.Manage().CurrentSPID;
SqlTraceReader rdr = new SqlTraceReader();
rdr.Start(
traceConnection.ConnectionString,
Path.Combine(scenario.TraceDrive, "temp\\traces"),
TraceColumns.None,
TraceRowFilter.CreateByServerProcess(spid.Value)
);
using (rdr)
{
const string sql = "Select NULL";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
object scalar = cmd.ExecuteScalar();
}
var sqlSummary = rdr.ReadSummaryReport();
}
rdr.Stop();
}
Interlocked.Increment(ref count);
}
},
out clientDuration,
out clientCpu
);
Console.WriteLine(
resultFormat,
threads,
scenario.Title + ":",
clientCpu,
clientDuration,
count);
}
}
}
// Сценарий 3. Сеанс трассировки на весь Unit of work
{
watch = new Stopwatch();
long clientDuration;
long clientCpu;
long count = 0;
StressController.Run(
threads,
delegate
{
lock (watch)
if (!watch.IsRunning)
{
watch.Start();
}
using (SqlTraceReader rdr = new SqlTraceReader())
{
const string name = "Trace Session per Scenario";
var v1 = new SqlConnectionStringBuilder(TestEnvironment.MasterConnectionString);
v1.Pooling = true;
v1.ApplicationName = name;
rdr.Start(
v1.ConnectionString,
TestEnvironment.TracePath,
TraceColumns.None,
TraceRowFilter.CreateByApplication(name)
);
while (watch.ElapsedMilliseconds < ScenarioDuration)
{
using (var connection = connectionFactory())
{
connection.Open();
int?spid = connection.Manage().CurrentSPID;
const string sql = "Select NULL";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
object scalar = cmd.ExecuteScalar();
}
}
Interlocked.Increment(ref count);
}
var sqlSummary = rdr.ReadSummaryReport();
rdr.Stop();
}
},
out clientDuration,
out clientCpu
);
Console.WriteLine(resultFormat, threads, "Trace Per Session:", clientCpu, clientDuration, count);
}
}
}