That would happen if you did not have ord.Amount wrapped in the aggregate SUM function.

It should work w/ the query listed.

How many rows does the CTE body (the subquery portion) return? I'm not sure if the query engine is smart enough to short-cut the query based on the top 1...

You can't parameterize a from clause. You could parameterize the WHERE clauses.

You could concatenate the table type into dynamic sql as described here: https://odetocode.com/articles/365.aspx

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 =...