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...

  1. Is my assumption correct? That DTA created the index?
  2. Was it created and dropped (before I clicked "apply recomendations") as a method of testing performance and increase and such?
  3. I thought that DTA only used "Hypothetical Indexes"....is there a field/flag for that?
  4. Am I using DTA correctly in this manner? What are the best practices for using this tool?
  5. What really could have cause this lock-up? It seems so odd.
  • I have never even thought of running the Tuning Advisor against the production db. Generating the log file, sure, has to be against prod, but the analysis should be against a copy of a prod backup, as should the testing. Running this against production is so far from 'best practice' that yout list of questions are irrelevant in any sane setup. And I echo Max and Aaron's comments. – simon at rcl Mar 14 '18 at 18:03
  • There is enough info in the DMVs to get recommended indexes. I use sp_BlitzIndex , which does all the work for you. – Greg Mar 15 '18 at 1:42
  • I know DTA created the index because it names that as _DTA_xxxxxxx and that was the index that was locked. I know it was locked because I could see it with different monitoring tools (such as the ones that SQL Sentry has to offer, Idera and the one with SSMS...sorry I forget the names off the top of my head). Also, according to the logs the index of that name was created during that run by my ID and that was the only SQL thing I was doing at the time. Also, we only have Prod (not test/dev....not my choice), and its a DB for a website and the website was sending errors related to that – Keith E. Truesdell Mar 15 '18 at 12:29
  • Thank you all for the information. @Greg I ususally use the sp_BlitzIndex as well, i like the stuff from BrentOzar and Company. I have used DTA a few times before and it was under more ideal situations and didn't have any issues. This was a situation where I wanted to use a tuning thing while I had a few other things to do. – Keith E. Truesdell Mar 15 '18 at 12:31
  • @TomV This was for MS SQL Server 2012 SP3 Microsoft SQL Server 2012 (SP3-CU10-GDR) Its not too much of an issue right now / anymore. Overall it was more of a curiosity and a slight annoyance when I was in the "thick" of tuning things there. – Keith E. Truesdell Dec 21 '20 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.