question

Modestas-2045 avatar image
0 Votes"
Modestas-2045 asked NavtejSaini-MSFT commented

Azure SQL Database: Dead simple queries taking long time to execute

Hey there.

After using SQL Database for quiet a while I notices that it's randomly misbehaving.
Current plan I got is Premium P1: 125 DTUs, which should be more than enough for my loads.

There are moments when queries like this:

select Id, Name, Surname, Age
from MyPeople
where Id = 'someId'

is taking more than a minute. The Id is primary key and index exists.
To make this harder to solve - this is random and out of ~100 queries one might take long time, while others take less than a second.
Checking Query Performance Insights I can not see that query or other long running queries at all (this make me believe that the tool can not be trusted for performance insights since it shows random data). The resource usage during the "interesting" moments are negligible, meaning that CPU, RAM and IO is bellow 10% of total capacity.
Query Performance Recommendations also show no suggestion as well.

Are there any other ways to get some insight what might be going on with database itself?

azure-sql-database
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Modestas-2045

Please go through this document for getting recommendation for Azure SQL DB. Once you have gone through the same, please ask your questions and we can help regarding the same.

Regards
Navtej S


0 Votes 0 ·

@Modestas-2045

Were you able to check the document and the recommendations.

Regards
Navtej S

0 Votes 0 ·

Yeah. This did not really answer the question why the long running queries (real ones) don't show up in Query Performance Insights. As for the performance issues, if we can call those "performance issues" it is still a bit unclear. I will try using the dedicated resource, where I get more control over the resources. Then this will probably help out to nail down the question whether the issues is because of some misbehaving query or some azure configuration.

0 Votes 0 ·

1 Answer

NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered NavtejSaini-MSFT commented

@Modestas-2045

Have you looked at using the Intelligent Insights for getting more details about your Database. Also as checking this performance issue will need access to your Azure Resources, We recommend raising issue with Azure Support. Please share the ticket number and we can get more traction regarding the same.

Regards
Navtej S


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Modestas-2045 Please let us know if you need any further help or information, Let us know if you were able to raise issue with support.

0 Votes 0 ·