Free SQL Help for Devs, DBAs, and the Curious

SQL Profiler is an invaluable tool for database administrators and developers. It helps in monitoring and diagnosing problems in SQL Server, MySQL, DB2, Azure SQL, and Azure Synapse. One of the most powerful features of SQL Profiler is the ability to set filters, which can help you focus on specific events or data. However, to use these filters effectively, you need to understand how they work and how to set them up properly.

In SQL Server 2019, the SQL Profiler provides several filters, such as DatabaseName, ApplicationName, LoginName, and more. These filters can be set when starting a new trace. For example, to filter events only for a specific database, you can set the DatabaseName filter like this:

-- set the DatabaseName filter
SELECT * FROM sys.traces WHERE database_id = DB_ID('MyDatabase');

On the other hand, in MySQL 8.0, the Performance Schema provides a similar functionality to SQL Profiler. You can use the events_statements_summary_by_user_by_event_name table to filter events by user and event name. For instance, to filter queries executed by a specific user, you can use a query like this:

-- filter events by user name
SELECT * FROM events_statements_summary_by_user_by_event_name WHERE USER = 'myuser';

In DB2 11.5, you can use the MON_GET_PKG_CACHE_STMT table function to filter statements by various criteria such as package name, section number, and more. For example, to filter statements from a specific package, you can execute a statement like this:

-- filter statements by package name
SELECT * FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) AS t WHERE t.pkgname = 'mypackage';

In Azure SQL and Azure Synapse, you can use the Query Store feature to filter queries by various criteria, including query text, plan id, runtime stats, and more. For instance, to filter queries that have a high total duration, you can use a query like this:

-- filter queries with high total duration
SELECT q.query_id, p.plan_id, rs.total_duration
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.total_duration > 10000;

In conclusion, using SQL Profiler filters effectively can help you narrow down your investigation to the most relevant events or data, saving you time and effort. While the specifics of setting up filters differ between SQL Server, MySQL, DB2, Azure SQL, and Azure Synapse, the underlying principles are the same: identify the events or data you are interested in, and set up your filters accordingly.