Hi Experts,
Seeking help on a query. The query is used to take a min to get executed. But now, over the weekend I don’t know what happened, this query is taking hours but never gets completed.
Can anyone tell what is happening? Attaching DDL and index scripts and view definition. Tried checking the fragmentation , rebuild the indexes , updated stats with full scan with no recompute. Still the query runs forever.
Further, I broke the stmt into 2 pieces and tried 2 execute in 2 parts. In that case, the qry1 and qry2 runs as expected (very fast).
However, combining the 2 parts into single query(which is my original query we trying to make it work), the query runs forever.
Attaching actual execution plans collected for qry part 1 & qry part 2.
Since the combined query is taking more time, I had collected only the estimated plan. If anyone interested, please take a look.
I don’t see any blocking. Waittype is null, status is running/runnable.
Any help would be appreciated.
Env details :
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Aug 15 2017 10:23:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Attachment: After Unzipping the zip file, you will find below files.
OriginalQry.sql – This is query in question taking more time and never gets finished
Qrypart1.sql – part 1 of the query which runs fast within expected time. Basically, the original query is divided into 2 parts. This is part 1 of the query.
Qrypart2.sql - part 2 of the query which runs fast within expected time. Basically, the original query is divided into 2 parts. This is part 2 of the query.
Table n index DDL.sql – DDL scripts of the tables and indexes involved
View.txt – view definition which is referenced in the above queries
EstimatedPlan.pesession – estimated execution plan of the original query which never gets completed.
qry1.pesession – Actual execution plan of Qrypart1.sql
qry2.pesession - Actual execution plan of Qrypart2.sql
Note: All plans are collected using SQL Sentry Plan Explorer.
Thanks,
Sam
October 28, 2019 at 5:46 pm
This bit looks odd and long-winded:
AND EXISTS (
SELECT 1
FROM (
SELECT PARTY_ID
FROM C_B_ACCOUNT WITH (NOLOCK)
GROUP BY PARTY_ID
HAVING count(1) = 1
) A
WHERE A.PARTY_ID = ACCT.PARTY_ID
)
I would write it like this:
AND EXISTS (SELECT *
FROM C_B_ACCOUNT X WITH(NOLOCK)
WHERE X.PARTY_ID = ACCT.PARTY_ID
GROUP BY X.PARTY_ID
HAVING COUNT(*) = 1)
October 30, 2019 at 11:28 am
Hi Jonathan, Thanks for the reply.
This query used to run fine within a min. don't know why it is taking long time and never finished.no blocking, waitype shows null, status=running... it looks very strange.
for workaround, we told them to create a temp tbl for subsets and run the query runs fast.
Still I m curious why the original query is running forever. If we asked if app team changed anything , the answer is NO.
The first piece returns 91042 row(s) takes 1 sec and 2nd piece returns 2706 row(s) takes few milli secs to complete.
We have to do just a LEFT OUTER JOIN of the 2 subsets. Don't know it executing ... executing .. executing....
I don't know why the query is suddenly taking longer. I just had a quick look at the SQL and saw that bit looked a bit odd. I don't think it has anything to do with the long runtimes of your SQL though.
What is the purpose of the LEFT OUTER JOIN? The query doesn't seem to be using any of the columns produced by it and it only ever selects one row for each row in the rest of the query?!
You might find that the statistics on the tables need updating or some new ones adding as the optimiser might be choosing a sub-optimal execution plan.
I often just try rewriting a query in different ways to see if it will execute faster, for example, the original query, I think, can be rewritten as:
SELECT GETDATE() LAST_RUN,
CASE WHEN party.last_update_date > acct.last_update_date THEN party.last_update_date
ELSE acct.last_update_date
END record_last_updated,
P_XREF.ROWID_SYSTEM XREF_SYSTEM,
P_XREF.PKEY_SRC_OBJECT PKEY_SRC_OBJECT,
party.ROWID_OBJECT PARTY_ROWID_OBJECT
FROM v_party_account_last_updated vw_party
INNER JOIN c_b_party party
ON party.rowid_object = vw_party.party_id
AND party.bo_class_code = 'Organization'
AND (party.ACTV_FLG <> 0 OR party.ACTV_FLG IS NULL)
INNER JOIN C_B_ACCOUNT ACCT
ON ACCT.PARTY_ID = PARTY.ROWID_OBJECT
AND (ACCT.IS_MERGED <> 1 OR ACCT.IS_MERGED IS NULL)
AND EXISTS (SELECT *
FROM C_B_ACCOUNT X
WHERE X.PARTY_ID = ACCT.PARTY_ID
GROUP BY X.PARTY_ID
HAVING COUNT(*) = 1)
INNER JOIN c_b_party_xref P_XREF
ON vw_party.party_id = p_xref.rowid_object
AND P_XREF.ROWID_SYSTEM = 'SURF'
AND PKEY_SRC_OBJECT LIKE 'ACCT%'
OUTER APPLY(SELECT TOP(1)
PARTY_ID,
COMM_TYP_CD,
COMM_VAL
FROM C_B_PARTY_COMM
WHERE COMM_TYP_CD = 'PHONE'
AND COMM_USG = 'PRTNR'
AND HUB_STATE_IND = 1
/* AND PARTY_ID IN (SELECT ROWID_OBJECT
FROM C_B_PARTY
WHERE BO_CLASS_CODE = 'ORGANIZATION') */
AND C_B_PARTY_COMM.PARTY_ID = party.ROWID_OBJECT
ORDER BY LAST_UPDATE_DATE DESC) AS PRTNR_PHONE
PS: I can't test it so might not be correct.
October 30, 2019 at 5:45 pm
Okay. Thanks for taking time. The problem is we can tell them to re-write query but this query was stable since a long time. And changes has to be to multiple places in the code and has to go through release process.
How to find out how many plans has been generated and generation date and usecounts of the plan.May be I will try to delete the new plan and see if sql uses old plan. I m unsure if this is right but just want to give it a try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy