What is the performance overhead comparison between SQL Server Profiler, server side traces, and Extended Events, and how should this inform monitoring decisions on a production system?

SQL Server Profiler typically has the highest overhead since it processes and displays events in real time through the client interface, server side traces reduce this overhead somewhat by writing directly to a file, and Extended Events have the lowest overhead of the three due to their more efficient internal architecture, making Extended Events the generally recommended choice for any ongoing monitoring on a busy production system.

-- Extended Events is the lowest overhead option
-- for continuous production monitoring
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;