DTA - Not working on SQL 2016

Question

Hello - I am trying to use Database tuning advisor on "Microsoft SQL Server 2016 (SP2-GDR) (KB4583460)" It is giving me below error.

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Answer 1

Hi @Neil ,

Did your account is a sysadmin account? In order to start tuning application needs to create some objects in MSDB database. You have to be a member of sysadmin fixed server role in order to initialize MSDB for tuning (DTAClient).

You can use SQL Profiler on another server has same version, that was working, to see what is happening when firing up the Database Tuning Advisor, and find that it is looking for which objects in the MSDB database. Check whether these objects are missing in failed server. You can script these objects on working server, then create them in failed MSDB.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I understand that I have to create objects, but do not have the code to create them for 2016 version I am using. I see some code online but that is of older version. I dont know why MS didnt created this code by default in 2016.

Any way I was able to tune the query without DTA, but I may need it in future.

Answer 3

It's good to hear that you were able to tune the query without DTA! And maybe it's good if things stays that way. You see, DTA is not intended to tune individual queries, but to analyse workloads.

When I work with clients, I can sometimes see the result of DTA being used in the wrong way. That is, people feed DTA a single query whereupon DTA tunes that workload, as if your application only runs that single query all day long. A typical recommendation from DTA is to add an index that covers the workload. The net result is that you get a number of redundant indexes:

CREATE INDEX ix1 ON tbl(a, b)
CREATE INDEX ix1 ON tbl(a, b) INCLUDE (c)
CREATE INDEX ix1 ON tbl(a, b) INCLUDE (c, d)
CREATE INDEX ix1 ON tbl(a, b) INCLUDE (c, d, f, g)
CREATE INDEX ix1 ON tbl(a, b) INCLUDE (c, d, e)

and so on.

The intended usage of DTA is that you run a trace or XEvemt-session for a full day and then feed that information to DTA. Then you may get some more useful advice out of it.