Repository navigation
[FEATURE] Distinguish between severity of Warnings #436
Description
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:
-
This is not a SARGable issue. The IB03.UNIV_SEZIONI is numeric(), which has a highest precedence on int. And the column is searchable (*).
-
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
Activity
Only call a conversion non-SARGable when it converts the column (#436)
Say which warnings are SQL Server's and which are ours (#436)
erikdarlingdata commentedon Aug 20, 2026
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)outranksint, so comparing your column to an integer converts the parameter up to the column's type, which is exactly what the plan says: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 stringCONVERT_IMPLICITappeared 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 FORCEDand captured two actual plans:UNIV_SEZIONI— clustered index scan, predicate exactly as above.UNIV_SEZIONI— Index Seek, and the seek predicate isCONVERT_IMPLICIT(numeric(18,0),[@0],0).SQL Server seeks straight through the conversion. It also raises no
PlanAffectingConvertwarning 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:
Which is the actionable read:
UNIV_SEZIONIisn'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 asourcefield 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 tableis what made this a twenty-minute confirmation instead of a guess.Only call a conversion non-SARGable when it converts the column (#436) (
Say which warnings are SQL Server's and which are ours (#436) (#439)
erikdarlingdata commentedon Aug 21, 2026
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 asourcefield 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
Criticalbadges on findings worth 0.0% of runtime andWarningbadges 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 tableis why this got confirmed against a real SQL Server 2025 plan rather than argued about.v1.27.0 (#568)
Keep analyzer bookkeeping off the engine's warnings, and see table-va…
Read only the function's own comparison in the non-SARGable check