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 sp_WhoIsActive

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


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.

  • 1
    Can you get the execution plan from SELECT * FROM sys.dm_exec_query_statistics_xml(session_id_of_long_running_session)? This will show the progress it has made in the 20 hours it has been running Commented Aug 2, 2023 at 10:54
  • @MartinSmith It just got completed, took 23hrs. I know the session id, is there a way to get this details for the finished query? Commented Aug 2, 2023 at 11:01
  • Not with the same level of detail. Query store will have some details about resource usage and aggregated waits but it won't have the same "per operator" stats. But definitely worth looking in QueryStore for the details of that query too Commented Aug 2, 2023 at 11:04
  • @MartinSmith How to get details from QueryStore? Sorry for newbie questions. Commented Aug 2, 2023 at 11:10
  • 1
    @Pythoncoder I'm not really familiar with Python but I agree that SO seems to indicate it's possible, and those people are trustworthy sources of information. Ok, good to know. If I rewrite it using temp tables, I'll let you know, and you can test it. If you run into any issues, I'll try to help overcome them.
    – J.D.
    Commented Aug 2, 2023 at 21:26

1 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.

  • Sure, I will try to create indexes as recommended and make changes in query. As this query is running for more than 23hrs now, is it possible to get statistics from running query? Commented Aug 2, 2023 at 10:10
  • You can try "live execution plan in SSMS", but this is how the query is executing execution plan-wise. I would first try to precise the indexes, then try to re-run the query with STATISTICS IO ... I expect the run-time to be way less ; In my original post, I don't explicitly mention the column issuingdate. In case the considerable amount of records is older than your search parameter, I would make sure I have an index on this column as well. Preferably as first col. of the index in case this WHERE significantly narrows down the amount of data returned
    – Sranda
    Commented Aug 2, 2023 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.