I have a query which first performs joins inside
cte and then based on cte join with other table and perform group by with some aggregations and get the top 15 records.
This query is taking 23 hours to run on Azure SQL Database.
Azure database pricing tier: S6 with 750GB storage with 20% unused storage space left
TableName rows TotalSpaceGB UsedSpaceGB UnusedSpaceGB table2 332,318,173 117.72 117.71 0.01 table3 153,700,352 60.78 60.76 0.01 table1 15,339,815 13.21 13.20 0.01 abctable 1,232,868 0.81 0.80 0.00
Actual Execution plan with query
Actual Execution plan without query using
NULL sometimes. Got this using
Actual execution plan was taken from the running query as shown in this SO answer
This tables contains clustered index on primary keys and nonclustered index on other fields which I am not using in filters or joins or order by.
table1 is parent of
table2 is parent of
Any advice or suggestions are highly appreciable.