question

Neil-9764 avatar image
0 Votes"
Neil-9764 asked OlafHelper-2800 commented

DTA - Not working on SQL 2016

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)






sql-server-general
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have no idea if it helps, but you should download and install Service Pack3 for SQL 2016. There is no good reason to be that far behind.

0 Votes 0 ·
Neil-9764 avatar image Neil-9764 ErlandSommarskog ·

I agree with you, but I have important databases on the server, don't want to risk upgrading the SQL Server entirely, which I have not done earlier. I was able to do this on lower version, but don't understand why MS misses this in a major releases like 2016 server.

0 Votes 0 ·

don't want to risk upgrading the SQL Server

Installing a service pack is an update, not an upgrade. SP fixes know bugs and sometimes have performance enhancements.
0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Neil-9764,

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".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Neil-9764 avatar image
0 Votes"
Neil-9764 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.