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

Your understanding is correct, I think.  As Grant noted, since tables, views, etc., exist only within a database, and not at the instance level, you can't grant permissions to them...

1) You need another table with just ID and NAME.  Repeating the same name in multiple rows is a 1NF violation.

2) The clustering key to this table should be (ID,...

For example, please provide us the table structure in code and sample data in code form, as INSERT statements:

IF OBJECT_ID('tempdb.dbo.#Employee') IS NOT NULL
    DROP...

Yes, I also thought about it, but the following example shows it doesn't matter - SQL Server implicitly converts NULL to the correct type, no?

DECLARE...

It's completely dependent on the indexes. I know Minion lets you pick lists of indexes & break up the process. I suppose Ola's script must too. Do that....

And you never want the log to autogrow during index maintenance because it's such a slow process.

Instead, pre-grow the log to the total size you'll need.  If you really must,...