August 7, 2017 at 6:33 am
Hi Experts,
One of the query is taking long time to execute. Its taking more than 6 mins. Can anyone provide guidance on optimize this query and bring down the execution time. Ran the query in plan explorer and attaching the same.
-- Rowcount info
/*
TableName SchemaName RowCounts
DDA_Acct dbo 70224
VDDA_RELATIONSHIP dbo 177474 ---this is a View
Branch_Description dbo 56
CIF_Name dbo 245108
NAICS_Description dbo 2213
CIF_Contact dbo 83429
CIF_Phone dbo 333109
*/
The issue is there are no where /filter condition as we have to take all the data from all tables as per bisuness rule. so, basically the tables will be loaded on daily basis and we need to consider all the rows for processing. That is the reason why we see full table scans / Index Scans all over the execution plan.
So, any recommendations in regards the query rewrite, any index recommendations against the tables which are getting joined? any tips on tsql rewrite ?(case rewrite with joins etc...) or any other way we can reduce the logical reads etc... Right now the query is going in parallel . Any suggestions over MAXDOP setting ?
--This is internally calling one view. Definition is below.
--CREATE VIEW dbo.VDDA_Relationship AS
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
Inq.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
''
END
AS Tax,
CASE
WHEN
Inq.Direct_Indirect_Code = 0
THEN
'Direct'
WHEN
Inq.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
Inq.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
Inq.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
''
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, 3 AS Relationship_Code, 'Owner/Signer' AS Relationship_Code_Desc,
(
Inq.Relationship_Percent * 100
)
AS Relationship_Percent, Inq.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A
LEFT OUTER JOIN
dbo.Stmt_Address_To_DDA SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Inquiry_Name_To_DDA Inq
ON A.DDA_Account = Inq.Account_Number
AND A.Institution_Number = Inq.Institution_Number
INNER JOIN
dbo .CIF_Name CN
ON Inq.Name_ID = CN.Name_ID
AND Inq.Institution_Number = CN.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON Inq.Account_Number = TN.Account_Number
AND Inq.Institution_Number = TN.Institution_Number
AND Inq.Name_ID = TN.Name_ID
LEFT OUTER JOIN
dbo.Owner_Signer_Name_To_DDA Own
ON Inq.Account_Number = Own.Account_Number
AND Inq.Institution_Number = Own.Institution_Number
AND Inq.Name_ID = Own.Name_ID
LEFT OUTER JOIN
dbo.Relationship_Name_to_DDA R
ON Inq.Account_Number = R.Account_Number
AND Inq.Institution_Number = R.Institution_Number
AND Inq.NAME_ID = R.Name_ID
LEFT OUTER JOIN
dbo.Name_to_Port NP
ON A.Portfolio = NP.Portfolio
AND Inq.Name_id = NP.Name_ID
AND Inq.Port_Sequence_Number = NP.Port_Sequence_Number
AND Inq.Institution_Number = NP.Institution_Number
AND NP.Name_Line <> 999
LEFT OUTER JOIN
dbo.Port_Relationship PR
ON NP.Relationship_Code = PR.Relationship_Code
AND NP.Institution_Number = PR.Institution_Number
WHERE
Inq.Name_Line <> 999
AND Own.Name_ID IS NULL
AND R.Name_ID IS NULL
UNION
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
Own.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
''
END
AS Tax,
CASE
WHEN
Own.Direct_Indirect_Code = 0
THEN
''
WHEN
Own.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
Own.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
Own.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
''
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, Own.Relationship_Code AS Relationship_Code, ComRel.External_Value AS Relationship_Code_Desc,
(
Own.Relationship_Percent * 100
)
AS Relationship_Percent, Own.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A LEFT
OUTER JOIN
dbo.Stmt_Address_To_DDA SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Owner_Signer_Name_To_DDA Own
ON A.DDA_Account = Own.Account_Number
AND A.Institution_Number = Own.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON Own.Account_Number = TN.Account_Number
AND Own.Institution_Number = TN.Institution_Number
AND Own.Name_ID = TN.Name_ID
LEFT OUTER JOIN
dbo.Name_to_Port NP
ON A.Portfolio = NP.Portfolio
AND Own.Name_id = NP.Name_ID
AND Own.Port_Sequence_Number = NP.Port_Sequence_Number
AND Own.Institution_Number = NP.Institution_Number
AND NP.Name_Line <> 999
LEFT OUTER JOIN
dbo.Port_Relationship PR
ON NP.Relationship_Code = PR.Relationship_Code
AND NP.Institution_Number = PR.Institution_Number
INNER JOIN
dbo.CIF_Name CN
ON Own.Name_ID = CN.Name_ID
AND Own.Institution_Number = CN.Institution_Number
INNER JOIN
dbo.Common_Relationship ComRel
ON Own.Relationship_Code = ComRel.Relationship_Code
AND Own.Institution_Number = ComRel.Institution_Number
UNION
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
R.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
''
END
AS Tax,
CASE
WHEN
R.Direct_Indirect_Code = 0
THEN
''
WHEN
R.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
R.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
R.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
''
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, R.Relationship_Code, DR.External_Value AS Relationship_Code_Desc,
(
R.Relationship_Percent * 100
)
AS Relationship_Percent, R.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A
LEFT OUTER JOIN
dbo.Stmt_Address_To_DDA as SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Relationship_Name_to_DDA R
ON A.DDA_Account = R.Account_Number
AND A.Institution_Number = R.Institution_Number
INNER JOIN
dbo.CIF_Name CN
ON R.Name_ID = CN.Name_ID
AND R.Institution_Number = CN.Institution_Number
INNER JOIN
dbo.DDA_Relationship DR
ON R.Relationship_Code = DR.Relationship_Code
AND R.Institution_Number = DR.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON R.Account_Number = TN.Account_Number
AND R.Institution_Number = TN.Institution_Number
AND R.Name_ID = TN.Name_ID
WHERE
R.Relationship_Code > 99
UNION
SELECT
A.Institution_Number,
A.Portfolio,
A.DDA_Account,
A.Account_Type,
SA.Port_Sequence_Number AS Address_Line,
A.Branch_Number,
CN.Name,
CN.Name_ID,
R.Port_Sequence_Number AS Name_Line,
CASE
WHEN
TN.Name_ID IS NOT NULL
THEN
'Yes'
ELSE
''
END
AS Tax,
CASE
WHEN
R.Direct_Indirect_Code = 0
THEN
''
WHEN
R.Direct_Indirect_Code = 1
THEN
'Direct'
WHEN
R.Direct_Indirect_Code = 2
THEN
'Indirect'
WHEN
R.Direct_Indirect_Code = 3
THEN
'Secondary'
ELSE
''
END
AS Direct_Indirect_Code, CN.Relationship_Code AS Name_Format_Code, R.Relationship_Code, ComRel.External_Value AS Relationship_Code_Desc,
(
R.Relationship_Percent * 100
)
AS Relationship_Percent, R.CIF_Alt_Name_Key
FROM
dbo.DDA_Acct A
LEFT OUTER JOIN
dbo.Stmt_Address_To_DDA SA
ON A.DDA_Account = SA.Account_Number
AND A.Institution_Number = SA.Institution_Number
INNER JOIN
dbo.Relationship_Name_to_DDA R
ON A.DDA_Account = R.Account_Number
AND A.Institution_Number = R.Institution_Number
INNER JOIN
dbo.CIF_Name CN
ON R.Name_ID = CN.Name_ID
AND R.Institution_Number = CN.Institution_Number
INNER JOIN
dbo.Common_Relationship ComRel
ON R.Relationship_Code = ComRel.Relationship_Code
AND R.Institution_Number = ComRel.Institution_Number
LEFT OUTER JOIN
dbo.Tax_Name_To_DDA TN
ON R.Account_Number = TN.Account_Number
AND R.Institution_Number = TN.Institution_Number
AND R.Name_ID = TN.Name_ID
WHERE
R. Relationship_Code > 3
-- takes 42 secs to the above view
Thanks in advance.
August 7, 2017 at 6:52 am
1) How can we possibly help you without table definitions (including existing indexes and keys)?
2) Have you examined file IO stalls when this is running?
3) Have you examined wait stats while this is running?
4) What is the configuration of the server and especially the IO path? If you truly need all data then two things will help tremendously: RAM and IO (assuming the data is larger than the amount of RAM you can put on the server.
5) Speaking of RAM if it isn't maxed out for your (presumably Standard) Edition of SQL Server then make that happen. This is so important that I have actually TURNED DOWN REQUESTS FOR CONSULTING when a client didn't have enough of it!!! I am EXCEPTIONALLY GOOD at tuning SQL Server and applications that run on it, but I just cannot beat the microsecond-time-scale efficiencies that additional RAM offers.
6) Other things to look for are bad estimates between actual and estimated row counts in the query and mismatched data types.
7) Oh, and are the UNIONs required? If not then replacing them with UNION ALL will remove the SORT/DISTINCT that happens under the covers with them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 7, 2017 at 10:58 am
Uploaded plan explorer generated query plan. IT should have the table diagram n table structure. Appreciate if any inputs from it. Tomorrow I ll try to get the table definitions if possible.
August 7, 2017 at 12:57 pm
a couple of initial comments based on the query plan:
- there are a number of places that the actual number of rows far exceeds the estimated number of rows, how clean / up to date are statistics?
- I notice the vast majority of the joins are resolving as hash match, including many that are producing residual I/O in a table scan, this tells me there may be an opportunity to improve on indexing here.
August 7, 2017 at 1:23 pm
Thanks Chris for taking a look.
Wanted to know what does the term residual I/O mean? Also, is there a problem going with Hash match?
August 7, 2017 at 2:33 pm
residual IO means that the database engine did a lot more reads than necessary, for example:
Tax_Name_To_DDA, which is listed as a HEAP, has 4 full table scans on it. one of those says 70,221 rows read but only 268 rows returned, another says 70,221 rows read but only 9 returned. Here's an article that talks about residuals in execution plans:
https://sqlperformance.com/2016/06/sql-indexes/actual-rows-read-warnings-plan-explorer
Hash match in general is not bad, but if there are residual IO or residual predicates involved, then it means there's more work going on than necessary.
There's also a good book available about understanding execution plans here:
http://www.sqlservercentral.com/articles/books/94937/
August 7, 2017 at 2:48 pm
I also notice that it has a degree of parallelism of 16 which seems high. Guidance from Microsoft suggests this probably shouldn't be more than 8:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
August 7, 2017 at 11:18 pm
Chris. How can I tell that they were 16 threads spawned for parallelism operators in the execution plan?
August 8, 2017 at 12:39 am
Attaching table definitions and index information. "Table Structures.txt"
Please give some pointers on how to go about Indexing for the base query and optimization tips.
August 8, 2017 at 6:17 am
vsamantha35 - Monday, August 7, 2017 11:18 PMChris. How can I tell that they were 16 threads spawned for parallelism operators in the execution plan?
If you hover your mouse pointer over the Select node of the plan diagram (the top left most node), it will show you information about the query as a whole, such as:
Reason for early termination: Time Out
Actual Rows: 177,474
Estimated Rows: 56,591
...
Degree of Parallelism: 16
...
Branches: 26
Used Threads: 176
...
August 8, 2017 at 6:56 am
Thanks Chris.
August 8, 2017 at 7:15 am
1) I still haven't seen anything on the server configuration.
2) There are numerous tables that have nothing but a clustered index. Some of those clustered indexes are pretty fat.
3) There are numerous heap tables. Those have several negatives, including the potential for forwarding pointers and the fact that they can be HUGE with few rows due to the fact that deleted records that lead to empty pages result in the empty pages remaining allocated to the table. I have seen GB-sized SINGLE ROW tables due to this.
4) Given the amount of objects in play and the complexity of the query I am going to state that this problem is one that goes beyond the free forum realm, at least for me. I recommend you get a professional in to help out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply