Seems crazy but it's gotten to the point for a whole lot of folks where they need a "conductor" for the "symphony of automation" they've constructed to make sure that...

p.s.  If there's a DISTINCT or a GROUP BY that you're using like a DISTINCT, you really need to fix that.  Really.  And identifying the "core" data and using "Divide'n'Conquer"...

You say "On some occasions, again all under the same execution plan, poor performance occurs " but the execution plans you posted aren't even close to being the same.

The culprit...

AND, looking a bit closer, my code doesn't work correctly using the following data but the mod to pietlinden's code does, so use his code with the mod.

Here's the code...

Looking a bit closer at the code, there' may be a fly in the ointment of pietlinden's  code.  Give the following a run and see.

[EDIT] I DISCOVERED THE CODE...

Hi Phil,

Thanks for the reply.

The create/ insert would be as follows:

CREATE TABLE CustomerPolicies
(
[Mobile] NVARCHAR(20),
[WorkTelephone] NVARCHAR(20),
[HomeTelephone] NVARCHAR(20),
[FirstName] NVARCHAR(30),
[LastName] NVARCHAR(30),
[PolicyNumber] INT
)

INSERT INTO CustomerPolicies VALUES ('07972234564', 'N/A', 'N/A', 'Jon',...

Not to mention being the prime driver of this whole site!  +1 Billion to Steve!

@rjjh78,

Did any of those solutions help you or is there something else that you need help with on this problem?

BWAAAA-HAAAA-HAAAA!!!  I've found that "Penalty of Death" will sometimes work! 😀

I agree with Ant-Green's statement.  All of our data is encrypted and that means everything and not just SQL Server...

@jeff - while concat_ws deals well with nulls on this case it could also cause a collision on a case where one row has null vs one with ''...

P.S. I will also be presenting on the topic of Module Signing at the upcoming PASS Data Community Summit 2021: Module Signing: Use Certificates to...

Interesting.  Thank you for taking the time to post the feedback.

Out of curiosity, when is the last time the SQL Server Service was restarted?

Steve,

I'm pretty sure that most folks will recommend against the use of the Lightweight Pooling option for most things.  The reason I mentioned it was to make sure it wasn't...

I agree with Brian.  The code you posted doesn't even contain the "product_month" column and so I also wonder if you have the correct code posted or if you've just...

It could be a settings difference between VS  and SSIS.  For example, VS could be using connections that use one type of setting for ANSI NULLs and SSIS could be...