You have a SQL server 2014 server that has several databases. You are designing a performance monitoring strategy for the server. You need to recommend a solution to log and track the performance of…

While SQL Server Extended Events offer a modern and generally efficient event collection mechanism introduced in SQL Server 2008, the question implies a specific context where a well-configured server-side SQL Trace is deemed the most appropriate and resource-efficient solution for tracking stored procedures in SQL Server 2014.

Activity Monitor provides real-time, high-level overview data and current activity, but it lacks the detailed, persistent logging and specific event filtering capabilities necessary for historical analysis of individual stored procedure performance.

SQL Server Profiler is a client-side graphical tool that utilizes SQL Trace but generates significant performance overhead on both the client and the server due to its GUI and detailed event capture, making it unsuitable for resource-sensitive production monitoring.

SQL Trace, when configured as a server-side trace using Transact-SQL system stored procedures, provides a highly efficient method to capture specific events like stored procedure completion or RPC calls directly to a file on the server. This approach minimizes resource overhead by avoiding client-side interaction and the graphical interface of Profiler, making it suitable for low-impact performance monitoring in SQL Server 2014.