Azure SQL Table taking too long to load for more than 100 thousand records

Question

Azure SQL Table taking too long to load for more than 100 thousand records

Rama Gilaka 41

I am running a simple select query for azure sql table , taking 5 minutes to load,, tried auto tuning, has primary key and clustered Index, please suggest how to fix this?

Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-08-26T22:13:00.613+00:00

Please share the query with us and the execution plan of the query using this web site.
Rama Gilaka 41 Reputation points

2022-08-26T22:19:58.91+00:00

Row Num Table Scan Count Logical Reads Physical Reads Read-Ahead Reads LOB Logical Reads LOB Physical Reads LOB Read-Ahead Reads % Logical Reads of Total Reads
Total 1 133,028 0 0 0 0 0
TableName_001 1 133,028 0 0 0 0 0 100.000

(1 row affected)

CPU Elapsed
SQL Server Execution Times: 00:00:04.953 00:10:18.438
CPU Elapsed
SQL Server parse and compile time: 00:00:00.000 00:00:00.000

CPU Elapsed
SQL Server Execution Times: 00:00:00.000 00:00:00.000

Completion time: 2022-08-26T17:43:47.1631326-06:00

Totals:
Table Scan Count Logical Reads Physical Reads Read-Ahead Reads LOB Logical Reads LOB Physical Reads LOB Read-Ahead Reads % Logical Reads of Total Reads
Total 1 133,028 0 0 0 0 0
TableName_001 1 133,028 0 0 0 0 0 100.000
CPU Elapsed
SQL Server parse and compile time: 00:00:00.000 00:00:00.000
SQL Server Execution Times: 00:00:04.953 00:10:18.438
Total 00:00:04.953 00:10:18.438
Bjoern Peters 8,921 Reputation points

2022-08-27T13:57:16.477+00:00

Hi @Rama Gilaka

that information is nice, but unfortunately, not those Alberto asked for.

We need to see the complete query and the complete execution plan.

Then you can right-click and view that execution plan as XML... this XML please Copy&Paste in this website, then copy the resulting URL here: (this is just my example)
Rama Gilaka 41 Reputation points

2022-08-27T20:03:26.997+00:00

Hi Thank you for detailed info, I tried your way, I get the following error on Brent Ozar, coz this link would throw error for newer version of SQL or Azure SQL DB , I am using Azure DB

So I used http://statisticsparser.com/index.html, and sent above details, Please find the execution screenshot, thank you
Bjoern Peters 8,921 Reputation points

2022-08-27T20:44:11.357+00:00

Maybe you can save the XML-Output as a *.sqlplan file and upload it here.

Accepted answer

0 additional answers

Your answer

Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-08-26T22:13:00.613+00:00

Please share the query with us and the execution plan of the query using this web site.
Rama Gilaka 41 Reputation points

2022-08-26T22:19:58.91+00:00

Row Num Table Scan Count Logical Reads Physical Reads Read-Ahead Reads LOB Logical Reads LOB Physical Reads LOB Read-Ahead Reads % Logical Reads of Total Reads
Total 1 133,028 0 0 0 0 0
TableName_001 1 133,028 0 0 0 0 0 100.000

(1 row affected)

CPU Elapsed
SQL Server Execution Times: 00:00:04.953 00:10:18.438
CPU Elapsed
SQL Server parse and compile time: 00:00:00.000 00:00:00.000

CPU Elapsed
SQL Server Execution Times: 00:00:00.000 00:00:00.000

Completion time: 2022-08-26T17:43:47.1631326-06:00

Totals:
Table Scan Count Logical Reads Physical Reads Read-Ahead Reads LOB Logical Reads LOB Physical Reads LOB Read-Ahead Reads % Logical Reads of Total Reads
Total 1 133,028 0 0 0 0 0
TableName_001 1 133,028 0 0 0 0 0 100.000
CPU Elapsed
SQL Server parse and compile time: 00:00:00.000 00:00:00.000
SQL Server Execution Times: 00:00:04.953 00:10:18.438
Total 00:00:04.953 00:10:18.438
Bjoern Peters 8,921 Reputation points

2022-08-27T13:57:16.477+00:00

Hi @Rama Gilaka

that information is nice, but unfortunately, not those Alberto asked for.

We need to see the complete query and the complete execution plan.

Then you can right-click and view that execution plan as XML... this XML please Copy&Paste in this website, then copy the resulting URL here: (this is just my example)
Rama Gilaka 41 Reputation points

2022-08-27T20:03:26.997+00:00

Hi Thank you for detailed info, I tried your way, I get the following error on Brent Ozar, coz this link would throw error for newer version of SQL or Azure SQL DB , I am using Azure DB

So I used http://statisticsparser.com/index.html, and sent above details, Please find the execution screenshot, thank you
Bjoern Peters 8,921 Reputation points

2022-08-27T20:44:11.357+00:00

Maybe you can save the XML-Output as a *.sqlplan file and upload it here.

Answer 1

Bjoern Peters 8,921

From what I know and from my understanding, you are really running a simple query on a very large table on a very small Azure SQL DB.

Until now, you haven't told us what kind and size of Azure SQL database you are running...

From what I can see in the execution plan, it looks like you are running an Azure SQL DB with only two vCores (according to EstimatedAvailableDegreeOfParallelism) which means that your database has a maximum of 6GB RAM! But according to your ExecPlan, your query would like to have, to run optimally, at least 35GB RAM (optimally => without spilling data to the drive)...

So your query reads data from your database, stores it on disk, load another part of the data, and saves it on a disk.... over and over again... if the process finishes, it will be displayed to you from the disk...

Your ExecPlan shows an AvgRowSize="55171" (Bytes) and an EstimateRows="683321" => 683321*55171= 37699502891 Bytes = ~38GB

That's why your query runs that slow ;-)

Rama Gilaka 41 Reputation points

2022-08-27T23:17:20.587+00:00

Thank you for the detailed reply, this query is on staging database, but with same configuration on DEV Database, takes 2 mins,
The Azure DB plan is Standard S2,50 DTUS this is the only table has so many records, rest are small.
What would be the best configuration on Azure database in future for such larger tables, please suggest, thank you
Bjoern Peters 8,921 Reputation points

2022-08-28T02:17:53.9+00:00

Normally, you have to check against the whole workload your app produces on that database and not just a single query (which hopefully should not run like this in production)

You should check which columns and data is really necessary for your application in that given moment, in order to reduce the amount of data and maybe using another index…

If I should suggest a database size and the actual data, you’ll need a size which fits that query, so something bigger than 38GB… the first one is the General Purpose DB with 8vCPUs…
Rama Gilaka 41 Reputation points

2022-08-29T15:04:11.367+00:00

Thank you so much for the replies and it really helps

Share via

Azure SQL Table taking too long to load for more than 100 thousand records

0 additional answers

Your answer