where Date From >=  '2020-04-01'  and  Date To <= '2020-04-28'

There is no data in your sample that meets that requirement.

In

2  ----- 2020-04-01 ----- 2020-04-30

Date...

Is there a reason you don't have a clustered index or primary key? (may or may not be  the same)

Is TempDB on the C Drive? Is it growing/filling the drive?

Or is it actually SSMS temp files mentioned previously?

Check/tune their queries if possible.

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.