I tested out the permissions by logging in using SQL Server Authentication and trying to select from another table NOT in the list of 10. To my surprise, the rows...

Are AD groups given permissions to tables?

And does that login belong to one of those AD groups?

When tuning, first concentrate on getting the best clustered index on every (significant) table.  The clus index is by far the most significant performance factor for the table. ...

If you have a query that is taking a long time to execute. Just paste it into SSMS and press the estimated execution plan button in the menu: 

My specialty is tuning indexes.  I do it nearly every day.  And I typically don't look at queries to do that.  I don't generally need to.  Nor would I have...

Edit: I just read the email of the query results, which were perfectly readable.  I've adjusted the comments/code to reflect that.

--N/A after Edit: It's extraordinarily hard to read those results...

• This reply was modified 4 years ago by ScottPletcher.

Auto-id (identity in SQL Server) is not always bad as the clustering key (*), but it's disastrously bad to default to using it as the clustering key.  The clustering key...

A lower-level query (llq) can automatically references all columns from a higher level query (hlq).  This is intentional and is not an error.

For example:

SELECT ...

FROM dbo.hlq

WHERE EXISTS ( SELECT 1...

Why are you exclusively locking the table?  That's almost never necessary, particularly on an INSERT.  That could definitely cause blocking / locking issues.

You can explicitly set DB_CHAINING on (ALTER DATABASE ... SET DB_CHAINING ...) for selected dbs if you prefer.  You might want to consider that in this specific case.

You provided no sample data, so no way to test.  But something like this should help.  If table "accession_2" does not contain duplicate "accession_number"s, you should be able to remove...

So you mean to move data to a new filegroup just that one table?

No, all the (very) large tables.  Until the original filegroup is small.

Because it's LOB data, isn't it's...

SQL could have to rewrite roughly 3.4TB of data (the used space) to shrink that file -- that will take a long time.

Are you using data compression on the tables? ...

(1) force large value types of out row (as above).  Note that for an existing table, you need to run an UPDATE to move the actual column values off page. ...

For efficiency, create a clustering key on the table on ( header1, header2 ).  Since SQL Server so strongly prefers unique indexes, if those values aren't unique by themselves, add...