none
c# Thread Contention with SqlDataReaders and SqlDataAdapters RRS feed

  • Question

  • We notice that inside of our .Net application we have contention when it comes to using SqlDataReader. While we understand that SqlDataReader is not ThreadSafe, it should scale. The following code is a simple example to show that we cannot scale our application because there is contention on the SqlDataReader GetValue method. We are not bound by CPU, Disk, or Network; Just the internal contention on the SqlDataReader. We can run the application 10 times with 1 thread and it scales linearly, but 10 threads in 1 app does not scale. Any thoughts on how to scale reading from SQL Server in a single c# application?

        using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Text;
        using System.Threading;
        using System.Diagnostics;
        using System.Globalization;
        
        namespace ThreadAndSQLTester
        {
            class Host
            {
                /// <summary>
                /// Gets or sets the receive workers.
                /// </summary>
                /// <value>The receive workers.</value>
                internal List<Worker> Workers { get; set; }
                /// <summary>
                /// Gets or sets the receive threads.
                /// </summary>
                /// <value>The receive threads.</value>
                internal List<Thread> Threads { get; set; }
        
                public int NumberOfThreads { get; set; }
                public int Sleep { get; set; }
                public int MinutesToRun { get; set; }
                public bool IsRunning { get; set; }
                private System.Timers.Timer runTime;
        
                private object lockVar = new object();
        
                public Host()
                {
                    Init(1, 0, 0);
                }
        
                public Host(int numberOfThreads, int sleep, int minutesToRun)
                {
                    Init(numberOfThreads, sleep, minutesToRun);
                }
        
                private void Init(int numberOfThreads, int sleep, int minutesToRun)
                {
                    this.Workers = new List<Worker>();
                    this.Threads = new List<Thread>();
        
                    this.NumberOfThreads = numberOfThreads;
                    this.Sleep = sleep;
                    this.MinutesToRun = minutesToRun;
        
                    SetUpTimer();
                }
        
                private void SetUpTimer()
                {
                    if (this.MinutesToRun > 0)
                    {
                        this.runTime = new System.Timers.Timer();
                        this.runTime.Interval = TimeSpan.FromMinutes(this.MinutesToRun).TotalMilliseconds;
                        this.runTime.Elapsed += new System.Timers.ElapsedEventHandler(runTime_Elapsed);
                        this.runTime.Start();
                    }
                }
        
                void runTime_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
                {
                    this.runTime.Stop();
                    this.Stop();
                    this.IsRunning = false;
                }
        
                public void Start()
                {
                    this.IsRunning = true;
        
                    Random r = new Random(DateTime.Now.Millisecond);
        
                    for (int i = 0; i < this.NumberOfThreads; i++)
                    {
                        string threadPoolId = Math.Ceiling(r.NextDouble() * 10).ToString();
        
                        Worker worker = new Worker("-" + threadPoolId); //i.ToString());
                        worker.Sleep = this.Sleep;
        
                        this.Workers.Add(worker);
        
                        Thread thread = new Thread(worker.Work);
                        worker.Name = string.Format("WorkerThread-{0}", i);
        
                        thread.Name = worker.Name;
        
                        this.Threads.Add(thread);
                        thread.Start();
        
                        Debug.WriteLine(string.Format(CultureInfo.InvariantCulture, "Started new Worker Thread. Total active: {0}", i + 1));
                    }
                }
        
                public void Stop()
                {
                    if (this.Workers != null)
                    {
                        lock (lockVar)
                        {
                            for (int i = 0; i < this.Workers.Count; i++)
                            {
                                //Thread thread = this.Threads[i];
                                //thread.Interrupt();
                                this.Workers[i].IsEnabled = false;
                            }
        
                            for (int i = this.Workers.Count - 1; i >= 0; i--)
                            {
                                Worker worker = this.Workers[i];
                                while (worker.IsRunning)
                                {
                                    Thread.Sleep(32);
                                }
                            }
        
                            foreach (Thread thread in this.Threads)
                            {
                                thread.Abort();
                            }
        
                            this.Workers.Clear();
                            this.Threads.Clear();
                        }
                    }
                }
        
            }
        }
        
        using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Text;
        using System.Data.SqlClient;
        using System.Data;
        using System.Threading;
        using System.ComponentModel;
        using System.Data.OleDb;
        
        namespace ThreadAndSQLTester
        {
            class Worker
            {
                public bool IsEnabled { get; set; }
                public bool IsRunning { get; set; }
                public string Name { get; set; }
                public int Sleep { get; set; }
        
                private string dataCnString { get; set; }
                private string logCnString { get; set; }
        
                private List<Log> Logs { get; set; }
        
                public Worker(string threadPoolId)
                {
                    this.Logs = new List<Log>();
                    
                    SqlConnectionStringBuilder cnBldr = new SqlConnectionStringBuilder();
                    cnBldr.DataSource = @"trgcrmqa3";
                    cnBldr.InitialCatalog = "Scratch";
                    cnBldr.IntegratedSecurity = true;
                    cnBldr.MultipleActiveResultSets = true;
                    cnBldr.Pooling = true;            
        
                    dataCnString = GetConnectionStringWithWorkStationId(cnBldr.ToString(), threadPoolId);            
        
                    cnBldr = new SqlConnectionStringBuilder();
                    cnBldr.DataSource = @"trgcrmqa3";
                    cnBldr.InitialCatalog = "Scratch";
                    cnBldr.IntegratedSecurity = true;
        
                    logCnString = GetConnectionStringWithWorkStationId(cnBldr.ToString(), string.Empty);
        
                    IsEnabled = true;
                }
        
                private string machineName { get; set; }
                private string GetConnectionStringWithWorkStationId(string connectionString, string connectionPoolToken)
                {
                    if (string.IsNullOrEmpty(machineName)) machineName = Environment.MachineName;
        
                    SqlConnectionStringBuilder cnbdlr;
                    try
                    {
                        cnbdlr = new SqlConnectionStringBuilder(connectionString);
                    }
                    catch
                    {
                        throw new ArgumentException("connection string was an invalid format");
                    }
        
                    cnbdlr.WorkstationID = machineName + connectionPoolToken;
        
                    return cnbdlr.ConnectionString;
                }
        
                public void Work()
                {
                    int i = 0;
        
                    while (this.IsEnabled)
                    {
                        this.IsRunning = true;
        
                        try
                        {
                            Log log = new Log();
                            log.WorkItemId = Guid.NewGuid();
                            log.StartTime = DateTime.Now;
                            List<object> lst = new List<object>();
        
                            using (SqlConnection cn = new SqlConnection(this.dataCnString))
                            {
                                try
                                {
                                    cn.Open();
        
                                    using (SqlCommand cmd = new SqlCommand("Analysis.spSelectTestData", cn))
                                    {
                                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        
                                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) // DBHelper.ExecuteReader(cn, cmd))
                                        {                                    
                                            while (dr.Read())
                                            {
                                                CreateClaimHeader2(dr, lst);
                                            }
        
                                            dr.Close();
                                        }
        
                                        cmd.Cancel();
                                    }
                                }
                                catch { }
                                finally
                                {
                                    cn.Close();
                                }
                            }
        
                            log.StopTime = DateTime.Now;
                            log.RouteName = this.Name;
                            log.HostName = this.machineName;
        
                            this.Logs.Add(log);
                            i++;
        
                            if (i > 1000)
                            {
                                Console.WriteLine(string.Format("Thread: {0} executed {1} items.", this.Name, i));
                                i = 0;
                            }
        
                            if (this.Sleep > 0) Thread.Sleep(this.Sleep);
                        }
                        catch { }
                    }
        
                    this.LogMessages();
        
                    this.IsRunning = false;
                }       
        
                private void CreateClaimHeader2(IDataReader reader, List<object> lst)
                {
                    lst.Add(reader["ClaimHeaderID"]);
                    lst.Add(reader["ClientCode"]);
                    lst.Add(reader["MemberID"]);
                    lst.Add(reader["ProviderID"]);
                    lst.Add(reader["ClaimNumber"]);
                    lst.Add(reader["PatientAcctNumber"]);
                    lst.Add(reader["Source"]);
                    lst.Add(reader["SourceID"]);
                    lst.Add(reader["TotalPayAmount"]);
                    lst.Add(reader["TotalBillAmount"]);
                    lst.Add(reader["FirstDateOfService"]);
                    lst.Add(reader["LastDateOfService"]);
                    lst.Add(reader["MaxStartDateOfService"]);
                    lst.Add(reader["MaxValidStartDateOfService"]);
                    lst.Add(reader["LastUpdated"]);
                    lst.Add(reader["UpdatedBy"]);
                }
        
                /// <summary>
                /// Toes the data table.
                /// </summary>
                /// <typeparam name="T"></typeparam>
                /// <param name="data">The data.</param>
                /// <returns></returns>
                public DataTable ToDataTable<T>(IEnumerable<T> data)
                {
                    PropertyDescriptorCollection props =
                        TypeDescriptor.GetProperties(typeof(T));
        
                    if (props == null) throw new ArgumentNullException("Table properties.");
                    if (data == null) throw new ArgumentNullException("data");
        
                    DataTable table = new DataTable();
                    for (int i = 0; i < props.Count; i++)
                    {
                        PropertyDescriptor prop = props[i];
                        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                    }
                    object[] values = new object[props.Count];
                    foreach (T item in data)
                    {
                        for (int i = 0; i < values.Length; i++)
                        {
                            values[i] = props[i].GetValue(item) ?? DBNull.Value;
                        }
                        table.Rows.Add(values);
                    }
                    return table;
                }
        
        
                private void LogMessages()
                {
                    using (SqlConnection cn = new SqlConnection(this.logCnString))
                    {
                        try
                        {
                            cn.Open();
                            DataTable dt = ToDataTable(this.Logs);
        
                            Console.WriteLine(string.Format("Logging {0} records for Thread: {1}", this.Logs.Count, this.Name));
        
                            using (SqlCommand cmd = new SqlCommand("Analysis.spInsertWorkItemRouteLog", cn))
                            {
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
        
                                cmd.Parameters.AddWithValue("@dt", dt);
        
                                cmd.ExecuteNonQuery();
                            }
        
                            Console.WriteLine(string.Format("Logged {0} records for Thread: {1}", this.Logs.Count, this.Name));
                        }
                        finally
                        {
                            cn.Close();
                        }
                    }
                }
            }
        }
    
    
    

    Friday, September 30, 2011 7:09 PM

