CODEWITHSUNDEEP
sql-serverazureazure-sql-databasedta
Ⲁⲅⲅⲉⲗⲟⲥ
Ⲁⲅⲅⲉⲗⲟⲥ

Reputation: 668

How to measure the performance of the Azure SQL DB?

I need to measure the SQL Azure DB performance using DTA, is it possible or not, if not what is the workaround to consume a workload file (.trc)??

Upvotes: 2

Views: 1010

Answers (2)

Conor Cunningham MSFT
Conor Cunningham MSFT

Reputation: 4501

Alberto is correct - there are features within SQL Azure which help watch and improve the performance of your database queries automatically in some cases. Profiler trace + DTA are not currently supported in SQL Azure. The DTA (Database Tuning Advisor) feature in SQL Server is very good for taking traces and trying to replay them on a different server to simulate possible index and partitioning changes which could improve your performance. The automatic tuning feature does that for you without having to use DTA today yourself.

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning

If all you want to do is explore the performance of your database, then you can use the query store in SQL Azure (and SQL Server 2016+) to do this kind of analysis.

https://azure.microsoft.com/en-us/blog/query-store-a-flight-data-recorder-for-your-database/

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

If you have not tried this using a recent release of SQL Server Management Studio(SSMS), then I highly suggest you download this and try it. You can see top N queries by different metrics, plan changes over time, and other metrics which give you faster insight into the performance profile of your database + application.

There is no way to take a .trc file today and examine it in the query store, but you can enable query store in an on-premises SQL Server (2016+) and then record your production workload for awhile to see how it is behaving. Please understand there is an overhead to running with the query store on - usually it is modest, but for highly ad hoc OLTP query workloads you may see larger overhead. There are some knobs to tune this, so please just go through normal due diligence before modifying a production system. If you have problems, turn it back off and re-examine until you have the right settings to help capture the relevant data from your workload to help make tuning decisions.

Hope that helps!

Sincerely, Conor Cunningham Architect, SQL

Upvotes: 2

Alberto Morillo
Alberto Morillo

Reputation: 15698

Database Engine Tuning Advisor does not support Azure SQL Database. It is also not possible to create a trace file from an Azure SQL Database using SQL Server Profiler.

SQL Azure automates the creation of indexes that may improve performance of your workload with a feature named automatic tuning. Automatic Tuning on Azure SQL also drops redundant indexes and uses the best execution plan for queries

Upvotes: 2

Related Questions