July 29, 2021 at 11:53 am
The query doesn't have any kind of filtering through a WHERE clause? It's likely that running the query forces SQL SErver to move all the data in question through memory, which will absolutely cause memory pressure clearing out the plan cache. Yeah, you're then going to see lots and lots of recompiles. I'd say you already have enough to show the vendor that the query is problematic. Now, whether they listen or not is a different matter entirely.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2021 at 1:53 pm
No there is no WHERE clause in the statement.
I have tried suggesting this to the vendor before but they dismissed it and said that they query could not be optimised anymore, but now I have noticed this and sent them more information hopefully they will take another look.
My company is looking to me to fix the issue, but without the vendors assistance theres little that I can influence, hence my suggestion of changing the compat level of the DB to 2016 level to hopefully force the query to create a better enough plan to not continually kill my cache.
July 29, 2021 at 2:38 pm
Yeah, compat level might help, but it could also burn things down. They're probably trying to avoid dealing the change to the cardinality estimation engine that occurred in 2014 by leaving it at the old compatibility. Switch it over, but be ready to switch it back, right away, if things look like they're going badly.
But, if you can't filter the query in any way, yeah, it's going to use a ton of memory and that will absolutely affect the plans in cache. No way around it really as long as you can't change the query. The only option is really more hardware.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2021 at 3:58 pm
But, if you can't filter the query in any way, yeah, it's going to use a ton of memory and that will absolutely affect the plans in cache. No way around it really as long as you can't change the query. The only option is really more hardware.
Be aware that more hardware won't eliminate the problem - it may delay the problem long enough to allow the system to get past the issue without causing too many issues, but the buffer cache will still be affected and you will still see an effect.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2021 at 6:52 pm
I think the current query can be tuned up and that it should definitely help performance. Will it help it enough to meet your needs? You'll just have to test it and see.
Note that including the schema names on the table names is not just cosmetic, it can help cut down on recompiles for potential security reasons.
SELECT
list_requests.createdOn,
attributes.attribute_30_textValue,
attributes.attribute_31_textValue,
attributes.attribute_26_textValue,
attributes.attribute_27_textValue,
attributes.attribute_73_textValue,
dictionaries.dictionary_51_description,
attributes.attribute_29_textValue,
list_requests.description,
list_requests.contact_name,
list_requests.contact_location_fullName,
list_requests.requestNumber,
dictionaries.dictionary_54_description,
dictionaries.dictionary_55_description,
list_requests.assignee_name,
list_requests.requestId,
list_requests.requestType_acronym,
list_requests.requestType_baseRequestTypeId,
list_requests.isMajorIncident,
ROW_NUMBER() OVER(ORDER BY dictionary_51.description ASC,list_requests.createdOn ASC) AS rowNumber,
@param0 AS queryUserId
FROM
dbo.list_requests lr
OUTER APPLY (
SELECT
MAX(CASE WHEN rAV.requestAttributeTypeId = 26 THEN rAV.textValue END) AS attribute_26_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 27 THEN rAV.textValue END) AS attribute_27_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 29 THEN rAV.textValue END) AS attribute_29_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 30 THEN rAV.textValue END) AS attribute_30_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 31 THEN rAV.textValue END) AS attribute_31_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 73 THEN rAV.textValue END) AS attribute_73_textValue
FROM dbo.requestAttributeValue rAV
WHERE rAV.requestId = list_requests.requestId AND
rAV.requestAttributeTypeId IN ( 26, 27, 29, 30, 31, 73 )
) AS attributes
/* My best guess is that an OUTER APPLY will perform better here, so I coded that first,
but a LEFT OUTER JOIN could perform better instead. There's no way to be sure which
will actually be better without looking at the query plan and/or testing each method. *//*LEFT OUTER JOIN (
SELECT
rAV.RequestId,
MAX(CASE WHEN rAV.requestAttributeTypeId = 26 THEN rAV.textValue END) AS attribute_26_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 27 THEN rAV.textValue END) AS attribute_27_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 29 THEN rAV.textValue END) AS attribute_29_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 30 THEN rAV.textValue END) AS attribute_30_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 31 THEN rAV.textValue END) AS attribute_31_textValue,
MAX(CASE WHEN rAV.requestAttributeTypeId = 73 THEN rAV.textValue END) AS attribute_73_textValue
FROM dbo.requestAttributeValue rAV
WHERE rAV.requestAttributeTypeId IN ( 26, 27, 29, 30, 31, 73 )
GROUP BY rAV.RequestId
) AS attributes ON attributes.requestId = list_requests.requestId
*/
OUTER APPLY (
SELECT
MAX(CASE WHEN rC.dictionaryId = 51 THEN c.description END) AS dictionary_51_description,
MAX(CASE WHEN rC.dictionaryId = 54 THEN c.description END) AS dictionary_54_description,
MAX(CASE WHEN rC.dictionaryId = 55 THEN c.description END) AS dictionary_55_description
FROM (
SELECT rC.dictionaryId, rC.classificationId
FROM dbo.requestClassification rC
WHERE rC.requestId = list_requests.requestId AND
rC.dictionaryId IN ( 51, 54, 55 )
) AS rC
LEFT OUTER JOIN dbo.classification c ON c.classificationId = rC.classificationId
) AS dictionaries
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 2, 2021 at 10:30 am
Hi,
what is the output from the execution plan. We got a high cpu usage, because there was an explicit type conversion from nvarchar(4000) to char(29).
We were able to fix this, and there was no more cpu pressure. First time we needed about 8h, and after we changed the type we only needed 30min.
Kind regards,
Andreas
August 2, 2021 at 1:27 pm
If you've got QueryStore enabled, you could confirm if you are in fact experiencing regressed plans. From there you can force SQL Server to use a plan that worked better than others, but depending on what parameters are being passed, that might cause other issues as well.
Regardless, forcing a plan likely means that query needs optimizing as others have said. I know arguing with vendors can be a pain, but I would document very carefully everything you've tried and get your leadership onboard with the idea that this isn't something you can fix by throwing more resources at it.
Good luck!
August 2, 2021 at 2:25 pm
It looks like the query got truncated.... the common-table-expression data does get completed (there is no closing bracket and no other T-SQL statement using it), the rest of the query may or may not be important.
What version of MSM are you running? For v14.18, the release notes state that SQL Server versions 2012 / 2012 R2 / 2014 / 2016 are supported. Have you been told to run the database in 2008 compatibility mode?
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply