Throttling EF queries to save DTUs-CodePudding

We have an asp.Net application using EF 6 hosted in Azure. The database runs at about 20% DTU usage for most of the time except for certain rare actions. These are almost like db dumps in Excel format, like having all orders of the last X years etc. which the (power) users can trigger and then get the result later by email.

The problem is that these queries use up all DTU and the whole application goes into a crawl. We would like to kind of throttle these non-critical queries as it doesn't matter if this takes 10-15min longer.

Googling I found the option to reduce the DEADLOCK_PRIORITY but this wont fix the issue of using up all resources.

Thanks for any pointers, ideas or solutions.

CodePudding user response：

Optimizing is going to be hard as it is more or less a db dump.

Azure SQL Database doesn't have Resource Governor available, so you'll have to handle this in code.

Azure SQL Database runs in READ COMMITTED SNAPSHOT mode, so slowing down the session that dumps the data from a table (or any streaming query plan) should reduce its DTU consumption without adversely affecting other sessions.

To do this put waits in the loop that reads the query results, either an IEnumerable<TEntity> returned from a LINQ query or a SqlDataReader returned from an ADO.NET SqlCommand.

But you'll have to directly loop over the streaming results. You can't copy the query results into memory first using IQueryable<TEntity>.ToList() or DataTable.Load(), SqlDataAdapter.Fill(), etc as that would read as fast as possible.

var results = new List<TEntity>();
int rc = 0;
using (var dr = cmd.ExecuteReader())
{
   while (dr.Read())
   {
     rc  ;
     var e = new TEntity();
     e.Id = dr.GetInt(0);
     e.Name = dr.GetString(1);
     //  ...
     results.Add(e);
     
     if (rc%100==0)
       Thread.CurrentThread.Sleep(100);
   }
}

var results = new List<TEntity>();
int rc = 0;
foreach (var e in db.MyTable.AsEnumerable())
{
   rc  ;
   var e = new TEntity();
   e.Id = dr.GetInt(0);
   e.Name = dr.GetString(1);
   //  ...
   results.Add(e);

   if (rc%100==0)
     Thread.CurrentThread.Sleep(100);

}

For extra credit, use async waits and stream the results directly to the client without batching in memory.

Alternatively, or in addition, you can limit the number of sessions that can concurrently perform the dump to one, or one per table, etc using named Application Locks.