Free SQL Help for Devs, DBAs, and the Curious

Database performance monitoring is an essential aspect of database administration that helps in identifying and resolving potential issues that could impact the overall functionality of a database system. One of the most effective tools for monitoring database performance in SQL Server 2019 is Extended Events, a lightweight, highly scalable, and flexible event-handling system built into the SQL Server. Extended Events can be used to monitor SQL Server instances and diagnose performance issues.

To setup Extended Events in SQL Server 2019, you need to create an event session. An event session collects data defined by a specific event package. Here is a simple example of setting up an event session for SQL Server:

-- SQL code goes here

CREATE EVENT SESSION [Performance Monitoring] ON SERVER

ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1))

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

GO

MySQL 8.0 also provides a similar functionality with the Performance Schema, a feature for monitoring MySQL Server execution at a low level. The performance schema focuses on the server's internal execution, unlike the INFORMATION_SCHEMA that includes the SQL interface to metadata. A typical MySQL command to query the performance schema might look like:

-- MySQL code goes here

SELECT * FROM performance_schema.events_statements_summary_by_digest

WHERE SCHEMA_NAME = 'my_database' AND DIGEST_TEXT LIKE '%my_table%';

In the case of DB2 11.5, the performance monitoring tool is the DB2 Performance Expert. It can be used to monitor and analyze the performance of DB2 databases. The MON_GET_TABLE function can be used to retrieve performance data about a particular table. Below is a simple example:

-- DB2 code goes here

SELECT TABSCHEMA, TABNAME, ROWS_READ, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED

FROM TABLE(MON_GET_TABLE ('-', '-', -2)) AS t

For Azure SQL and Azure Synapse, extended events are the primary mechanism for monitoring these systems. You can use the Azure portal, Transact-SQL, PowerShell, or REST API to create and manage extended event sessions. Here is an example of a T-SQL script to create an extended events session in Azure SQL:

-- SQL code goes here

CREATE EVENT SESSION [AzureSQL_PerformanceMonitoring] ON DATABASE

ADD EVENT sqlserver.sql_statement_completed (SET collect_statement=(1))

ADD TARGET package0.ring_buffer

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

With these tools at your disposal, you can effectively monitor and optimize the performance of your databases across various platforms. Remember, regular monitoring and proactive maintenance can significantly reduce the risk of database-related issues and ensure your systems continue to function optimally.