How to optimize the SQL query which uses cte, group by and run for 23 hours?

Question

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.

Details:

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 sp_WhoIsActive

wait type: (14ms)PAGEIOLATCH_SH:dev-db:1(*) or NULL sometimes. Got this using sp_WhoIsActive.

Note:

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 and table2 is parent of table3

Any advice or suggestions are highly appreciable.

Answer 1

"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." -> you can't expect your query to run fast if your indexes aren't fit for the query and tables are this big (your query scans the whole table).

If I were you, I would take this into consideration:

• move the WHERE pl.issuingdate > '2021-8-1' into the CTE (your CTE could get smaller to work with if you use this condition every time and there's data significant amount of data earlier than 2021-8-1)
• make sure an index exists for every foreign key (column you join the tables on);
• index column order matters with multi-column indexes and non-equality searches; for the query you have posted, I would put on first and second place to the index the foreign key and the columns what you have in WHERE clause (for equality searches, column order is not that important, for inequality searches, it matters more)
  • for example, for table table1, I would create an index with columns in the following order: CREATE INDEX ix_01 ON dbo.table1(pluigid, pmode, pltype ,plstatus) - it contains your foreign key and the WHERE clause columns
• when experimenting with indexes, bear "5-to-5 rule" in mind, that means do not exceed roughly 5 indexes on one table and refrain from having more than 5 columns in 1 index (given the environment we talk about is OLTP)

Please try to create indexes fit for your query, try re-running the query and let us know if this at least moves you in the right direction.

Also, besides execution plan, I would recommend running the query with SET STATISTICS TIME, IO ON and pasting the result from "messages" tab in SSMS to IO statistics parser to see what is the query doing IO wise. Learning about IO pattern would make the tuning efforts bit more effective.