Monday, December 24, 2012

C# : Simulate concurrent calls to SQL Server

I happened to need to write a small C# routine that simulate a lot of concurrent calls to a stored procedure in SQL Server. The purpose of this application was to simulate deadlock scenario that occurred in production server. A lot of deadlock occurred at highly concurrent situation only, so simulating the condition in test environment is very helpful to figure out deadlock root cause and also to validate deadlock fix.
I simplified the case and write a sample code that pretty much done the same thing.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace NS
{
    class Program
    {
        static void Main(string[] args)
        {
            Parallel.For(0, 100, (i) => Run(i));
        }

        static void Run(int i)
        {
            string strConn = "Data Source=(local);Initial Catalog=MyDB;Integrated Security=true";

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("sp_GetNext", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // Input param
                SqlParameter pInput = new SqlParameter("@in", SqlDbType.Int);
                pInput.Direction = ParameterDirection.Input;
                pInput.Value = 1;
                cmd.Parameters.Add(pInput);
                
                // Output param
                SqlParameter pOutput = new SqlParameter("@out", SqlDbType.Int);
                pOutput.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(pOutput);

                cmd.ExecuteNonQuery();

                Console.WriteLine(pOutput.Value);
            }
        }
    }
}

The calling stored procedure is nothing special, it is ordinary ADO.NET routine to make a SP call. To simulate concurrent calls, I called Run() method by using Parallel.For(). This Parallel.For() method runs Run() method 100 times, almost concurrently. Depending on CPU cores, the actual number of worker threads can vary but it will simulate as many concurrent calls as possible from one machine. If test machine has single CPU or is a slow machine, probably the simulation might fail. But it gaves me decent level of simulation with my eight core machine.