Index analysis is complicated!  So, just to get a place to start, I'm looking at "borderline duplicate indexes."
 In the cases where there is a single-column index  AND another non-clustered...

I've attached the results.

Jeff
I use Chrome and if I use the Sql Code button  below, pasting in Tsql works just fine.   It's the results that are problematic -- ssms grid results.

Yes that works well.  And I see you can do some common formatting such as Control B for bold without having to access the Format button. The preview is immediate...

ok, can some of this be commented out?

MAX(ds.ItemNumber) AS Equality#
  FROM #index_missing
  CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds
  WHERE equality_columns IS NOT NULL

This seems to fix it

FILEGROUP_NAME(iif(i.data_space_id>32000,1,i.data_space_id)) AS main_fg_name,

Now does anybody really want to see a spreadsheet of the whole database, from this...

This change didn't remove the error       I see there are two indexes that have data_space_id greater than 60,000

FILEGROUP_NAME(cast(i.data_space_id as smallint) ) AS main_fg_name,

On the copy of prod I can run it on one table at a time, generally.  Same error with certain tables.  Everything smallint changed to int.  Looking at the temp...

I'm looking for the problem.  Changing the 3 declared variables from smallint to int didn't help.  It succeeded on a small test database but having this error on a large...

Absolutely, I'm a little over my head.  I really think there are enough design problems to warrant a consultants time --- just have to convince management.

I"m just focused on page splits and fill factor at this point.   Other analysis is going on for missing indexes, unused indexes, overlapping etc etc.

Found this query...

Thanks for that.   What would suggest as far as changing that clustered index?  The table as a few other indexes that cover Client_ID, as well as one on that...

It's a large table and pretty busy.  It has about 25 indexes but here's the PK and the one odd-ball ( partitioned index )
Default fill factor in prod is...

Well we're talking about an application and database created by developers years ago, that has now grown to about 6 TB.

So while we could consider changing the PK...

I probably need to drop and re-create the two in-memory tables so that their durability is just Schema, not Schema and Data since we're ok with data loss for a...