The SQL Server upgrade went perfectly. Then everything got slower.

The upgrade weekend went to plan. Database engine to a current release on Saturday morning, application back up by 14:00, smoke tests green, everyone home before dinner. On Monday at 08:40 the shared service centre that grew by acquisition opened a vendor workspace and waited eleven seconds for it. It used to take under two. A search that returned in four seconds on Friday now takes forty. Nothing failed. Nothing was logged. The database administrator's dashboard was healthier than it had been all year: processor time under 40 percent, read latency at 3 ms, buffer cache hit ratio at 99 percent.

Everything on that dashboard was true. The server was faster and the application was slower, because OpenText Content Management wants a query optimizer that Microsoft replaced in 2014, and the upgrade had just taken it away. There are two different ways to ask for the old one back, the right one depends on your Content Management version, and an upgrade of either product will cheerfully leave you with neither.

Why a query gets slower when nothing is broken

Before SQL Server runs a query it guesses. For each step it estimates how many rows will come back, and it picks a plan around that estimate: which index to use, whether to seek or scan, which join method, how much memory to reserve. Guess ten rows when the answer is a hundred thousand and the plan is still correct. It just does a very large amount of work to return exactly the same result.

Microsoft rewrote that estimator in SQL Server 2014. For most applications it was an improvement. For the query shapes Content Management issues it was not, consistently enough that Microsoft added a switch to bring the old behaviour back, and OpenText documents turning it on as the supported configuration rather than as a workaround.

Two settings, and which one is yours

The dividing line is Content Management 22.4. It is worth writing down, because it is the whole answer.

Content Management	What OpenText asks for	Compatibility level
22.4 and older	`COMPATIBILITY_LEVEL = 110`, the SQL Server 2012 level	Held down at 110
23.1 and newer	`LEGACY_CARDINALITY_ESTIMATION = ON`	Left at the highest available

For the older versions:

ALTER DATABASE <dbname> SET COMPATIBILITY_LEVEL = 110;

Update statistics on the database afterwards. Changing the compatibility level changes how plans are built, and the statistics the old estimator will work from should be current when it starts.

For 23.1 and newer, the compatibility level goes back to the latest your SQL Server supports, and one scoped setting does the work:

USE <dbname>;
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

There is a third thing in circulation, and it is the one that turns an upgrade into a mystery. Instead of the compatibility level, some installations were set up years ago with trace flag 9481, which does the same job for every database on the instance rather than for yours. If you are moving past 22.4, those trace flags have to come off and the scoped setting has to go on. Do one without the other and the system is slower on Monday than it was on Friday, with a successful upgrade log to show for it. It is the same shape as an agent that is enabled but never loaded: two settings that have to agree, no complaint from either when they do not.

What to run before the argument starts

Two statements, thirty seconds, in each environment you care about. Run them in the Content Management database.

SELECT name, compatibility_level
FROM   sys.databases
WHERE  name = DB_NAME();

SELECT name, value
FROM   sys.database_scoped_configurations
WHERE  name = 'LEGACY_CARDINALITY_ESTIMATION';

Write down both answers for production, acceptance and test, with the date. That single line of evidence is what turns "the system feels slow" into a specific claim that a specific person can act on, and it is what tells you afterwards whether the upgrade changed anything.

While you are there, one consequence worth reading twice. Since SQL Server 2016 the optimizer updates statistics more aggressively on large tables by default, but only when the database is at compatibility level 130 or higher. Hold a Content Management 22.4 database at 110 for the optimizer, as OpenText asks, and you have opted out of that behaviour at the same time. On a repository ingesting documents all day, that makes a statistics maintenance schedule somebody's actual job rather than something the engine handles quietly.

When this is the answer, and when it is not

Symptom	What is actually wrong	What to do
Everything slower after a database engine upgrade, no errors anywhere	The new estimator is now in use	Check both settings above, apply the one for your version
Slower after upgrading Content Management past 22.4	Trace flags removed or compatibility level raised, scoped setting never switched on	Trace flags off, `LEGACY_CARDINALITY_ESTIMATION = ON`, compatibility level back to current
Slower after a restore onto a new server	A restore keeps the old compatibility level, and somebody tidied it up	Confirm the version boundary before raising anything
One report or one search slow, everything else normal	Not this. Statistics, fragmentation or an index	Different conversation, do not touch these settings

That last row matters as much as the other three. This is a whole-system symptom. If one screen is slow and the rest of the day is fine, changing the optimizer settings will not help you and will make the next investigation harder.

Four more settings, while you have the DBA's attention

The same OpenText guidance names several other things that are set once and then never looked at again. These are the ones that come up most often in practice.

Cost threshold for parallelism. SQL Server ships with 5, which means it starts splitting queries across processors almost immediately. Content Management mostly issues small, short queries where the coordination costs more than it saves. OpenText's own performance lab landed on 50 as a reasonable starting point.
MaxDOP, the limit on how many processors a single query may use. The default is unlimited. The guidance is the number of physical cores, capped at 8, and on servers with separate memory nodes, the cores in one node rather than all of them.
Antivirus exclusions. The database files, the log files and tempDB out of scanning, and the SQL Server engine process out of active monitoring. This is a different subject from scanning the documents themselves, and it gets missed because the two conversations happen with different people.
Recovery model. Content Management creates its database in SIMPLE, which does not keep transaction logs. OpenText's recommendation is FULL, and FULL only works if somebody owns log backups. Until they do, your recovery point is the last full backup, whatever the recovery plan says.

Collation deserves its own sentence, because it is the one you cannot fix on a Wednesday afternoon. OpenText asks for a case-sensitive, accent-sensitive collation that matches across the server, tempDB and the Content Management database, partly for compatibility with Archive Server. Every documented route to correcting a mismatch afterwards requires an outage. Get it right at installation or plan a weekend.

What this means if you own the process

The cost of this one is not the fix. The fix is a single statement and a service window. The cost is the six weeks before it, during which the vendor, the hosting party and your own infrastructure team each demonstrate that their layer is healthy, because each of them is right. Nothing is broken. The system has simply become expensive to use, and there is no ticket in any queue that names the cause.

The decision it changes is what goes into the upgrade runbook. "Does the application start" is not a post-upgrade check. "What is the compatibility level and is legacy cardinality estimation on, before and after" is one, and it takes half a minute.

The question to put to your supplier, in their words: after this upgrade, what will the compatibility level of the Content Management database be, and will LEGACY_CARDINALITY_ESTIMATION be on? Show me the output of both, before and after the window. If the answer arrives without the version boundary in it, ask which side of 22.4 they think you are on.

The optimizer never announces that it changed its mind. It returns exactly the same rows, correctly, and takes eleven seconds to do it.
— a Monday morning that turned into six weeks

What I do differently now

I ask for the two query outputs at the start of an upgrade discussion, not at the end of a performance investigation. I put them in the runbook as a step with a signature next to it, in the same list as the smoke tests. I ask which Content Management version boundary the DBA is working to, because the answer tells me in one sentence whether they have read the current guidance or are repeating what was true in 2019. And I no longer accept a green dashboard as evidence of anything, because on that Monday morning every number on it was accurate and every one of them was beside the point.