70-465 Question #171: Real Exam Question with Answer & Explanation

Question

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 store procedures. The solution must minimize the resource usage on the server. What should you include in the recommendation?

Options

• ASQL server extended events
• BActivity monitor
• CSQL server profiler
• DSQL Trace

Explanation

To log and track stored procedure performance on SQL Server 2014 with minimal resource usage, a server-side SQL Trace is the recommended solution.

Common mistakes.

• A. 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.
• B. 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.
• C. 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.

Concept tested. SQL Server Stored Procedure Performance Monitoring

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/create-a-trace-sql-server

No community discussion yet for this question.