Select taking long time to execute
This below select query is taking more than 2 days to execute and it is waiting on Cxpacket waits. I couldn't upload the actual plan as the query is still running for 16hrs now. I'm uploading the estimated plan. Can you please assist me what's wrong with this plan. I will upload the actual plan once it is executed..
The table 'Fact.policytransaction' has close to 80million records.
I have also attached the output of sys.dm_os_waiting_tasks.
Thanks for the help in advance.
The CXPACKET waits are at the nodes I would expect, and are all short (around the size of the 4ms quantum). CXPACKET waits are of course perfectly normal in a parallel query, and these look to be just a sign that processing is continuing, but there is a lot of work to do.
The query text is truncated in the supplied files, so I can't inspect the logic too deeply, but it seems probable the query is missing a vital predicate or join condition somewhere that is resulting in an explosion in the row count at some intermediate stage, certainly below node 14 in the execution plan.
I recall from your previous questions that this is generated code (SAP BO), but assuming you are able to capture the query text, it might help to include it in your question, though really that sort of analysis would be going beyond what this site is really for, unless it turns out to be something obvious.
If this were my query, I would take the query text, compare it to the execution plan, and run just the sections below nodes 9 and 14 (from the wait stats) to see if the row counts are indeed much higher than expected. Together with code review, this should help identify where the problem is occurring. I would also be monitoring for hash spills and memory consumption during execution.
As a side note, I would also upgrade this server from R2 SP2 (10.50.4000) to at least R2 SP3 (10.50.6000) and enable TF 4199, just in case there is a fixed product bug involved. I would also likely run the query with OPTION (RECOMPILE) to remove some of the quirks of the generated code (e.g. CASE When 'Ironshore Agency Share' ='Iron-Starr Agency Share').
As I have said before, and notwithstanding possible query errors, upgrading to a more modern version of SQL Server at some stage and looking into columnstore is definitely something to think about.