SQL provide missing index info that you can use to help determine whether you need other index(es) or not, or even whether you need to change the clustered index to...

OK, technically, other queries in the same transaction that did the UPDATE could see it before committing it, in something like this:

BEGIN TRANS

UPDATE

SELECT

COMMIT TRANS

But not within the context of a...

Actually you'd get an error, since alias "s." is not defined.

The old value.  The new value can't be seen by a transaction modifying that table until the transaction's been committed.

You really need to provide directly usable table structures and data.  That is:

CREATE TABLE and INSERT statement(s)

CREATE TABLE #df1 /*or just df1*/

( version varchar(10) NOT NULL, host ... )

INSERT INTO...

SSMS is not great about how best to define indexes.

Would you be able to post the query and the DDL for the table, including the indexes?

And please confirm, are you...

Maybe as below.  No directly usable data so I can't test it.  This code takes advantage of the fact that df1.host is known to be equal to df2.pc.

            
January 20, 2021 at 2:39 am
            
#3835308

            
            
                                                
        

I don't think there is a single "best" CPU%.  But I would be OK with generally 80-90%, as long as there weren't periods where it spiked above 95% and stayed...

I'm confused then.  When you originally asked:

Any suggestion to tune this query?

Did you expect we...

You really should not use a temp table for this.  It's a lot of overhead copying rows to the temp table, esp. if you then separately build an index afterward. ...

It shouldn't be clustered, but you do need an index, as I noted above, on:

( user_id, allowed_member_id )

 If the user has a restriction on the members he can view, we're inserting into a temp table only the members he is allowed to see;

Isn't the list of allowed...

contains (PRODUCTS, 'Product1 OR Product2 OR Product3 OR Product4 OR ...')

Nice.  I was familiar with the others but not with "select all user...

You specified an INNER JOIN.  That means SQL must check to see if a matching row(s) exist in the other table because, if not, SQL can't return the row.  YOU...