title: C# Web API SQL Server 2012: Errors Under Load Test (14 million+ Records)
body: <p>I am trying to build a Web API that connects to a SQL Server 2012 database with 14 million+ records in a single table. The only action for the API is GET, and will be available to the public as an Open Data API, so it needs to be able to handle many concurrent users.</p>
  
  <p>The table has seven fields:</p>
  
  <pre><code>field1 bigint  
  field2 nvarchar(50)  
  field3 nvarchar(10)  
  field4 float  
  field5 datetime  
  field6 nvarchar(20)  
  field7 nvarchar(10)  
  </code></pre>
  
  <p>I have written simple test APIs in:<br>
  - C# .NET 4.6.1<br>
  - C# .NET Core 2</p>
  
  <p>I've also tried ApirIO as a Nuget package, and as a command-line app.</p>
  
  <p>I have also tried using Python Eve with SQLAlchemy but with similar results.</p>
  
  <p>The API works in that I can see the results on my browser, Postman, cURL, etc. But when I try to load test using Vegeta at a rate of 30 requests per second (test duration 30 seconds), I get multiple connection errors and latency rises to around 30 seconds.  </p>
  
  <p>I have pasted the results for a load test on the API running on the AperIO command line app: </p>
  
  <pre><code>Requests      [total, rate]            900, 30.03
  Duration      [total, attack, wait]    59.9700536s, 29.966666367s, 30.003387233s
  Latencies     [mean, 50, 95, 99, max]  29.903549803s, 30.002625352s, 30.004389905s, 30.012575115s, 30.03090955s
  Bytes In      [total, mean]            49579, 55.09
  Bytes Out     [total, mean]            0, 0.00
  Success       [ratio]                  1.22%
  Status Codes  [code:count]             200:11  0:889  
  Error Set:
  Get http://localhost:18092/xyz?pageSize=25: net/http: timeout awaiting response headers
  </code></pre>
  
  <p>I have tried C# with and without OData, and with manually coded paging classes with the max page size set to 50, then 25, then 10, then 5, but the results are all broadly similar.</p>
  
  <p>Note: I truncated the table and re-populated with 5000 records and there were no problems with the load test. I re-populated with 14 million records and the errors re-appeared.</p>
  
  <p>Is there some way SQL Server can be optimised to serve a database with a recordset of over 14 million to an API with multiple concurrent users (e.g. 1000 users) with a very low latency (around 0.015sec)?  </p>
  
  <p>Thanks in advance,<br>
  Mo</p>
  
  <h3>Edit (to clarify from comments):</h3>
  
  <p>The server has RAM 32GB / Memory 160GB / 4 CPUs  </p>
  
  <ul>
  <li>The CPUs went to 100% after about 5 seconds, we temporarily upped the server to 8 CPUs but they also went up to 100%  </li>
  </ul>
  
  <p>There is a clustered and non-clustered index on the table  </p>
  
  <p>The Linq code for the Get method in the Controller is:</p>
  
  <pre><code>var source = (from aqm in _context.aqm_context.
           OrderBy(a =&gt; a.field1)
           select aqm).AsQueryable();
  
  var items = source.Skip((CurrentPage - 1) * PageSize).Take(PageSize).ToList();
  return items;
  </code></pre>
  
  <p>From SQL Server Profiler, the SQL being executed is  </p>
  
  <pre><code>SELECT 
      [Extent1].[field1] AS [field1], 
      [Extent1].[field2] AS [field2], 
      [Extent1].[field3] AS [field3], 
      [Extent1].[field4] AS [field4], 
      [Extent1].[field5] AS [field5], 
      [Extent1].[field6] AS [field6], 
      [Extent1].[field7] AS [field7]
      FROM [dbo].[table] AS [Extent1]
      ORDER BY [Extent1].[field1] ASC
      OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY 
  </code></pre>
  
  <p>I was originally using MongoDB with <a href="http://python-eve.org/" rel="nofollow noreferrer">Python Eve</a> (with the same recordset) and I was getting 0.015 sec latency with no errors running the same tests. MongoDB is not on my organisation's list of approved technologies (yet) so I was asked to try SQL Server as a backend. I tried the same Eve config using SQLAlchemy connected to SQL Server and immediately received poor latency and connection errors.</p>
  
  <p>So the question could also be:<br>
  - Is SQL Server a valid choice to serve an open data API with a recordset of 14 million to the public with potentially thousands of concurrent users? Or is a NoSQL document store like MongoDB better suited?</p>