Firstly these Perm tables are created where before the temp tables were created within the process purely so i could conduct my analysis i.e capture the stats..... I should point out the process creates over 150 tables in total.... and is probably spread across a couple 100,000 lines of SQL & 250 stored procs

That means every time before i run the process i must drop the tables...

Thanks for the heads up in relation to the DMV stats being reset .. i've used these before so i'm aware of the stats being reset under certain scenarios...

In terms of the performance improvement i'm looking for, "See if there is any improvement to be made from new or modifying indexes" was my brief 🙂

I have since found out why only Non-Clustered indexes were created i spoke with some of the current Dev team they said thev'e never modified the Index's on the temp tables... then one Dev asked me what type of index is created by the CREATE INDEX statement and then the penny dropped ... the original developer used the CREATE INDEX statement which creates NON_Clustered indexes by default....