Answers

  • Ok, I did contention analysis on my side and what I see is contention in GetString as well.   The contention in my case was on gc handle during allocations.  This sort of makes sense since GetString is allocating a string every time (and this code is the hot spot for repro).

    Hence to work around the issue I configured my app to use server GC, with the following app.config =>

     

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    	<runtime>
    		<gcServer enabled="true" />
    	</runtime>
    </configuration>
    

    I ran the same test with server GC enabled and ALL of the contention on the user threads went away.   I'll post back with numbers for server GC.  

    Moral of this story is IF you want to write ANY code to scale well in a single process, you should turn on server GC.  Looks like with workstation GC mode it will more frequently block user threads when GC activities occur.


    Matt
    • Marked as answer by Sean_Pardue Saturday, October 29, 2011 5:34 AM
    Monday, October 24, 2011 11:44 PM
    Moderator

All replies

  • Hi Sean,

    Welcome!

    Based on your code, you are using multi-thread to read database, I'm not sure about your question here. Any exceptions or the performance doesn't as you expected? I think this link is helpful: http://msdn.microsoft.com/en-us/library/ms810437.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 3, 2011 9:51 AM
    Moderator
  • No it doesn't perform as expected. It appears that the SqlDataReader is using a shared resource at some point and therefor does not scale.

    Tuesday, October 4, 2011 3:24 PM
  • Hi Sean,

    Thanks for your feedback.

    I'm not sure about what you really want. Each thread has each SqlDataReader, there is not problem. Are you just record the Log of each DataReader read data? CreateClaimHeader2 method just append all the records.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 5, 2011 9:17 AM
    Moderator
  • I agree, it should not be a problem, but it is. It does not scale. The threads deadlock on the SqlDataReader.

    Wednesday, October 5, 2011 2:40 PM
  • Hi Alan,

    Based on Sean's code, the CreateClaimHeader2 method serves no purpose other than to pull data off of the reader as an example of using the reader.  The data contained inside the reader is unimportant to the question he has, which I have experienced also.  The question is about the performance of SqlDataReader and Adapters in a multi-threaded environment sharing no resources, and therefore performing no synchronizations.

    To rephrase his question, each thread has a SqlDataReader that pulls data from a stored procedure, logs how long it took to pull this data (for determining per minute rate processing I would assume), and then throws that data away.  At the end, all logs are sent to the database.  The readers inside the threads are local to the thread, and not shared nor know about the other threads.  So while there should not be a problem, as you state, there is.  The concurrency charts he provided from Visual Studio 2010 Performance Explorer tell the story better, but when I tried similar code I achieved a rate of 5000/min with 1 thread, 22000/min with 5 threads, 27000/min with 10 threads, and 19000/min with 20 threads.   This is on a Windows Server 2008 with 24 Cores and 48 Gb of Ram.  If the SqlDataReader scaled, then the numbers would be 5000/min for 1 thread, 25000/min for 5 threads, 50000/min for 10 threads, and 100000/min for 20 threads.  

    The question is then, with no network problems or sql bottlenecks, memory and cpu not being an issue at all (the cpu never exceeded 46%), why does VS2010 show contention between the threads on the SqlDataReader (as shown in the charts provided)?

    Also note, looking through the code, each thread has its own id which is part of the connection string, therefore Sql Server will give it it's own thread pool, so this can't be an issue.

    Thursday, October 6, 2011 12:25 AM
  • Hi all,

    Thanks a lot for your detailed explanation.  It's quite interesting!  I have not studied the SqlDataReader deeply, but could this issue be related to the database implementation?  It may be blocked when multiple instances trying to connect a single table within the same app?  http://blogs.msdn.com/b/khen1234/archive/2005/10/18/482202.aspx.  

    Besides, is it possbile to send us some repro demo and db files?  If you can share some VS profiler logs with us, that would be great! 

    Have a nice day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 10, 2011 3:11 AM
    Moderator
  • Michael,

    The stored procedure being executed is a select statement using the with (nolock) hint. It does not appear to be a database issue, we profile the sql server when running these tests and see no locking nor waiting tasks.

    We are able to run the test application 10 times with 1 thread and everything scales appropriately. We see thread contention when we try to tread-up inside of 1 application.

    How would I go about attaching our Visual Studio solution and a database backup of our test code to this forum?

    Thanks,
    Sean

    Wednesday, October 12, 2011 12:36 AM
  • Hi Sean,

    Sorry, I forgot to tell you my mail address.  You can directly ping me at misun@microsoft.com, if you want to share some demo with me. 

    Have a nice day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, October 12, 2011 1:24 AM
    Moderator
  • Michael,

    I have sent an email with the source code and database backup to the email address you provided.

     

    Thanks,

    Sean

    Friday, October 14, 2011 5:17 PM
  • I see a number of issues here. First, you're using MARS. In a word: Don't. It simply causes problems. I've written about this a number of times and I'll leave it at that. These problems are exacerbated when you reach the 10 query limit when things really get ugly. I expect this is the source of your issues. Disable MARS and retry.

    Next, I would not rebuild the ConnectionString on each invocation. I would build them at design-time and work with the Connection Pool.

    I would also use the Datatable.Load method to populate the DataTable from the DataReader. Why re-invent the wheel?

    So, why not use the Async methods of the DataReader to run the query? If you're interested in overlapping operations, this would make sense. Consider that the FETCH portion of the operation is still Synchronous.

     

    hth


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Friday, October 14, 2011 6:50 PM
    Moderator
  • Hi Sean,

    William is very senior in ADO.NET programming.  Also, I think his reply is very helpful on this issue.  Can you try his suggestions and let us know the result? 

    Good day, both!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, October 17, 2011 1:49 AM
    Moderator
  • Hi William,

    We have tried enabling and disabling MARS and do not get any different performance in regards to thread contention.

    We are using the connection pool and have tried several different approaches here. The last approach was to create a seperate connection pool for each thread by modifying the WorkStationId. 1 connection pool or multiple connection pools do not yield different contention results either.

    We tried DataTable.Load and the problem got worse.

    We tried the Async methods and still receive contention on the GetValue call of the DataReader.

    Also note that this is a stripped down example of a much larger application to show the contention issues we are seeing with the SQL DataReader. That is why we are doing some of the things you see in the example with connection strings and traversing the DataReader.

    Do you have any more thoughts on the contention we are seeing? It seems to be very low level in the System.Data.dll with the SNIReadSync method and we are not sure on how to overcome it. We have come up with a very complicated work-around were we actually launch other processes that perform the SQL processing, but this seems like a very ugly kludge around and scaling problem that should not be there in the MS assembly.

     

    Thanks,

    Sean

     

    Tuesday, October 18, 2011 1:57 PM
  • Hi Sean,

     

    First, let me know what version of .NET you are using so I can investigate further.   Then I'll see if I can reproduce this independently and post back.

    The stack you show in the statistical profiler is waiting for SNIReadSync to return.   SNIReadSync is posting a read to SQL Server at the network level then waiting for network response.   Typically if you have a fast network and SQL Server has enough CPU, you will never see any waiting in SNIReadSync.  The reason for this is the server will send the response over tcp-ip and tcp-ip layer will buffer data on the client side faster than you can read it off the wire.

    But it could be that either SQL is not sending the data fast enough OR the network bandwidth or latency is slowing down the response.   For example 10 threads are saturating the network, whereas 1 thread is not.

    However, the fact that "We can run the application 10 times with 1 thread and it scales linearly, but 10 threads in 1 app does not scale." tells me that this is not likely the problem.  My assumption here is you ran 10 processes concurrently and you linearly scale, whereas 1 process with 10 threads does not scale.  So this does not sound like contention on server side or network bottleneck issue.

    Hence I do suspect some limiting factor on client side.   

    One thing you could try in the meanwhile is the SqlDataReader.GetValues method.  This reads entire row of values in one call.  Should be cheaper than fetching values one at a time, including all of the expensive index lookups (calling reader["mycolumn"]), should improve performance.


    Matt
    Wednesday, October 19, 2011 6:38 AM
    Moderator
  • Matt,

     

    We are using .Net 4.0.

    Your assumption with the 10 concurrent processes is correct. We have eliminated the possibility of network bottleneck.

    We will try the GetValues method and will get back to you.

    Thanks,

    Sean

    Wednesday, October 19, 2011 1:33 PM
  • Apparently we have the same issue. Using the concurrency profiler we get a lot of contention on SqlCommand.ExecuteScalar/SqlDataReader.GetValue.

     

    Sean, did you get some results using GetValues instead of Getvalue?

     

    thanks.

    Monday, October 24, 2011 5:13 AM
  • Hi folks,

    I ran an experiment over the weekend on two different machines and I see the following results:

     

    Threads SnacSingleProc SnacMultiProc Delta SqlClientSingleProc SqlClientMultProc Delta
    2 103929029 104143306 0% 27500101 28231530 3%
    4 135217733 138970628 3% 45811653 46093092 1%
    8 144099221 150487832 4% 46515925 46675960 0%
                 
                 
    Threads SnacSingleProc SnacMultiProc Delta SqlClientSingleProc SqlClientMultProc Delta
    2 70063173 72096042 3% 20111525 21865818 8%
    4 91791829 104264772 12% 34225861 37793044 9%
    8 103514741 105786216 2% 36957301 38257336 3%

    I tested the SQL Native Client driver versus the SqlClient driver.  The delta is for single proc versus multi-proc tests for same provider.   I see only a small advantage when using multitple processes.   It looks to me like IF the machine is CPU bound, using multiple processes is more advantageous but still not by much (top set of numbers for less CPU bound machine versus bottom set for CPU bound machine).

    To get the numbers I ran 5 separate runs and then summed the value.  So to be honest the difference is statistically very small, esp when not CPU bound, but there appears to be some difference.

    Below is the code I used to replicate the results.  Note I strictly used character columns (20 columns with 20 chars per column), I'm going to run a test with datetime column next to see if there is more of a difference (I noticed your table had many datetime columns).

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Odbc;
    using System.Data.SqlClient;
    using System.Threading;
    using System.Diagnostics;
    using System.Reflection;
    using System.Runtime.InteropServices;
    
    namespace ReadScale
    {
    	class Program
    	{
    		/// <summary>Number of milliseconds to run warmup before capturing perf data.</summary>
    		const int warmupMilliseconds = 30000;
    
    		/// <summary>Number of milliseconds to capture perf statistics</summary>
    		const int captureMilliseconds = 60000;
    
    		/// <summary>
    		/// Number of milliseconds to keep running workload after capturing perf statistics
    		/// The reason for this is when running processes in parallel, you want to keep the same consistent load 
    		/// on the server while all concurrent processes are finishing up their workload period.
    		/// Since not all processes start at the same time, you need some lag at the end to ensure all concurrent
    		/// processes are running all the time during the captureMilliseconds period.
    		/// </summary>
    		const int warmdownMilliseconds = 30000;
    
    		/// <summary>
    		/// Number of row count to copy out block size.
    		/// Copying row count in blocks reduces contention on the lock (avoids taking lock each time we update the counter).
    		/// </summary>
    		const int rowCopyThreshold = 10000;
    
    		/// <summary>Current SQL Server target (from command line args)</summary>
    		static string currentServer;
    
    		/// <summary>Tracks number of rows read for a single process</summary>
    		static int rowsRead = 0;
    
    		/// <summary>Grab rowsReadLock before updating rowsRead counter.</summary>
    		static object rowsReadLock = new object();
    
    		/// <summary>Flag used to control running of all worker threads, set to false to stop the threads.</summary>
    		static bool keepRunning = true;
    
    		/// <summary>
    		/// Main entry point.  See usage below for details about arguments.
    		/// </summary>
    		static void Main(string[] args)
    		{
    			if (args.Length < 3)
    			{
    				log("Usage: ReadScale <server> <mode> [<options>]");
    				log(" <server>  - Target SQL Server. Note a new database named ReadScale will be created.");
    				log(" <mode>    - Specify setup|launch|execute");
    				log("                     setup   = setup database given <options>");
    				log("                     launch  = launch a series of tests given <options>");
    				log("                     execute = Execute a single test give <options>");
    				log(" setup <rowCount> <columnCount> <charPerColumn>");
    				log("           - <rowCount>      = Number of rows in read workload table");
    				log("           - <columnCount>   = Number of columns in read workload table");
    				log("           - <charPerColumn> = Number of characters per column in read workload table");
    				log(" launch <provider> <threadCountList>");
    				log("           - <provider>      = snac|sqlclient");
    				log("           - <threadCountList> = Comma-separated list of thread counts to test (for example 1,2,4,8)");
    				log(" execute <provider> <tag> <threadCount>");
    				log("           - <provider>      = snac|sqlclient");
    				log("           - <tag>           = Textual tag to identify workload type (for stats grouping)");
    				log("           - <threadCount>   = Number of concurrent threads inside process to run read workloads");
    				return;
    			}
    
    			int argNumber = 0;
    			foreach (string arg in args)
    			{
    				log("Arg[{0}]={1}", argNumber, arg);
    				argNumber++;
    			}
    
    			currentServer = args[0];
    			string mode   = args[1];
    
    			bool setup = (mode.Equals("setup"));
    			bool launch = (mode.Equals("launch"));
    			bool execute = (mode.Equals("execute"));
    
    			string tag = "";
    			string provider = "";
    			int rowCount, columnCount, charsPerColumn, threadCount;
    			List<int> threadCountList = new List<int>();
    
    			rowCount = columnCount = charsPerColumn = threadCount = 0;
    
    			if (setup)
    			{
    				rowCount = int.Parse(args[2]);
    				columnCount = int.Parse(args[3]);
    				charsPerColumn = int.Parse(args[4]);
    			}
    			else if (launch)
    			{
    				provider = args[2];
    				threadCountList.AddRange(args[3].Split(',').Select(s => int.Parse(s)).ToList());
    			}
    			else if (execute)
    			{
    				provider = args[2];
    				tag = args[3];
    				threadCount = int.Parse(args[4]);
    			}
    			else
    			{
    				Console.WriteLine("Unrecognized mode {0}", mode);
    				return;
    			}
    
    			try
    			{
    				using (SqlConnection conn = GetConnection("master"))
    				{
    					if (setup)
    					{
    						log("Setting up database and target table with {0} rows and {1} columns with {2} chars/column", rowCount, columnCount, charsPerColumn);
    						xsql_ne(conn, "drop database ReadScale");
    						xsql(conn, "create database ReadScale");
    						xsql(conn, "use ReadScale");
    						xsql(conn, "create table stats(Time datetime default(getdate()), Tag varchar(255), Provider varchar(255), Threads int, RowsRead int, RowsPerSecond int)");
    						xsql(conn, "create table readscale(id int, " + Replicate("f{0} varchar(20) default (replicate('x'," + charsPerColumn + "))", columnCount) + ")");
    						xsql(conn, "insert into readscale (id) values (1)");
    						xsql(conn, string.Format("while ((select count(*) from readscale) < {0}) insert into readscale (id) select top 1000000 id from readscale", rowCount));
    						log("Prepared database, exiting.");
    						return;
    					}
    					else if (launch)
    					{
    						xsql(conn, "use ReadScale");
    
    						// First run test with single thread and N threads, then N processes with 1 thread.
    						foreach (int t in threadCountList) 
    						{
    							ShellTestAndWaitForExit(conn, string.Format("P1,T{0}", t), provider, 1, t);
    							ShellTestAndWaitForExit(conn, string.Format("P{0},T1", t), provider, t, 1);
    						}
    						return;
    					}
    					else
    					{
    						// Run the test.
    						xsql(conn, "use ReadScale");
    						RunScalabilityTest(conn, tag, provider, threadCount);
    					}
    				}
    			}
    			catch (Exception ex)
    			{
    				log("Exception => {0}", ex.Message);
    			}
    		}
    
    		/// <summary>
    		/// Launches a single scalability test.
    		/// Test can either use multiple processes or multiple threads in the same process.
    		/// The purpose is to compare the performance of running workloads in a single process versus multiple processes.
    		/// </summary>
    		static void RunScalabilityTest(SqlConnection conn, string tag, string provider, int threadCount)
    		{
    			log("Starting test {0} with {1} threads...", tag, threadCount);
    			rowsRead = 0;
    			keepRunning = true;
    			for (int i = 1; i<=threadCount; i++)
    			{
    				new Thread(new ParameterizedThreadStart(WorkerThread)).Start(provider);
    			}
    
    			log("Warming up server for {0} seconds...", warmupMilliseconds/1000);
    			Thread.Sleep(warmupMilliseconds);
    			int startRowsRead, endRowsRead;
    			lock (rowsReadLock)
    			{
    				startRowsRead = Interlocked.Increment(ref rowsRead);
    			}
    
    			log("Capturing data for {0} seconds...", captureMilliseconds/1000);
    			Thread.Sleep(captureMilliseconds);
    
    			lock (rowsReadLock)
    			{
    				endRowsRead = Interlocked.Increment(ref rowsRead);
    			}
    
    			int totalRowsRead = (endRowsRead - startRowsRead);
    			int rowsPerSecond = totalRowsRead / (captureMilliseconds / 1000);
    			log("Read {0} rows in {1} seconds ({2} rows/sec)", totalRowsRead, captureMilliseconds / 1000, rowsPerSecond);
    
    			log("Warming down server for {0} seconds...", warmdownMilliseconds);
    			Thread.Sleep(warmdownMilliseconds);
    
    			keepRunning = false;
    
    			xsql(conn, string.Format("insert into stats (Tag, Provider, Threads, RowsRead, RowsPerSecond) values ('{0}','{1}',{2},{3},{4})", tag, provider, threadCount, totalRowsRead, rowsPerSecond));
    		}
    
    		/// <summary>
    		/// Runs a single workload test.
    		/// Worker starts up and blocks on GlobalStartWork app lock until released.
    		/// Then caller keeps looping processing workload until primary thread sets keepRunning = false to shut down the thread.
    		/// </summary>
    		/// <param name="providerParam"></param>
    		static void WorkerThread(object providerParam)
    		{
    			string provider = (string)providerParam;
    			try
    			{
    				using (SqlConnection connLock = GetConnection("ReadScale"))
    				{
    					// Block on GlobalStartWork lock until we are allowed to continue.
    					// This is used to serialize start of concurrently running processes.
    					SqlTransaction tranLock = connLock.BeginTransaction();
    					xsql(connLock, "exec sp_getapplock 'GlobalStartWork', 'Shared'", tranLock);
    					log("Worker thread gained GlobalStartWork lock, starting work.");
    					xsql(connLock, "exec sp_releaseapplock 'GlobalStartWork'", tranLock);
    					tranLock.Rollback();
    				}
    
    				using (IDbConnection conn = GetConnection("ReadScale", provider))
    				{
    					IDbCommand cmd = conn.CreateCommand();
    					cmd.CommandText = "select * from ReadScale with (nolock)";
    
    					while (keepRunning)
    					{
    						int localRowCount = 0;
    						using (IDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
    						{
    							int columnCount = dr.FieldCount - 1;
    							while (dr.Read())
    							{
    								for (int i = 1; i <= columnCount; i++)
    								{
    									dr.GetString(i);
    								}
    								localRowCount++;
    								if (!keepRunning) break;
    								if (localRowCount == rowCopyThreshold)
    								{
    									lock (rowsReadLock)
    									{
    										rowsRead += localRowCount;
    									}
    									localRowCount = 0;
    								}
    							}
    
    							// Add remaining rows to row count summary.
    							lock (rowsReadLock)
    							{
    								rowsRead += localRowCount;
    							}
    						}
    					}
    				}
    			}
    			catch (Exception ex)
    			{
    				log("Thread exception => {0}", ex.Message);
    			}
    		}
    
    		/// <summary>
    		/// Shells one or more processes to execute a test run.
    		/// Attempts to serialize start of work for each process by grabbing exclusive lock on GlobalStartWork app lock.
    		/// Once all processes are started, it releases the lock to allow all processes fair access to SQL resources.
    		/// </summary>
    		public static void ShellTestAndWaitForExit(SqlConnection conn, string tag, string provider, int processes, int threadCount)
    		{
    			List<System.Diagnostics.Process> waitForProcesses = new List<Process>();
    			System.Reflection.Assembly a = System.Reflection.Assembly.GetEntryAssembly();
    
    			// Create app lock to synchronize start of all worker threads and processes.
    			SqlTransaction tranLock = conn.BeginTransaction();
    			xsql(conn, "exec sp_getapplock 'GlobalStartWork', 'Exclusive'", tranLock);
    			log("Test runner owns GlobalStartWork lock.");
    
    			for (int i = 1; i <= processes; i++)
    			{
    				System.Diagnostics.Process cmd = new System.Diagnostics.Process();
    				cmd.StartInfo.FileName = a.Location;
    				cmd.StartInfo.Arguments = string.Format("{0} execute {1} {2} {3}", currentServer, provider, tag, threadCount);
    				cmd.StartInfo.UseShellExecute = false;
    				cmd.StartInfo.RedirectStandardOutput = false;
    				cmd.Start();
    				waitForProcesses.Add(cmd);
    			}
    
    			// Give about 5 seconds to let everyone block on the GlobalStartWork lock.
    			Thread.Sleep(5000);
    
    			// Release lock.
    			xsql(conn, "exec sp_releaseapplock 'GlobalStartWork'", tranLock);
    			log("Test runner released GlobalStartWork lock.");
    			tranLock.Rollback();
    
    			// Wait for all processes to exit.
    			waitForProcesses.ForEach(cmd => cmd.WaitForExit());
    		}
    
    		/// <summary>
    		/// Returns an opened SqlConnection to specified database.
    		/// </summary>
    		static SqlConnection GetConnection(string database)
    		{
    			SqlConnection conn = new SqlConnection(string.Format("server={0};initial catalog={1};integrated security=sspi;", currentServer, database));
    			conn.Open();
    			return conn;
    		}
    
    		/// <summary>
    		/// Returns an opened IDbConnection to specified database and provider.
    		/// </summary>
    		static IDbConnection GetConnection(string database, string provider)
    		{
    			if (provider.Equals("snac"))
    			{
    				SqlConnection conn = new SqlConnection(string.Format("server={0};initial catalog={1};integrated security=sspi;", currentServer, database));
    				conn.Open();
    				return (IDbConnection)conn;
    			}
    			else
    				if (provider.Equals("sqlclient"))
    				{
    					OdbcConnection conn = new OdbcConnection(string.Format("DRIVER=SQL Server Native Client 11.0;SERVER={0};DATABASE={1};Trusted_Connection=Yes;", currentServer, database));
    					conn.Open();
    					return (IDbConnection)conn;
    				}
    				else
    				{
    					throw new ArgumentException("Invalid argument => " + provider);
    				}
    		}
    
    		/// <summary>
    		/// Logs message to console window with system time stamp.
    		/// </summary>
    		static void log(string format, params object[] args)
    		{
    			string message = string.Format(format, args);
    			string status = string.Format("{0} {1}", GetSystemTimeString(), message);
    			Console.WriteLine(message);
    		}
    
    		/// <summary>
    		/// Struct for GetSystemTime below.
    		/// </summary>
    		private struct SYSTEMTIME
    		{
    			public ushort wYear;
    			public ushort wMonth;
    			public ushort wDayOfWeek;
    			public ushort wDay;
    			public ushort wHour;
    			public ushort wMinute;
    			public ushort wSecond;
    			public ushort wMilliseconds;
    		}
    
    		[DllImport("kernel32.dll", SetLastError = true)]
    		static extern void GetSystemTime(ref SYSTEMTIME lpSystemTime);
    		/// <summary>
    		/// GetSystemTimeString is used for timestamping logs to get consistent date and time across all systems.
    		/// For example Windows cluster log and  uses system time 
    		/// </summary>
    		static string GetSystemTimeString()
    		{
    			SYSTEMTIME st = new SYSTEMTIME();
    			GetSystemTime(ref st);
    			return string.Format("{0:D2}-{1:D2}-{2:D2} {3:D2}:{4:D2}:{5:D2}.{6:D3}",
    					st.wYear, st.wMonth, st.wDay, st.wHour, st.wMinute, st.wSecond, st.wMilliseconds);
    		}
    
    		/// <summary>
    		/// Replicate a string given format and count.  
    		/// Useful for constructing arbitrary column count create table statements.
    		/// Example input "p{0} int", 5 => "p1 int, p2 int, p3 int, p4 int, p5 int"
    		/// </summary>
    		public static string Replicate(string format, int count)
    		{
    			StringBuilder sb = new StringBuilder(format.Length * count);
    			for (int i = 1; i <= count; i++)
    			{
    				sb.Append(string.Format(format, i));
    				if (i < count) sb.Append(',');
    			}
    			return sb.ToString();
    		}
    
    		/// <summary>
    		/// Execute T-SQL statement and silently catch any exception (ne = no exception).
    		/// </summary>
    		static void xsql_ne(SqlConnection conn, string sql)
    		{
    			try { xsql(conn, sql); }
    			catch (Exception) { }
    		}
    
    		/// <summary>
    		/// Execute T-SQL statement.
    		/// </summary>
    		static void xsql(SqlConnection conn, string sql)
    		{
    			using (SqlCommand cmd = conn.CreateCommand())
    			{
    				cmd.CommandTimeout = 0;
    				cmd.CommandText = sql;
    				cmd.ExecuteNonQuery();
    			}
    		}
    
    		/// <summary>
    		/// Execute T-SQL statement with associated transaction.
    		/// </summary>
    		static void xsql(SqlConnection conn, string sql, SqlTransaction trans)
    		{
    			using (SqlCommand cmd = conn.CreateCommand())
    			{
    				cmd.CommandTimeout = 0;
    				cmd.CommandText = sql;
    				cmd.Transaction = trans;
    				cmd.ExecuteNonQuery();
    			}
    		}
    	}
    }
    
    


    Matt
    Monday, October 24, 2011 5:42 PM
    Moderator
  • Note I will also run above in concurrency profiler to see what I can see.
    Matt
    Monday, October 24, 2011 5:42 PM
    Moderator
  • Note my perf numbers are backwards (column for SNAC and SqlClient are reversed) due to a bug in my code :)

    Here is fix for above ->

     

    static IDbConnection GetConnection(string database, string provider)

     

    		/// <summary>
    		/// Returns an opened IDbConnection to specified database and provider.
    		/// </summary>
    		static IDbConnection GetConnection(string database, string provider)
    		{
    			if (provider.Equals("sqlclient"))
    			{
    				SqlConnection conn = new SqlConnection(string.Format("server={0};initial catalog={1};integrated security=sspi;", currentServer, database));
    				conn.Open();
    				return (IDbConnection)conn;
    			}
    			else
    			if (provider.Equals("snac"))
    			{
    				OdbcConnection conn = new OdbcConnection(string.Format("DRIVER=SQL Server Native Client 11.0;SERVER={0};DATABASE={1};Trusted_Connection=Yes;", currentServer, database));
    				conn.Open();
    				return (IDbConnection)conn;
    			}
    			else
    			{
    				throw new ArgumentException("Invalid argument => " + provider);
    			}
    		}
    

     


    Matt
    Monday, October 24, 2011 7:16 PM
    Moderator
  • Ok, I did contention analysis on my side and what I see is contention in GetString as well.   The contention in my case was on gc handle during allocations.  This sort of makes sense since GetString is allocating a string every time (and this code is the hot spot for repro).

    Hence to work around the issue I configured my app to use server GC, with the following app.config =>

     

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    	<runtime>
    		<gcServer enabled="true" />
    	</runtime>
    </configuration>
    

    I ran the same test with server GC enabled and ALL of the contention on the user threads went away.   I'll post back with numbers for server GC.  

    Moral of this story is IF you want to write ANY code to scale well in a single process, you should turn on server GC.  Looks like with workstation GC mode it will more frequently block user threads when GC activities occur.


    Matt
    • Marked as answer by Sean_Pardue Saturday, October 29, 2011 5:34 AM
    Monday, October 24, 2011 11:44 PM
    Moderator
  • I tested with server GC, the delta is statistically very small now =>

     

    Threads SnacSingleProc SnacMultiProc Delta SqlClientSingleProc SqlClientMultProc Delta
    2 24027285 24287290 1% 108027605 108587610 1%
    4 42764501 43921668 3% 156246405 156894996 0%
    8 42683045 44094504 3% 158496373 160212168 1%

    Matt
    Tuesday, October 25, 2011 5:17 AM
    Moderator
  • Also, let me know if using gcServer enabled="true" does not solve your problem!


    Matt
    Wednesday, October 26, 2011 6:00 PM
    Moderator
  • Matt,

     

    Thanks so much for your help! This setting allows our test application to scale linearly on our Windows Server machine.

    Saturday, October 29, 2011 5:36 AM