Database Engine Tuning Advisor Crashes Constantly

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.