Does extended event create overhead in azure sql database?

Question

Does extended event create overhead in azure sql database?

Answer 1

@Saha, Sanjay (NIHILENT)
Thank you for the question and for using Microsoft Q&A platform.

Extended Events in Azure SQL Database are designed to be lightweight and impose minimal overhead on the database system. This makes them an efficient choice for continuous monitoring. The amount of overhead will depend on the specific events being captured and the workload on the database at the time.

Extended Events is a lightweight performance monitoring feature that enables users to collect data to monitor and troubleshoot problems. By using Extended Events, you can see details of the database engine internal operations that are relevant for performance monitoring and troubleshooting purposes.

If you are concerned about the potential overhead of Extended Events, you can use the "ring buffer" target to limit the amount of data that is captured. This can help to reduce the impact on performance while still providing valuable information about the events occurring within the database.

Please refer to the below links for more help:
https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16
https://techcommunity.microsoft.com/blog/azuresqlblog/improving-extended-events-in-azure-sql/3980918
https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-code-ring-buffer?view=azuresql&tabs=sqldb
https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/targets-for-extended-events-in-sql-server?view=sql-server-ver16
https://www.learnthecontent.com/exam/azure/database-administrator/dp-300-administering-microsoft-azure-sql-solutions/s/Monitor-by-using-Extended-Events

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi @Saha, Sanjay (NIHILENT)

Using Extended Events in Azure SQL Database does create some overhead, but it is designed to be minimal.

Extended Events is a "tracing" tool and event architecture that allows users to collect as much or as little data as necessary to troubleshoot or identify performance problems while mitigating the impact on ongoing application performance.

It is superior to the deprecated SQL Trace and SQL Server Profiler features in terms of performance and flexibility

Thank You!

Answer 3

In two words: IT DEPENDS.

More precisely, it depends on what events that you are capturing. And it depends on the type of workload. Say that you get the idea to capture actual execution plans through extended events. For a workload that consists of long-running analytical queries, no problem. For an even that concerts of many short statements, for instance, inserting one row at a time, this same event can have a severe impact on your workload.

Note here that in this specific case, it does not help if you apply a filter, because enabling causes all processes to produce the XML for the execution plan.

There are quite a few other events that if collected casually can have severe impact on your performance. Other events that fire only now and then, for instance database_xml_deadlock_report, are less problematic.

In short, you need to know what you are doing. And if you don't - don't do it in production.

Another part is to analyse the data produced the event session. Loading and shredding that awful XML can be quite expensive.