title: Optimize response time from API, with SQL Server involved
body: <p>I have a project with requirements that response time should be under 0.5sec under load as 3000 concurrency users;</p>
  
  <p>I have few API which use some aggregation from SQL Server. 
  when we testing it with 3000CCU average response time about 15 second. And also 500 error due to SQL can't handle so many requests. Actually requests to SQL Server interrupt with timeout) 
  Our current instance is r4.2xlarge 8CPU and 61GB Memory. </p>
  
  <p>All code is asynchronous without blocking operations. 
  We running our app behind load balancer with 10 instances 300 CCU per instance in this case.  utilization on instances about 30%. The bottleneck  currently is SQL server.</p>
  
  <p>I see few solution. Setup some big SQL, Cluster or Sharding, I'm not really sure. I'm not strong in that. </p>
  
  <p>Or use cache for request. We have mostly read only data, which we can cache after aggregation.</p>
  
  <p><strong>Update:</strong>
  I need solution to cache exactly sql responses. To order work with it late with LINQ.</p>
  
  <p>But it seems there is no ready solution for that.</p>
  
  <p>I found good try for that called CacheManager. But there are few problems exist with this. </p>
  
  <ol>
  <li>It works with Redis only in sync mode, means use sync command instead of async.  </li>
  <li>There is no implementation of concurrency lock, which can occur in our case because we have 10 instances.  We need solution which work with as distributed cache. </li>
  <li>There are few bugs which utilize Redis multiplexor wrong. And you constantly will have connection problem. </li>
  </ol>
  
  <p>Please advice how to overcome this issue. How you solve it. I'm sure there are people who already solve it somehow. </p>