I have left my old job but cannot find a copy of the Auto Tuning Index DB TSQL that I used to have on all my servers.
It was written about 10 years ago I believe by Microsoft people and I took the code from their blog. However I cannot find the SQL to rebuild it now.
I am on a laptop, with MS SQL Express running so I cannot use the build in automatic tuning tool. I just want the DMV that holds missing indexes or unused indexes in tables, so that restarts and reboots doesn't loose the data from the DMV's that are cleared when that happens.
I know not to wildly just implement every index recommendation and to cover as many queries with as few indexes as possible. However this is a read heavy system, writing done in the morning, rest is read 90%, Insert/Update 10% til midnight.
I could build one from scratch using the DMVS that used to list the table, the index name, the main columns, included columns and so on but I know there is one out there to save me the time.
It just used to use a job to save current DMV counters of index hits, updates etc in tables so that if MS SQL was rebooted those stats would still be there and over time you can see the most unused indexes from the total count over months etc.
I have had a look tonight but can just get one SQL statement to list me some recommendations, I would like the SQL to rebuild the AutoIndex Recommendation Database.
Thanks for any help in finding it or something similar that saves DMV data on a schedule so it's not lost.