ProcessMfgID being automatically populated in BAQ joins is an established nuisance for those of us not appropriately licensed. Being locked out of full indexes that include those fields where there are no alternatives can cause some annoying query performance tuning puzzles.
As ProcessMfgID is becoming a particular nuisance in my little corner of the world, I’m digging deeper. The good news is, we’re only partially locked out of these indexes. SQL Server will apply a partially referenced compound index if it’s referenced correctly. Consider the index,
CREATE INDEX myindex ON mytable(myfirstcolumn, mysecondcolumn)
If I write a join referencing myfirstcolumn or reference it in a WHERE statement, the index will be used even if I don’t mention mysecondcolumn. If I reference mysecondcolumn but not myfirstcolumn, the index won’t be used.
How do we know that ordering if we didn’t write the index? This information is stored in the system catalog as key_ordinal. Here’s where I ask a favor… SaaS users aren’t granted system catalog access, and BAQ references about data structure are incomplete and not entirely correct. Perhaps someone with a self hosted instance (who knows what they’re looking at before running SQL copied from the internet) might be able to help? I’m making one or two guesses here but I’m confident this query will do the deed:
select
i.name as IndexName
,t.name as TableName
,c.name as ColumnName
,ic.key_ordinal
from sys.index_columns ic
join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
join sys.tables t
on t.object_id = ic.object_id
join sys.schemas s
on s.schema_id = t.schema_id
join sys.indexes i
on i.index_id = ic.index_id
and i.object_id = ic.object_id
cross apply(
select distinct
null as 'found the field'
from sys.index_columns caic
join sys.columns cac
on cac.column_id = caic.column_id
and cac.object_id = caic.object_id
where 1=1
and cac.name = 'ProcessMfgID'
and caic.index_id = ic.index_id
and caic.object_id = ic.object_id
) as OnlyIndexesContainingProcessMfgID
where 1=1
and s.name = 'Erp'
order by i.name
,ic.key_ordinal
Here’s a great example of the outcomes I’m after (perhaps for definitions of “great” where “it work on my machine”):
I’m auditing purchased parts in bills of materials that arent’ QtyBearing. Easy enough to join PartRev on PartMtl on Part, right?
select
[PartRev].[PartNum] as [ParentPart],
[Part].[PartNum] as [ComponentPart]
from Erp.PartRev as [PartRev]
inner join Erp.PartMtl as [PartMtl] on
PartRev.Company = PartMtl.Company
and PartRev.PartNum = PartMtl.PartNum
and PartRev.RevisionNum = PartMtl.RevisionNum
and PartRev.AltMethod = PartMtl.AltMethod
inner join Erp.Part as [Part] on
PartMtl.Company = Part.Company
and PartMtl.MtlPartNum = Part.PartNum
where ( PartRev.Approved = 1
and Part.TypeCode = 'P'
and Part.InActive = 0
and Part.QtyBearing = 0)
Welp that consumes 14 seconds of execution time to return a few rows.
Let’s assume for now that I’m using an entire index or foreign key between Part and PartMtl since I didn’t modify what was automatically populated.
What if I assume that deleting the ProcessMfgID reference in the join between PartMtl and PartRev misdirected the SQL Optimization Engine away from using a related index?
Then, what if I make a guess that Company is at the front of a relevant compound index on PartMtl, and try using that as a nudge to reference that index in the execution plan?
<snip>
where ( Part.TypeCode = 'P'
and Part.InActive = 0
and Part.QtyBearing = 0
and PartRev.Approved = 1
and PartMtl.Company = @CompanyID ) --I added this line, that's it
56ms. No change to outputs. That’s a win.
Adding locked-out data to compound indexes broke database performance. Applying knowledge about how indexes are structured to slightly arcane understanding of how compound indexes work is how we get some of that performance back.
I’ve sometimes found that adding Company = CurrentCompany does help to improve performance, but I never did the deep dive you’ve done there to understand it - thank you
So I decided to take the @kananga challenge and run the sql and test this out in my 25.1 since I need to get some time in the baq designer.
They do not exist in my current system, but the attached is from 25.1.
I made the query and my results are even better. I got the warning that the join was using non licensed column, but the Analyze did not flag that as an issue. Without Company was 124 seconds and with was 123 ms. So Seconds to milliseconds.
That’s really important to watch out for! Previously, retaining this field in a join would cause the query to fail to run, and users complained. Developers made the problem go away by making the problem statement technically false. Now if you retain this field in a join, the join silently fails with only a mild warning in BAQ.
Try this:
In a simple BAQ, add PartRev and PartMtl without modifying the default join. Select any fields, we’re just seeing that data is returned here so just ‘company’ is fine. The query will run successfully and return an empty dataset.
Go back to the query diagram and remove the ProcessMfgID join condition. If you have any part revisions containing materials, the query will now return data.
I totally forgot to look at that until just now. Thank you very much!
Indeed every single one of those compound indexes has Company as the first ordinal field. So, pasting where company = @company in query and table expressions where ProcessMfgID was removed should be a performance tuning step. Not a panacea depending on the query but worth trying.
Definitely going to be adding that as an expression like @company --brief footgun alert comment when that happens so the next person to discover that particularly eldtritch workaround doesn’t toss it and blow up a production query. And by “next person” I’m including future me because of precedent reasons.
I believe you can see the name of all table indexes provided out of the box in Data Dictionary. If you know the name of the index you can optimize the BAQ by going to Actions->Query Execution Settings. Then add a new QueryOption that specifies the index to use, for instance: TABLE HINT(Erp.Project,INDEX(IX_Project_Description))
Keep in mind, that It’s possible that you just made enough changes to kick out a bad plan. It’s really hard sometimes to know what things are affecting performance in SQL.
Data dictionary returns index names, but not their structure. There is fragmented info in ZKey and ZKeyField, but it’s incomplete and some info isn’t actually possible. Most importantly, it doesn’t return index structure. That’s important for cases like this one where ProcessMfgID jumped the ordinal line in front of critical fields like MtlSeq.
I generally try to avoid query hints in production, always if I don’t have access to execution plans and the query cache. There are several ways to gum up a server as an outcome of optimization engine imperatives based only on execution time. Besides, if a query hint works, that indicates the query can be fixed without the hint.
That’s a good reason to use query hints, for us SaaS folks anyway. OPTION(RECOMPILE) skips existing query plans. The way BAQ works is bad at query plans in general, but not perfectly bad, so it’s worth momentarily using such a hint to verify a performance change.
Reviewing execution plans and noodling around in the query cache would confirm an improvement of course, but I very much don’t want SaaS access to that level of shared securable.
@Banderson True, but in my limited playing with the one baq removing the company made it revert back to slow and adding it again made the same improvement.
I do not know enough sql to know if that is good plan bad or not.
Yeah, that’s usually because that old plan is there and it picked it up again. It doesn’t delete the old one when you’ve made a change enough to make a new one. It just leaves it there until “The same query” comes up again. But it doesn’t have an ID that it uses, it’s comparing the query to what it has in the store.
Since you’re on Prem, you can run DBCC FREEPROCCACHE and that will actually clear out all of the plans. (this is a hammer, so I wouldn’t use it all the time, but if you’re in a dev env, you can try it). See if running that, then running your query again changes the performance, even without adding or removing company.
That being said, adding company in all of your joins is a good idea, as that will help it use the right indexes. But parameter sniffing muddies up the waters when try to test performance.
PSA for accidental DBA’s: doing this wide open on a production SQL instance will make sure you don’t get lonely for a bit. It’ll drop all the plans and all of your users will be stuck behind a recompile load every time they do something where the cache hasn’t been built or settled out yet.
FREEPROCCACHE has optional args you can use to snipe a specific plan out of your cache without affecting anyone else. I might see if I have some notes on that lurking about later…
[edit] Not trying to step on toes! I was once that accidental DBA. I learned a LOT about the cache plan parts of the system catalog that week, one everyone stopped coming over to ask, why so slow all of a sudden.
I would really recommend this course. It will teach you a lot about how SQL databases work. We bought the unlimited class, and for me, it was totally worth it to work through the videos. You still won’t know everything, but you’ll definitely understand a lot more.
Also in Execution settings you can switch to alternate company security and it will create company clauses differently, which may make your explicit Company=@CompanyID redundant
The company=@company is already redundant. It’s a hack. Not a reliable hack, and absolutely not a fix.
Incorporating ProcessMfgID before the end of the ordinal list of indexed fields in compound indexes broke index inclusion for all users who aren’t licensed to read ProcessMfgID. Query performance which depends on those indexes has been made worse by a verified three orders of magnitude by this change.
I’m trying to figure out how to write a support ticket for this. It’s a rather arcane technical mistake, and I’m not sure how to get it in front of someone with that kind of skillset.
