Performance Issue

Question

Performance Issue

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

DBA_007 - Tuesday, March 12, 2019 8:39 AM
DBA_007 - Tuesday, March 12, 2019 7:33 AM
Grant Fritchey - Tuesday, March 12, 2019 7:25 AM
DBA_007 - Tuesday, March 12, 2019 6:15 AM
please find the query and plan
where?
Here
Anyupdate?

Just so you know, we're volunteers here. It's not a paid service. We'll get at stuff, when and where we can. You might not get consultant level responses.

----------------------------------------------------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 Ten Centuries Points: 1180 More actions · Answer 2

Grant Fritchey - Tuesday, March 12, 2019 8:53 AM
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?

Thanks for the response,please find the attached query and this is the slow running

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Thanks. With that, I'm back to my other recommendations. Your principals waits are on memory, so that's a likely problem. However, pick either DISTINCT or TOP 1, doing both is excess work. I'd drop the DISTINCT.

----------------------------------------------------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 Ten Centuries Points: 1180 More actions · Answer 4

Thanks for the reply, Physical memory is 128Gb out of which 114gb was set as max memory to SQL. We can't remove Distinct or Top1 as the query is inbuilt executing from a Tool

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

DBA_007 - Tuesday, March 12, 2019 9:08 AM
Grant Fritchey - Tuesday, March 12, 2019 8:53 AM
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?
Thanks for the response,please find the attached query and this is the slow running

The real problem here is the whole reason why you're using DISTINCT. You need a little "Divide'n'Conquer" code to isolate the minimum required rows so that you can avoid DISTINCT and TOP 1 and GROUP BY. In other words, someone needs to actually do an analysis on the tables and rewrite the code to avoid the inherent partial Cartesian Products formed by accidental many-to-many joins. Remember that "Set Based" doesn't mean "All in one query".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

DBA_007 - Tuesday, March 12, 2019 11:17 AM
Thanks for the reply, Physical memory is 128Gb out of which 114gb was set as max memory to SQL. We can't remove Distinct or Top1 as the query is inbuilt executing from a Tool

And herein will lie our problems going forward. If we can't modify the code, we can't modify the behavior. There isn't a magic "run faster" switch. There are two core ways to deal with performance issues. First up, throw money at the problem. Buy bigger, better, faster hardware. That usually solves most issues. When the money runs out, or there isn't any, the second choice to fix performance issues is to address the root causes which are always: bad code, bad structures, bad or missing indexes, bad or missing statistics.

If you have the option of throwing money at this, I'd recommend that. Otherwise, you have to do the hard work. From what you've shown us, it's not bad/missing indexes and it's not bad/missing statistics. That leaves structure and code. The structure looks sketchy in my eyes (200+ columns in a single query?) and the code has issues. If you're saying that you can't change the code, then look to the structure. If that can't change either, you're back to throwing money.

These are the harsh realities that we have to deal with.

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