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

Question

You need to reconfigured a solution to minimize the amount of server resource used when users access Report1. What should you recommend?

Options

• AConfigure report1 to use a function to select data from View1
• BConfigure Report1 to use a stored procedure to select data from View1
• CCreate View1 as a distributed partitioned View
• DCreate View1 as an indexed view

Explanation

To minimize server resource consumption when Report1 accesses data from View1, the optimal recommendation is to implement a highly efficient database function to retrieve the necessary data.

Common mistakes.

• B. While stored procedures are generally efficient due to compilation and plan caching, functions (especially ITVFs) can offer greater composability within a larger query, allowing the optimizer to integrate their logic more directly and potentially yield a more efficient overall execution plan than calling a separate stored procedure in certain reporting contexts.
• C. Creating a distributed partitioned view is a database architectural strategy for horizontal scaling and managing very large datasets across multiple servers, which addresses scalability rather than directly minimizing immediate server resources consumed by a specific report querying a single view.
• D. Indexed views significantly improve read performance by pre-computing and storing results, but they incur maintenance overhead for DML operations on the underlying base tables, which can negate performance benefits if the data in View1 changes frequently, or they may not be suitable if the report's query involves unsupported operations.

Concept tested. Database object optimization for report data access

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine

No community discussion yet for this question.