none
CPU / DTUs getting maxed out on SQL Database, but top queries less than 1% RRS feed

  • Question

  • Hello,

    We just launched an Azure SQL Database, and the DTU and CPU usage is behaving strangely.  The database is only receiving about 30 requests per minute, and the CPU/DTU will be extremely low for hours, and then jump up to 100% and stay there (with no increase in the number of requests that triggers this).  When we click to view the top queries, none of them are above 1% cpu usage.  We started out on a 5 DTU plan, and yesterday upgraded to 20 DTUs and the same behavior is occurring.  Any idea what else might cause the DTU/CPU to get maxed out?  See images below:

    Thanks in advance for any advice!

    Joe

    Tuesday, October 8, 2019 4:31 PM

All replies

  • I'm getting closer, I found these repeated entries in the error log.  (about 8 - 10 per SECOND)

    The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

    The thing is, the App Service that queries the database is only doing simple selects, updates, and inserts... none of which uses any complex WHERE IN statement.  Furthermore, every query is wrapped in a try/catch block, and I'm never seeing an exception like this.

    Where could these large queries be originating from?

    Tuesday, October 8, 2019 7:20 PM
  • Hi TurboWagon25,

    Can you detail the T-SQL that is being used to insert and/or query the database? The root cause to a previous scenario with the same behavior was this:

    "On closer examination I found that I was adding supposedly 5 parameters in a loop, but the source object was not getting cleared, so the list of object to insert was just getting bigger and bigger."

    Can you ensure you are creating a new object every time. It is very possible that the same query is looping and now contains over 2100+ parameters. 

    Regards,

    Mike

    Wednesday, October 9, 2019 8:05 PM
    Moderator
  • Please let us know if you need additional assistance with this issues.

    Regards,

    Mike

    Friday, October 18, 2019 12:31 AM
    Moderator