0

My Windows workstation can launch the GUI for the Database Engine Tuning Advisor, but the CLI called "dta" is missing. Why?

Background

The Database Tuning Advisor is a SQL Server tool for suggesting changes to indexing and other things. It can be run two ways: A GUI, and a CLI. The GUI can be launched by searching for "Database Engine Tuning Advisor," or from SSMS. The CLI can be accessed with the "dta" command.

The GUI runs just fine, but not the CLI. According to the dta Utility documentation, the CLI is called "dta"

dta  
[ -? ] |  
[  
      [ -S server_name[ \instance ] ]  
      { { -U login_id [-P password ] } | -E  }  
...

But when I try to run it, from either a user or Administrator command window, it's not found:

C:\WINDOWS\system32>dta
'dta' is not recognized as an internal or external command,
operable program or batch file.

What files named "dta" are present?

I used cygwin to search for all files which name starts with "dta". Some are found, but no "dta.exe":

$ find -name 'dta*' -ls
562949953516183    0 drwxrwx---   1 SYSTEM   SYSTEM          0 Jun 12 09:03 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server/140/Tools/Binn/schemas/sqlserver/2004/07/dta
1125899906937496   52 -rwxrwx---   1 SYSTEM   SYSTEM      52868 Aug 22  2017 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server/140/Tools/Binn/schemas/sqlserver/2004/07/dta/dtaschema.xsd
1688849860388529   20 -rwxrwx---   1 SYSTEM   SYSTEM      16813 Jun  5 06:14 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dta.exe.config
1688849860388532  220 -rwxrwx---   1 SYSTEM   SYSTEM     225080 Jun  5 06:04 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dtaengine.exe
1688849860388534   20 -rwxrwx---   1 SYSTEM   SYSTEM      17902 Jun  5 06:14 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dtashell.exe.config
199565758487855892    1 -rwxrwx---   1 wayne    None          592 Jun 11 09:22 ./Users/wayne/AppData/Roaming/Microsoft/Windows/Recent/dta_tutorial.lnk
10414574138517174    4 drwxrwx---   1 wayne    None            0 Jun 11 09:47 ./Users/wayne/Documents/dta_tutorial

What's installed

  • Windows 10.0.18363 Build 18363
  • SQL Server 1027 Standard Edition (14.0.2027.2)
    • SQL Server Replication
    • Full Text and Semantic Extractions for Search
  • SQL Server Management Studio 18.5.1
  • Database Engine Tuning Advisor v18.5.1

History

  • The workstation had SQL Server 2014 installed
  • It had a prior version of SSMS installed
  • I upgraded to SQL Server 2017
  • I upgraded SSMS to version 18.1
  • dta was available, but it's -g (GUI) switch wasn't working. It complained about an expired or missing license.
  • A license error is what I've seen when SSMS is updated but the prior version of SSMS is invoked, very similar to how the "dta" tool was behaving
  • Under the hypothesis that the upgrade had not gone well and my dta was associated with a prior version of SQL server, I decided to start from a fresh install
  • I uninstalled anything associated with SQL Server 2014, SQL Server 2017, and SSMS.
  • I did a fresh install of SQL Server 2017
  • I installed all updates for SQL Server 2017
  • I installed SSMS 18.1
  • I found that the dta command was no longer present.
1

DTA used to be a part of SQL Server, but it moved to SSME. During the upgrade from SQL Server 2014 to SQL server 2017, two things happened:

  • SQL Server 2014 DTA.EXE was left behind
  • SSMS 18 did not add the directory containing DTA.EXE to the path

Therefore, dta.exe was brining up the SQL Server 2014 DTA, which no longer had license information and was failing.

The fix is to add the SSMS directory containing DTA.EXE to the path, before any other SQL Server directories. See https://stackoverflow.com/q/44272416/238886 for how to edit the path in Windows 10. Here's the directory to add. Make sure it's above the other SQL Server directories, as shown here:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.