SYSK 263: .NET Stored Procs Do Not Execute in Parallel using MARS
The http://msdn2.microsoft.com/en-us/library/ms345109.aspx article has the following note in the Interleaved Execution section:
“Stored Procedures written in any of the .Net languages will not interleave while managed code is executing. If the inproc data provider is used, then the executed batch is subject to normal rules for interleaving and atomic execution of statements.”
So, to eliminate any doubt, I created the following test (code below):
- Create three CLR stored procedures – one that takes about 5 seconds, another that takes 2 seconds, and the third returns almost instantaneously.
- Create an MTAThread project that:
- Creates a connection with MultipleActiveResultSets=True and Asynchronous Processing=true
- Create three commands using that connection (one for each stored procedure)
- Asynchronously invoke all three commands
The result is that all three commands are executed on the same SPID, and thus, are executed sequentially!
BEGIN Command1
BEGIN Command2
BEGIN Command3
END StoredProcedure1. Entered at 07:42:56.892. SPID = 55
END StoredProcedure2. Entered at 07:43:02.446. SPID = 55
END StoredProcedure3. Entered at 07:43:04.458. SPID = 55
To make the stored procs execute in parallel, I had to create three connections – one per command (i.e. not using MARS):
BEGIN Command1
BEGIN Command2
BEGIN Command3
END StoredProcedure2. Entered at 07:55:49.556. SPID = 56
END StoredProcedure3. Entered at 07:55:53.191. SPID = 57
END StoredProcedure1. Entered at 07:55:49.556. SPID = 55
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1(out long enteredAt, out int spid)
{
enteredAt = DateTime.Now.Ticks;
// NOTE: I'm using this very ugly tight loop to get around
// HostProtectionException exception thrown on System.Threading.Thread.Join statement:
// System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
// The demanded resources were: Synchronization, ExternalThreading
DateTime targetTime = DateTime.Now.AddSeconds(5);
while (DateTime.Now < targetTime)
{
int x = 1;
}
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
SqlCommand cmd = new SqlCommand("SELECT @@SPID", cn);
spid = System.Convert.ToInt32(cmd.ExecuteScalar());
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure2(out long enteredAt, out int spid)
{
enteredAt = DateTime.Now.Ticks;
DateTime targetTime = DateTime.Now.AddSeconds(2);
while (DateTime.Now < targetTime)
{
int x = 1;
}
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
SqlCommand cmd = new SqlCommand("SELECT @@SPID", cn);
spid = System.Convert.ToInt32(cmd.ExecuteScalar());
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure3(out long enteredAt, out int spid)
{
enteredAt = DateTime.Now.Ticks;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
SqlCommand cmd = new SqlCommand("SELECT @@SPID", cn);
spid = System.Convert.ToInt32(cmd.ExecuteScalar());
}
}
};
***********************************************
public void Execute()
{
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection("MultipleActiveResultSets=True;Asynchronous Processing=true;Persist Security Info=False;Integrated Security=SSPI;database=Test;server=(local);"))
{
System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand("StoredProcedure1", cn);
cmd1.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand("StoredProcedure2", cn);
cmd2.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlCommand cmd3 = new System.Data.SqlClient.SqlCommand("StoredProcedure3", cn);
cmd3.CommandType = CommandType.StoredProcedure;
cn.Open();
System.Diagnostics.Debug.WriteLine("BEGIN Command1");
IAsyncResult ar1 = new System.Threading.WaitCallback(ExecScalarAsync).BeginInvoke(cmd1, null, this);
System.Diagnostics.Debug.WriteLine("BEGIN Command2");
IAsyncResult ar2 = new System.Threading.WaitCallback(ExecScalarAsync).BeginInvoke(cmd2, null, this);
System.Diagnostics.Debug.WriteLine("BEGIN Command3");
IAsyncResult ar3 = new System.Threading.WaitCallback(ExecScalarAsync).BeginInvoke(cmd3, null, this);
System.Threading.WaitHandle.WaitAll(new System.Threading.WaitHandle[] { ar1.AsyncWaitHandle, ar2.AsyncWaitHandle, ar3.AsyncWaitHandle });
}
}
private void ExecScalarAsync(object param)
{
System.Data.SqlClient.SqlCommand cmd = param as System.Data.SqlClient.SqlCommand;
System.Data.SqlClient.SqlParameter p1 = new System.Data.SqlClient.SqlParameter("enteredAt", System.Data.SqlDbType.BigInt, 8);
p1.Direction = ParameterDirection.Output;
System.Data.SqlClient.SqlParameter p2 = new System.Data.SqlClient.SqlParameter("spid", System.Data.SqlDbType.Int, 4);
p2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();
DateTime t = new DateTime((long)p1.Value);
System.Diagnostics.Debug.WriteLine(string.Format("END {0}. Entered at {1}. SPID = {2}", cmd.CommandText, t.ToString("hh:mm:ss.fff"), p2.Value));
}