SQL Server 2022 (Compatibility Level 160): GetSchemaTable() returns null

I had a very similar issue at the beginning of this year, so I would like to share some notes from my troubleshooting. Hopefully this helps narrow down the root cause and saves you some time. In my case, it turned out to be related to SQL Server 2022 Query Optimizer / Parameter Sensitive Plan.

One of the strongest clues in your case is that this already helps:

OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'))

That is important because it keeps the database at compatibility level 160, but makes the query optimizer behave more like compatibility level 150 for that query. Since the issue disappears with this hint, it strongly suggests that the problem is somewhere in the optimizer behavior introduced or enabled with compatibility level 160.

Starting with compatibility level 160, SQL Server 2022 enables Parameter Sensitive Plan Optimization (PSP) by default: 

https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver17 

There is also a very relevant SQL Server 2022 fix in CU4. Bug reference 2306669 is described by Microsoft as:

Fixes an issue where parameter sensitive plan (PSP) optimization produces a dispatcher expression but fails to create a query variant when an application attempts to use the SET FMTONLY ON T-SQL statement to return only metadata.

https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2022/cumulativeupdate4 

What is also worth noting is that CU4 was released back in 2023. That may sound old, but in my case it was still directly relevant because the customer was running SQL Server 2022 and had never installed any cumulative updates after the original installation.

Installing the CU was ultimately what fixed the issue for me.

The problem first appeared on a freshly built DEV environment, so I had the advantage of being able to test different options, change compatibility settings, and install updates without much operational impact.

Anyway, this seems also relevant because ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo) can use the metadata/browse path involving SET FMTONLY ON. And Microsoft also treats SET FMTONLY as a legacy mechanism and recommends newer metadata discovery methods such as sp_describe_first_result_set:

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-ver17 

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql?view=sql-server-ver17 

What I would try:

1. Check the exact SQL Server 2022 build

SELECT 
@@VERSION, SERVERPROPERTY('ProductVersion') AS ProductVersion, 
SERVERPROPERTY('ProductLevel') AS ProductLevel, 
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel;

SQL Server 2022 build list:

https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2022/build-versions 

https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates  

If the instance is still close to the original SQL Server 2022 RTM build, or generally has not been receiving CUs, I would definitely put updating it high on the list. On a production system, of course, upgrading SQL Server may not be something you can do immediately. It can require planning, testing, approvals, and a maintenance window.

2. Before starting a server upgrade, you can quickly test whether PSP is the trigger

Since QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150 already helps, I would also try the more targeted PSP-specific hint:

OPTION (USE HINT('DISABLE_PARAMETER_SENSITIVE_PLAN'))

Documentation:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver17 

If GetSchemaTable() starts returning the expected metadata with this hint as well, that would narrow the issue down even further and provide a strong indication that PSP is the problematic part of the compatibility-level-160 optimizer path.

I would treat this primarily as a diagnostic test or a targeted workaround, not automatically as the final solution. PSP is an intentional SQL Server 2022 optimization feature designed to improve queries where different parameter values require different execution plans. The workload may actually benefit from it, so disabling it without testing can introduce a performance regression.

3. If disabling PSP works for the individual query, then it is worth testing at database scope

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Documentation:

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver17 

This can help confirm whether PSP is really the root cause across the affected workload. I would still be careful with leaving it disabled at database level permanently, because that affects all eligible queries in the database. It is worth validating the performance impact before treating this as a production fix.

4. The other workarounds you already found also fit this theory

OPTION (RECOMPILE)
OPTION (OPTIMIZE FOR UNKNOWN)

and even changing the predicate with a CAST.

All of these can change the optimizer's handling of parameterized queries and may avoid the problematic execution/compilation path.

So based on what I saw in my case, I would strongly suspect PSP / Query Optimizer behavior first.

The fact that QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150 already resolves the issue, combined with Microsoft's documented CU4 fix for:

PSP + SET FMTONLY ON + metadata-only query processing

makes this a very strong direction. If the issue still reproduces on a current SQL Server 2022 CU, I would then consider reporting it to Microsoft as a possible regression or another variant of bug 2306669.

I cannot guarantee that the exact same fix will work in your environment, but hopefully these troubleshooting notes at least get you closer to the end of the investigation and help explain what is actually causing the behavior.