I know overlapping indexes are an issue but one a production system having a permanent DB which stores the missing index DMVs so you don't lose recommendations between server/service shut downs is a good idea because you can then see the actual real cost of indexes, missing or ones not being used and it helps guide you in creating indexes - not that you should follow the recommendations blindly, but they are a guide to what queries are missing index coverage especially in production.
At least then you can see which indexes have high counts and low counts and use your brain to create indexes that can cover one or more recommendations. I have used this system since DMVs came out and it has saved a lot of time in working out which indexes to create as you may think certain queries/indexes are the most important but from production you might actually see totally different ones are being used more.
I have never said to just add multiple indexes on top of each other and especially with SQL Express I would be even more wary of making too many indexes per table as the size probably comes into play a lot more therefore I want to know the actual key / important queries that my system is using that require coverage not just add a load from my own testing and then find out that my tests were just a tiny percentage of all queries made and that actually I really needed totally different index coverage.
This is why that system is so good as it keeps the counts in-between server/service shut downs when the DMVs counters are reset so you don't create a biased count from your own query testing.
I did "think", that the table collation was the one I needed to change the DB to, but then after testing I found that it was my testing on these 2 main tables that had made me think this. For some reason they have a different collation than the DB and the rest of the tables. I think what happened was I ported down a copy of the DB (backup/restore), then realised the size constraints of SQL Express, and created a new DB with the new collation and ran scripts porting over data but just for certain tables, and certain date driven data, e.g I have records of races going back before 2000 but don't really need all that data so a few years has been ported instead.
Anyway as these indexes weren't been copied down from the existing system keeping the DBs initial collation as SQL and then changing the columns to LATIN ( rather than the other way round ) seems the way to go. I can drop my indexes on these main tables now - then change the tables collation with the query that outputs all the ALTER table statements e.g
SELECT 'ALTER TABLE ['+USER_NAME(o.uid)+'].['+o.[name]+'] ALTER COLUMN ['+c.[name]+'] '+
WHEN c.prec IS NULL THEN t.[name]
END+' COLLATE '+t.collation
FROM syscolumns c
JOIN sysobjects o ON (c.id = o.id)
JOIN systypes t ON (c.xusertype = t.xusertype)
WHERE c.collation IS NOT NULL
AND o.type = 'U' -- NOT IN ('P','FN','TF','IF','S','V')
AND c.collation COLLATE Latin1_General_CI_AS != t.collation
ORDER BY o.[name]
- then rebuild the queries.
Then I need to look at a way to implement an "MS Agent" type replacement system that can run scheduled SQL jobs as its not just saving DMV data I want to do but system clean up / maintenance / table trimming - even more important on SQL Express / Query re-organisation/rebuilding / and hopefully be able to run the timed jobs that build betting systems if possible rather than have them all spawned from a Windows Service I was going to use with timers and numerous "jobs" , calling the relevant stored procedures.
Thanks for your suggestions