Major performance issues after SP32 upgrade

Rick Root

We upgraded from SP30 to SP32 about a month ago, and while most things have gone well (no major issues with functionality or anything), we've had significant performance degradation since the upgrade. It's been particularly noticeable with activating marketing efforts, calculating marketing efforts, and saving/refreshing ad hoc query selections / segment selections.

One thing I've noticed that we never saw before is the prevalence of xp_userlock queries when we are having the most noticeable performance issues.

I have a report that I built that sends me a list of any slow segment refreshes from the previous day,. for example, we have a segment in many of our marketing efforts called “ALL FYXX Individuals with Primary Email Address Seelction”. This is a very simple query which only joins the constituent node to a custom node where there is a boolean field called “Has Mailable Primary Email ADdress”. This selection gets refreshed quite often because it exists in a lot of marketing efforts. It was refreshed 3 times yesterday averaging about 9 minutes each time.

I can run that query in ad hoc query in about 60 seconds or faster..

This has been a continuing problem since the upgrade and we're going into our giving day in about a week and the slow marketing effort activation is going to be a problem.

Jennifer Borri

@Rick Root Oh no…what version of SQL Server are you on? This feels very similar to past upgrades of SQL Server when the cardinality version # had to be rolled back.

Rick Root

@Jennifer Borri Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64)
Jan 22 2023 17:38:22
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Christina Reynolds

@Rick Root I'm so sorry to hear this! I'd recommend contacting Customer Support (if you haven't already). I am also passing this info on to your CSM to further look into it.