2

I'm attempting to use Database Engine Tuning Advisor to tune my database. From the comments it's logging (it's just 40% into the analysis, after running all weekend) it appears that DTA is not capable of tuning operations on Temporary tables. Is that in fact the case?

Greg Dougherty
  • 3,281
  • 8
  • 35
  • 58

1 Answers1

2

No. Because in order to apply an index to a transient temporary table you would have to add the index to the script that used the temporary table. It can't just be applied to a non-permanent table as a set-and-forget operation.

What you can do is create a real table with the same name in your database and remove the creation of the temp table from scripts and replace with TRUNCATE TABLE. If you create this table, and then perform actions against it, the DTA will recommend indexing for the permanent table. You then script these recommended indexes, and add them to the temp table in your script.

Also: be aware that while the DTA does a pretty good job in most cases, it doesn't always get it right...

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • I was afraid of that. Not sure what your second sentence means. After all, I DO create the index in the code that uses the temp table, said code being the code I'm having DTA look at. And yes, I'm not going to blindly trust DTA's recommendations. :-) Thank you. – Greg Dougherty Apr 25 '11 at 15:28
  • @Greg Dougherty: By temporary replacing the temp table with a permanent one, the DTA might make index recommendations for that table. Simply script out those index definitions and add to the definition of the temp table. – Mitch Wheat Apr 25 '11 at 15:30