3

Microsoft SQL Database Engine Tuning Advisor seems to crash constantly for me... on multiple different servers, for multiple different databases, and throughout multiple different versions of SQL server (and DTA)...

I know this is probably a ridiculous question and not of the quality one would expect on stackoverflow :( but has anyone else experienced this?

Novox
  • 774
  • 2
  • 7
  • 24
  • Yes. I've found that it simply cannot handle some kind of trace events. I've had to pare back my SQL Trace event selection (i.e. don't include any events from the "Stored Procedure" section) before it would stop crashing. – pmbAustin Aug 14 '14 at 18:47
  • I have the same issues. The suggestion by @pmbAustin to exclude the Store Procedure events resolved the crashes for me, but I have no idea what the impact of this would be though. In my view, his comment should be an answer as it's at least a workaround. – Jaans Nov 24 '14 at 11:29

3 Answers3

1

I was having the same problem, as recently as SQL Server 2014 with Service Pack 2. I had to use a two-step approach to get it working again:

  1. Installed both the latest service pack, and also the latest cumulative update for the service pack. This fixed the issue with Database Engine Tuning Advisor, but it was still crashing for me (see step 2)

  2. I read where "hypothetical indexes" are added to your database when Database Engine Tuning Advisor is running. If it crashes, and does not complete successfully, the hypothetical indexes are not dropped. It was recommended that the hypothetical indexes be dropped from your database.

The combination of installing the latest service packs and cumulative updates, along with dropping the hypothetical indexes, seems to have worked for me.

Rob Wells
  • 11
  • 1
0

I've experienced this behavior many times, and the way to fix it was to update my instances to the latest Service packs.

also the first version of SQL 2012 Tuning Advisor was crashing for some reasons, but updating to the latest SP2 has fixed this issue.

side note: plan-cash (a new features in SQL 2012) maybe helpful until you fix this problem permanently.

Mohammed Swillam
  • 9,119
  • 4
  • 36
  • 47
0

I was experiencing the same issue when running analysis on a database that contained encrypted stored procedures. I removed the encryption before I captured my profiler trace workload then re-ran the analysis and issue was resolved.

Clayton Coomber
  • 1