[FEATURE] Distinguish between severity of Warnings

[pgfiore]

Which component(s) does this affect?

• Desktop App
• CLI Tool
• SSMS Extension
• Plan Analysis Rules
• Documentation

Problem Statement

Since English isn’t my native language, I'll start with a use case to make my life easier.

A query in SSMS (say, the latest 22) doesn't show any specific message, but in Performance Studio (PS) it shows the warning below.

I can only find one a.b.normal message; a table scan shows the predicate: dbo.IB03_GARANZ.UNIV_SEZIONI as IB03.UNIV_SEZIONI=CONVERT_IMPLICIT(numeric(18,0),[@0],0) (and @0 is the forced parametrization at work). And in SSMS, there’s no yellow exclamation mark to highlight warnings.

I have two doubts about PS behavior:

1. This is not a SARGable issue. The IB03.UNIV_SEZIONI is numeric(), which has a highest precedence on int. And the column is searchable (*).

2. I like PS showing “hidden” issues, but I’d like to have a way to differentiate between SQL Server warnings and PS help.

The plan is enclosed. Thank you for being so helpful.

(*) I tried the other way around and, in fact, "cast(IB03.UNIV_SEZIONI as varchar) = 5004001" correctly fires a warning in SSMS too.

Proposed Solution

Fix warnings severity
And differentiate between SQL Server warnings and PS "help"

Use Case

drop table if exists #IB03_GARANZ
create table #IB03_GARANZ (UNIV_PK numeric(18,0) primary key, UNIV_SEZIONI numeric(18,0))
select * from #IB03_GARANZ IB03 where UNIV_SEZIONI=1

Alternatives Considered

No response

[erikdarlingdata]

You're right on point 1, and it's a bug in the tool, not a subtlety you're missing. Your English is fine — the SQL reasoning is better than fine.

Why the warning is wrong. Data type precedence decides which side SQL Server converts, and it converts the lower-precedence side. numeric(18,0) outranks int, so comparing your column to an integer converts the parameter up to the column's type, which is exactly what the plan says:

[dbo].[IB03_GARANZ].[UNIV_SEZIONI] as [IB03].[UNIV_SEZIONI]=CONVERT_IMPLICIT(numeric(18,0),[@0],0)

The conversion is wrapped around [@0], not around the column. The column is untouched and still seekable. The shape that does cost you a seek is the mirror image — CONVERT_IMPLICIT(nvarchar(40),[dbo].[Users].[DisplayName],0)=[@d] — where every row has to be converted before it can be compared. Rule 12 was only checking whether the string CONVERT_IMPLICIT appeared anywhere in the predicate and never looked at which side it was on. That's the whole bug.

I checked rather than assumed, because seekability was the entire question. I built your repro on SQL Server 2025 under PARAMETERIZATION FORCED and captured two actual plans:

• No index on UNIV_SEZIONI — clustered index scan, predicate exactly as above.
• Add an index on UNIV_SEZIONI — Index Seek, and the seek predicate is CONVERT_IMPLICIT(numeric(18,0),[@0],0).

SQL Server seeks straight through the conversion. It also raises no PlanAffectingConvert warning on either plan, which is precisely why SSMS shows you no yellow icon. Your instinct and SSMS were both right and we were the odd one out.

What your plan will say instead. Not nothing — the scan is real, it just isn't a conversion problem:

Scan with residual predicate — SQL Server is reading every row and filtering after the fact. Only 0.100% of rows survived filtering (50 of 50,000). Check that you have appropriate indexes.

Which is the actionable read: UNIV_SEZIONI isn't indexed. (Worth saying out loud — I'd evaluate that index against the rest of your workload rather than adding it on this one query's say-so.)

Fix is in #437, with your repro committed as a test fixture. I checked it against all 38 plans in the test corpus: not one existing plan's verdict changes, so this only ever removes the false claim.

On point 2 — you've identified something real. SQL Server's own warnings and our advice are two different kinds of thing and we were rendering them identically. Warnings the engine wrote into the plan are a record of what actually happened; our rules are inferences from plan shape, and an inference can be wrong about a specific plan the way the engine's own record can't be. This issue is the proof: we told you a conversion prevented a seek on a plan SQL Server never flagged at all.

So engine warnings now carry a [SQL Server] tag in the app and the CLI, and a source field in the JSON. Only the engine's are tagged — a badge on all of them would just be noise, since our advice is what you already expect from the tool. That's in #439, separate from the rule fix so they can be judged independently.

Thanks for the repro script — having the exact create table is what made this a twenty-minute confirmation instead of a guess.

[erikdarlingdata]

Both halves shipped in v1.20.0.

Your plan no longer gets the false SARGability warning (#437) — it reports the scan and its residual predicate instead, which is the true and actionable read. And SQL Server's own warnings are now tagged [SQL Server] in the app and CLI, with a source field in the JSON, so you can tell the engine's findings from ours at a glance (#439).

Closing this one on the title, though, because you asked for two things and I want to be straight about which shipped: what landed distinguishes provenance, not severity. You were right that the severity grades are off, and I measured it while you had my attention — across the full test corpus, severity disagrees with the tool's own computed benefit on 59 of 202 warnings, in both directions. There are Critical badges on findings worth 0.0% of runtime and Warning badges on findings worth 100%.

That is a real defect and it is going to get fixed, but it re-grades about a third of all warnings, so it belongs with the ranking rework in #215 rather than being slipped into a patch. Follow that one if you want to weigh in — you have a good eye for this and the thread could use it.

Thank you for the repro script. Having the exact create table is why this got confirmed against a real SQL Server 2025 plan rather than argued about.