Free SQL Help for Devs, DBAs, and the Curious

SQL Server Extended Events, a lightweight performance monitoring system, allows database administrators to collect as much or as little data as needed to troubleshoot issues without impacting the performance of the server. Introduced in SQL Server 2008, Extended Events have become the primary tool for monitoring SQL Server performance in SQL Server 2016 and SQL Server 2017, replacing the older SQL Profiler.

Setting up Extended Events is straightforward. To create a new session, navigate to Management > Extended Events > Sessions in SQL Server Management Studio (SSMS), and then right click on Sessions and choose New Session Wizard. The wizard will guide you through the process of selecting the events and columns you want to monitor, setting filters to limit the data collected, and choosing a target for the data.

-- Here is an example of how to create a new session using T-SQL:

-- Start a new session

CREATE EVENT SESSION [Monitor Deadlocks] ON SERVER

ADD EVENT sqlserver.xml_deadlock_report

ADD TARGET package0.event_file(SET filename=N'MonitorDeadlocks.xel')

GO

-- Start the session

ALTER EVENT SESSION [Monitor Deadlocks] ON SERVER

STATE = START;

GO

With Extended Events, you can monitor a wide variety of data points. For example, you can track SQL statements that are performing poorly, monitor database activity, identify performance issues, and much more. You can also use Extended Events to monitor Azure SQL databases, which is particularly useful for those who have migrated their databases to the cloud.

While SQL Server's Extended Events are incredibly powerful, not all databases support them. For MySQL 5.7, the Performance Schema feature provides a similar functionality. It offers a way to inspect internal execution of the server at runtime. Introduced in MySQL 5.5 and enabled by default from MySQL 5.6.6 onwards, the Performance Schema provides detailed insight into server execution and performance.

-- Here is a MySQL example to show how to view the top 5 queries by average execution time:

SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT

FROM performance_schema.events_statements_summary_by_digest

ORDER BY SUM_TIMER_WAIT DESC

LIMIT 5;

GO

For those working with DB2 11.1, the Event Monitor is an equivalent tool to SQL Server's Extended Events. Event monitors provide detailed data about the events that occur in a database, such as SQL statements, database changes, and transactions. They can be created using the CREATE EVENT MONITOR statement and configured to suit your specific needs.

-- Here is an example of how to create a new event monitor in DB2:

-- Create an event monitor for statements

CREATE EVENT MONITOR stmtemon FOR STATEMENTS WRITE TO FILE 'stmtmon'

-- Enable the event monitor

SET EVENT MONITOR stmtemon STATE = 1;

GO

In conclusion, Extended Events in SQL Server, Performance Schema in MySQL, and Event Monitors in DB2 are all powerful tools for monitoring the performance of your databases. They provide detailed information about server execution and performance, helping you to identify and resolve issues quickly. Whether you're using SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, or Azure SQL, these tools should be a key part of your performance monitoring strategy.