1

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.
  • 3
    How do you know DTA created and dropped an index? – Max Vernon Mar 14 '18 at 16:25
  • 2
    And how do you know that's what "locked up" the database? – Aaron Bertrand Mar 14 '18 at 16:53
  • 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
0

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.

  • thank you for that. Normally it would be great to have that as well. This was a unique situation where there is only 1 DB. What is the quote, "everyone has a test database, some people are just lucky its on a different server". We only have 1 MS SQL server at this company, not my choice and against my recommendations, but this gave fuel to that fire. What about hypothetical indexes"? – Keith E. Truesdell Mar 15 '18 at 12:34

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.