Recently I ran a trace on our database so I could then plug it into Database Engine Tuning Advisor and see what queries needed to be improved and where there might be a real need for indexes.
While running the DTA, it had created an index before the end (I assume as a test) and then it deleted the index. However, because this was all on Live, a query had wanted to use that index and when it wasn't there anymore, it locked up the database.
My general questions...
- Is my assumption correct? That DTA created the index?
- Was it created and dropped (before I clicked "apply recomendations") as a method of testing performance and increase and such?
- I thought that DTA only used "Hypothetical Indexes"....is there a field/flag for that?
- Am I using DTA correctly in this manner? What are the best practices for using this tool?
- What really could have cause this lock-up? It seems so odd.