Performance Issue
-
Hello All,
I have a select query which is taking 3mins to get the results. I have verified the below.1)Fragmentation and Statistics were up to date.
2)There were no missing indexes
3)Verified the execution plan which shows seek operators with cost as 1% and there are multiple nested loops with 0% cost and one nested loop has 24% cost and there was a bookmark lookup it has the cost of 1%please provide your inputs.
-
There's not much to say, since you haven't
a) included your query
b) included the actual execution plan.It would also help to include table definitions and any views/functions that were used.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
The query is like below
select application1,application2............... from table1 inner join table2, inner join table3,........, inner join table n where psteid='10000004'
-
Iam getting the results with in 38sec upto table n-1,if I include tablen then it takes 3mins for the output.
eventhough the structure of tablen-1 and table n are same - DBA_007 - Tuesday, March 12, 2019 5:17 AM
Have you looked at the execution plan?
- Jonathan AC Roberts - Tuesday, March 12, 2019 5:52 AM
yes, actual execution plan shows seek operators with cost as 1% and there are multiple nested loops with 0% cost and one nested loop has 24% cost and there was a bookmark lookup it has the cost of 1%. below is the part of execution plan.
-
You need an index on Table42 on column application342 with an INCLUDE of application343 and application344.
If that sounds arbitrary, it is. We can't tell you what's wrong with a query that we can't see and can't make suggestions about possible solutions to issues on execution plans that we can't see. We have nothing but guesses from here and they'll be very bad guesses.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
- DBA_007 - Tuesday, March 12, 2019 6:02 AMJonathan AC Roberts - Tuesday, March 12, 2019 5:52 AM
yes, actual execution plan shows seek operators with cost as 1% and there are multiple nested loops with 0% cost and one nested loop has 24% cost and there was a bookmark lookup it has the cost of 1%. below is the part of execution plan.
Can you post the full execution plan? If necessary, get a copy of SentryOne Plan Explorer which can anonymize the plan, hiding any values that might be subject to compliance issues or privacy. Get us the XML, not a picture. The real information in any execution plan is in the details of the properties of the operators.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
please find the query and plan
-
any update on the result
- DBA_007 - Tuesday, March 12, 2019 6:50 AM
One relatively straight forward way to fix the problem is to insert the data into a temporary table from the query without the join to table_n (this takes 38 seconds). Then add an appropriate index (if it will help) on the temporary table, then do a join of the temporary table to table_n.
- DBA_007 - Tuesday, March 12, 2019 6:15 AM
where?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
- Grant Fritchey - Tuesday, March 12, 2019 7:25 AM
Here
- DBA_007 - Tuesday, March 12, 2019 7:33 AM
Anyupdate?
-
Why do you need DISTINCT when you have TOP 1? TOP 1 is going to ensure only one row gets returned. Eliminating the DISTINCT aggregation could help. I can't see the query because it's truncated in the plan. There might be more to catch there. The waits are all on memory allocation, so you might be under memory pressure overall. Certainly if you add another table, more memory will be involved. The one table with all cost, at 23%, looks like statistics are off a little. The optimizer thinks it's accessing 18,814 rows but only moves 1,575, so that cost estimate is inaccurate. You're getting two aggregations, so is there sub-select with an aggregation or is it just the DISTINCT and a GROUP BY that I can't see. Are there other hints? I ask because the loops join & seek to retrieve 18k rows seems excessive for the 1.3m cardinality of the index in question.
Oh, and the output list on the key lookup is 200+ columns, so you can't eliminate that with a covering index anyway. Since it's only a single row, I wouldn't sweat that as the point of the issue.
Now, is this the slow query or the fast query?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply