Don't forget all the overhead bytes that SQL requires internally to manage the row.

You might have to move some of the values to a second table, with a 1-1 relationship...

We'd have to see the table DDL to find the exact cause of that.

Definitely force the varchar(max) data off row, as you have done.

You should also page compress the table...

You need to call the Google address parser/"splitter" function or use some other address parser that does this for you.  You will never be able to do this accurately by...

SELECT t1.ID2, SUM(t1.Amount1) AS Amount1, ISNULL(MAX(t2.Amount2), 0) AS Amount2
FROM dbo.table1 t1
LEFT OUTER JOIN (
    SELECT ID2, SUM(Amount2) AS Amount2
    FROM dbo.table2
...

Yeah, that's rather tricky.  All I can think of now is to create a stored proc with an " EXEC AS 'powerful_user_name' " clause, then give the ScriptRunner the authority...

MS has actually provided complex formulas for years.  But it's so difficult to come up with accurate numbers to go into the formulas, and the result is only as good...

• This reply was modified 5 years, 9 months ago by ScottPletcher.

Many of my dbs are in simple mode since, for those for the most part, we can reload data from the past 12 hours rather easily.  So I can have...

I really wish SQL would add an option to rebuild within a specified key range.  That would allow the benefits of a full rebuild without the downside of forcing it...

As to reorganization, I believe it's still best practice to use reorg for columnstore tables (I'm on SQL 2016).   Full rowgroups won't be affected at all by this process, but...

There's no magic formula to tell you the best fillfactor for 3,000 indexes.  Or for 10 indexes, for that matter.

Taking a step back for a broader look, the single most...

I'm not 100% sure of what  you need, but I think the total from the first table is being overstated.  If so, then maybe this:

select 
count (H.Customs_Entry_Num)
,max(L.[Child_Customs_Entry_Num_Count])...

Thanks both

It looks like it's the ROW_NUMBER() section that's taking the time to work out.

Is there any sort of alternative to that bit?

I need to get the records in...

I think you do need at least a couple of indexes.  Also, you should capture both pat and ref flags in the same pass of the table and update them...

SELECT OrderNumber
FROM #X_ReadytoPick
GROUP BY OrderNumber
HAVING MAX(CASE WHEN OpsStatus = 'Okay' AND SOPstatus IN ('Acknowledgement', 'DocsPrinted') THEN 0 ELSE 1 END) = 0
ORDER BY OrderNumber /*optional, of course*/

First try "SELECT COUNT(*) FROM sys.dm_exec_cached_plans", to see how many cached plans you actually have.  Then go from there.