why am I unable to get display of 3,00,000 records in azure sql database with price-tire-50 /100 DTUs?

ArunRaaman 991

Hello There,

I recently started using Azure SQL Database as part of my Azure DE learning purpose.

I tried to run the query 'select (*) from' over a table that has around 3,00,000 records -- to just fetch the records ; tried this in Query-editor within Azure SQL DATABASE;

the price-tire I set is with 100 DTUs for Azure SQL SERVER /sql database; I waited for over 1200 seconds; the query is running forever without giving result; I had to force stop the query execution.

Would anyone help me understand the scenario?

Please find the attached screenshots for analysis purpose.

User's image

Accepted answer

Bruce (SqlWork.com) 56,931 Reputation points

2023-02-05T23:35:31.0266667+00:00

(extra post to display previous)
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

2 additional answers

Alberto Morillo 32,896 Reputation points MVP

2023-02-03T21:44:20.3966667+00:00

An S3 DTU database, the last time I used that service tier it had 2 cores but max degree parallelism was one (1). All queries are serialized. All those 3 million rows will be brought into memory and read with one vcore only.

Another thing about S3 it allows to use only 8 GB of RAM.

If you would like to have better performance with S3 and one vcore, try to make sure queries against that table are supported by an index.
Please sign in to rate this answer.
ArunRaaman 991 Reputation points

2023-02-09T00:55:40.1333333+00:00

Alberto, thank you for your elaboration and the tip... and also my apologies for late reply; I was in travelling for the last 5 days across overseas. As you mentioned in your tip, I tried up to S12 (3000 DTU) where the query started giving results from S7 -DTU model; with all these DTUs from S7, the query is giving results somewhere within 12 to 20 minutes... this was my experience.

I feel like the Query Editor is suffering from overload of query-output (of 3 hundred and more number of records). I have to give a try using Azure-Data Studio and also a pipeline to test the overall performance needs.

I also tried vCore --Standard-series (Gen 5) -- here the problem is the cost of running even for a minimal period from 30 minutes to 1 hour, is costing me around 20 dollars or more.

now, what concerns me is how it works in real time world, especially when the performance with respect to time is a key concern ?

Thank you again for your tip!

Below is the screenshot of cost-analysis in Azure-account:

ArunRaaman 991 Reputation points

2023-02-09T04:02:56.43+00:00

Alberto, my latest observation is 'while access azure sql db with azure data studio, am able to query the table with more data even with S2 -- DTUs model'; that is a good sign... further need to try performing any joins and calculations between tables and table columns
Sign in to comment
Bruce (SqlWork.com) 56,931 Reputation points

2023-02-05T23:34:45.1433333+00:00

you are overloading the query editor as all the rows must be read into memory in the web application, then packaged as a json response. also the browser can not handle this many rows

try using azure data studio instead.
Please sign in to rate this answer.
ArunRaaman 991 Reputation points

2023-02-09T00:58:11.7033333+00:00

Bruce, Thank you for your thoughts on the concerned I raised; after a few attempts, I understood that the query editor is suffering from overload; I haven't yet tried with 'data studio'. Will share you my experience very soon.

Thank you again!

ArunRaaman 991 Reputation points

2023-02-09T04:04:10.37+00:00

Bruce, my latest observation is 'while access azure sql db with azure data studio, am able to query the table with more data even with S2 -- DTUs model'; that is a good thing to know... further need to try performing any joins and calculations between tables and table columns... Thanks for the tip!
Sign in to comment