How to determine DTU cost of query

Question

Trying to troubleshoot an Azure web app that seems to timeout while hitting a query against an Azure SQL db.

I am trying to determine the difference in performance between a query executed from the app and the same query ran from SSMS. From the app, we are getting timeouts occasionally, but from SSMS the query returns in just a few seconds.

Where in the Azure portal can I see the query cost in terms of DTU for an individual query that just executed? I need to see that if indeed the queries are being executed the same way (app vs ssms).

Answer 1

The issue may not be related to DTU at all. This thing with Slow in the Application, Fast in SSMS is a theme that has come up more than once through the years. And often enough so that it inspired me to write an article on my web site with exactly that title: Slow in the Application, Fast in SSMS?

Answer 2

You cannot see the amount of DTU used by one query anywhere in the portal.

However, you can see the currently running queries in your database. This will give you insights in what query is executed by the app.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Answer 3

Hi bitshift Thank you for reaching out.

My understanding is that you are trying to determine the difference in performance between a query executed from the app and the same query ran from SSMS.

You can check the SET options between SSMS & the app since many of them are plan affecting. As a result, you will get different plans for the same query, and this can be the performance difference. DBCC USEROPTIONS is one way to check the SET options. Also, an event / profiler trace with statement completed and execution plan can reveal the differences.

Hope that helps.

Regards,

Oury

Answer 4

I would be interested to see the query plan generated by both executions. Are there any parameters in the query? Are you hardcoding them while executing from SSMS? Use Query Store to identify the query plan as I am sure there would be a difference.