XEvent tests intermittent failures

[paulmedynski]

We're seeing intermittent failures during CI test runs:

[xUnit.net 00:00:04.93]     Microsoft.Data.SqlClient.ManualTesting.Tests.XEventsTracingTest.XEventActivityIDConsistentWithTracing(query: "sp_help", commandType: StoredProcedure, xEvent: "rpc_starting") [FAIL]
  [xUnit.net 00:00:04.93]       Assert.Equal() Failure: Values differ
  [xUnit.net 00:00:04.93]       Expected: 1
  [xUnit.net 00:00:04.93]       Actual:   0
  [xUnit.net 00:00:04.93]       Stack Trace:
  [xUnit.net 00:00:04.93]         /_/src/Microsoft.Data.SqlClient/tests/ManualTests/TracingTests/XEventsTracingTest.cs(67,0): at Microsoft.Data.SqlClient.ManualTesting.Tests.XEventsTracingTest.GetCommandActivityId(String commandText, String eventName, Guid connectionId, XmlDocument xEvents)
  [xUnit.net 00:00:04.93]         /_/src/Microsoft.Data.SqlClient/tests/ManualTests/TracingTests/XEventsTracingTest.cs(51,0): at Microsoft.Data.SqlClient.ManualTesting.Tests.XEventsTracingTest.XEventActivityIDConsistentWithTracing(String query, CommandType commandType, String xEvent)
  [xUnit.net 00:00:04.93]            at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
  [xUnit.net 00:00:04.93]            at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span`1 copyOfArgs, BindingFlags invokeAttr)
    Failed Microsoft.Data.SqlClient.ManualTesting.Tests.XEventsTracingTest.XEventActivityIDConsistentWithTracing(query: "sp_help", commandType: StoredProcedure, xEvent: "rpc_starting") [1 s]

These tests run commands against SQL Server instances and capture events. I suspect some timing issues.

[edwardneal]

This is one of the tests I wrote - apologies. It looks like XEvents are written to the target asynchronously, and if the SQL Server is under load then this can preempt the next query.

I'll submit a PR tomorrow which accounts for the timing issue.

[edwardneal]

It looks like there are still intermittent failures, this time as the result of SQL Server breaking a deadlock by killing the connections executing the query we want to trace.

The XEvents session doesn't actually care whether or not the connection is killed, it still records the statement if there's an exception. That's completely logical, but it's not presently covered as a test case. I'm planning to adjust the tests slightly:

• Change the SP-based query to use the new RAII primitives. This should prevent it from depending on sp_help, which might deadlock if another job is creating a SQL object at the same time as our test.
• Add a test case to demonstrate that even SQL statements which throw an exception are traced.

I saw a few, more intermittent, deadlocks on SELECT @@VERSION, but don't fully understand why. I'll change this to SELECT 1 in case @@VERSION is an alias for a server property which does more work than I expect, but I'd appreciate it if CI could be run a few times to verify whether or not this has an impact on a more contested instance. If it still doesn't work, I'll need to start looking into deadlock reports.