CODEWITHSUNDEEP
azureazure-sql-database
Joe Aliperti
Joe Aliperti

Reputation: 1

CPU / DTUs getting maxed out on Azure SQL Database, but top queries less than 1% and database only a few MB

I 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 I click to view the top queries, none of them are above 1% cpu usage. I 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:

https://i.sstatic.net/JSlC2.png

https://i.sstatic.net/JIInz.png

Thanks in advance for any advice!

Joe

EDIT: 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?

Upvotes: 0

Views: 1850

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15668

You are only seeing the CPU component of the DTU graph, what about the "Data IO" and the "Log IO" components? Look at the top 5 queries on the 3 sections, and let me know if you find a query that start with "SELECT Statman ...". If you see that, then the Auto Update Statistics process is creating those DTU spikes.

Upvotes: 1

mauridb
mauridb

Reputation: 1569

I would suggest to install the sp_whoisactive script so that you can see what's going on more easily:

http://whoisactive.com/

Upvotes: 0

Related Questions