2

I am trying to use "Database Engine Tuning Advisor" for queries in Azure SQL.

According to https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor it should work with Azure SQL.

However it gives me "Failed to open a new connection".

ADDITIONAL INFORMATION: Database Engine Tuning Advisor does not support Microsoft Azure SQL Database. (DTAClient)

Is it correct? I found a thread on MSDN forums(asked at December 2016) that confirmed that Tuning Advisor worked with Azure in Microsoft® SQL Server® 2016 Service Pack 1 (SP1)

Is it still supported in Sql Server Management Studio v17.3 (Database Engine Tuning Advisor 14.0.1000.169), that I am using? What should I do to enable it?

I've tried Azure Performance Insights, but it doesn't allow me to get suggestions of indexes for individual queries that I have to analyse.

1

No. The error message is correct - it does not support Azure SQL. There is no SQL 2016 for "Azure SQL", but there is SQL 2016 on an Azure VM, which is the same as any traditional SQL Server installation (more or less).

That thread was confused, I assume, between SQL Server on an Azure VM and Azure SQL.

Using the Azure Portal you should be able to check out index autotuning.

| improve this answer | |
1

As a workaround (not complete as Tuning Advisor) if you run a query in SQL Server Management Studio and enable "Include Actual Execution Plan", in the "Execution Plan" tab you may see GREEN message

Missing Index CREATE NONCLUSTERED INDEX [] ON [dbo].[MyTable] (MyColumns)

and on Right Click you can expand "Missing Index Details"

However it gives one index suggestion in a time and not always give any suggestions at all.

Note that you may have correct indexes, but they may be fragmented and cause slow results .

| improve this answer | |

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.