I am reviewing a SQL Server 2008 R2 instance with 30+ databases with the goal of moving to SQL Server 2014. In reviewing this I found a SQL job that a previous employee implemented. The job utilizes a set of scripts from this article https://www.sqlservercentral.com/forums/topic/indexing-views-1, to automatically create and drop all recommended indexes every half hour 24/7. When this was implemented the databases were roughly 40gb, but since have grown to over 1TB as we are a highly transactional company. With one of the databases running our primary ERP/ordering system. From everything I understand about indexing, this seems like a terrible idea as it could be creating and dropping indexes on very large tables. Is this a good practice, am I missing something?
Found this in a related post
"How to use it? Run AutoIndex.sql to install the SPs and sql agent job. Upon every 30 minutes, the sql agent job will run the auto create index and auto drop index scripts to make recommendations. Same recommendation will not be stored multiple times, instead we just bump up the count and change the latest recommendation time. You can view the recommendations using the simple commands in the viewrecommendations.sql. Look for the recommendations in the recommendation table that have high counts, which means they have been repetitively recommended thus are more valuable. You can also look at the initial recommendation time and the last recommendation time to get a sense of the freshness and the time range this recommendation is valid for. After you made a decision to implement a recommendation, simply run execute_recommendation with the recommendation id and the recommendation will be implemented automatically." Thank U Snehal
According to that user the script you're proc you have in your system should just aggregate index recommendations over time and allow you too see what indexes are constantly being recommended.
I believe the important distinction here is SQL doesn't log how many times it suggest a particular index so you may get a suggested index based on a one off query, which probably isn't something you want to implement. Instead you run this for a period and see what's being hit frequently and create those indexes.