Is there a reason the app team can't tell you which columns are unused?

You could use server-side trace or extended events to log executions over a period long enough to...

Actual execution plan  (obfuscated as needed) could help. Part of an image is of limited value.

What are the actual outputs?

Are there scalar or multi-statement table-valued functions involved?

The costs pictured are...

You probably could/should simplify SiteType to not be organization specific -- e.g.,

• HQ
• Production
• Workshops

Then it doesn't need OrgTypeID.

Sample data would help, but it appear that relationships  to UserOrgType & UserOrgSiteType are wrong.

userOrgTable should probably have:

• userOrgID
• userOrgName
• userOrgTypeID: Assuming there is only one type per organization. If...

There's not much optimization you can do for selecting all rows and columns from a table -- best optimization is to return fewer columns is you don't really need all.

The...

What type of values are used in ROWID_LOCK?

Are they numeric strings? alphanumeric?

Are they ordered or random?

Are you actually seeing performance issues as a result of page splits?

Is this related to/a continuation of https://www.sqlservercentral.com/forums/topic/pagesplits-question?

Why don't you have a clustered index?

What column(s) are used in the where clause for doing deletes?

You also need to rebalance your tempdb files -- they should all be the same size. This will, unfortunately, require restarting SQL Server service.

e.g.,

ALTER DATABASE tempdb

MODIFY FILE (Name=tempdev, size =...

You post data by providing tested/working insert scripts that insert test data into the relevant tables for which you have provided DDL scripts.

That has been mentioned before -- e.g., https://www.sqlservercentral.com/forums/topic/dimensional-table-and-stored-procedure#post-4145269

member_number is a bigint in [localgroupmembershiprostercsv__$], & MemberNumber is only an int in @Temp & Members? Are any of the member #s larger than an int & possibly truncated (which...

Sounds like a prelude to spam.

Despite the name, I'm not aware of that being a Microsoft system stored procedure.

Can you provide the relevant body of the stored procedure (obfuscated for security if necessary)?

The error means...

You can't alter an existing computed column -- it has to be dropped & re-added.

Not much to go on. Is  the data relational? What database system is it stored in? Do you intend to move it to the same database system? To a self-managed...