5

I am trying to use Database Tuning Advisor for the first time.

I believe I have resolved all permissions-related issues - needing SHOWPLAN, needing SELECT permission on 'DTA_tuninglog', etc.

Yet under 'Recommendations' it shows nothing.

It definitely runs and seems to be doing work, and there are no bad-looking errors in its output, but no recommendations are given.

I even tried creating a dummy database with a horrible query (lots of table scans) that would clearly be made better with an index, but it doesn't show any recommendations for that, either.

Perhaps my real question is: How do I know when DTA is actually working, versus me having some configuration issue that causes it to produce no output?

Does anybody have any pointers?



More Info:

I am using the defaults for the 'Tuning Options'

Physical Design Structures (PDS) to use in database: Indexes

Partitioning strategy to employ: No partitioning

Physical Design Structures (PDS) to keep in database: Keep all existing PDS

jwd
  • 10,837
  • 3
  • 43
  • 67
  • 3
    Maybe your database is already perfect? – Aaron Bertrand Sep 13 '11 at 20:10
  • @Aaron - ha! Plus, as I mentioned, I tried it on a obviously-in-need-of-help test table, and still nothing... – jwd Sep 13 '11 at 20:11
  • can you post a pic of the options you have selected in the first scree please? – kmcc049 Sep 13 '11 at 20:12
  • @Aaron Bertrand, I almost spit up my soda when I read that. – HLGEM Sep 13 '11 at 20:12
  • @kmcc049: I can't easily get a screenshot at the moment, but can post info. I added the 'Tuning Options' page info, if that's what you meant. – jwd Sep 13 '11 at 20:18
  • yeah, which tables did you select in the two parts where you select tables? Also how big was the workset you used for analysis? The tuning advisor tends to need quite a few queries to give any good advice if you've only got ten or so its not gonna give you anything. – kmcc049 Sep 13 '11 at 20:36
  • @kmcc049: I selected the topmost checkbox for table, which made it auto-select all related tables in the lower section. Good to know about the data needing to be large. I think mine qualifies, it is from a 'stress test' run against the server. – jwd Sep 13 '11 at 20:45
  • did you record it in tuning mode? it one of the templates you select when using profiler? – kmcc049 Sep 13 '11 at 21:13
  • @kmcc049 - Thanks for that; no I did not. After changing that, now DTA chugs for a long time and eventually throws an exception. I guess that's progress! – jwd Sep 13 '11 at 22:01
  • np, if I change that to an answer will you give me an accept then? – kmcc049 Sep 13 '11 at 22:06

1 Answers1

1

Make sure that you have recorded your analysis data in tuning mode. To do this in SQL Server Profiler when you create the trace there is a drop down list with the label 'Use the template' change it from "Standard (default)" to "Tuning"

kmcc049
  • 2,783
  • 17
  • 13
  • 1
    With the qualification that DTA still may choke on your data (as it likes to on mine :) - I have subsequently had better luck feeding DTA with a single .sql file containing the slow query in question. – jwd Sep 13 '11 at 22:33