Exam 70-762 topic 1 question 86 discussion

Actual exam question from Microsoft's 70-762

Question #: 86
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete.
You need to reduce the likelihood that the reports will time out.
Solution: You partition the largest tables.
Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️

by Matt95 at May 28, 2020, 3:46 p.m.

Comments

Submit Cancel

databasejamdown

Highly Voted 4 years, 8 months ago

I really do think the answer is yes. The reports are based on filters which mean they are not accessing the entire table. Partitions allow the reports to be faster because it's access only the partition with the needed data.

upvoted 11 times

kiri2020

4 years, 7 months ago

agree, should be yes https://www.sqlshack.com/database-table-partitioning-sql-server/

upvoted 3 times

...

raja1234567890

Highly Voted 5 years ago

Could be along with partitioning table, we need to set lock_escalation also to true?

upvoted 5 times

...

5 years, 1 month ago

Any explanation?

upvoted 1 times

Hoglet

5 years ago

Partitioning is not a performance enhancement. It's about storage and/or index management.

upvoted 3 times

Braindripper

4 years, 5 months ago

There is a common misconception that partitioning doesn't improve performance that it just helps manage the data better. However in subsequent versions of SQL server we see how Microsoft relies heavily on splitting the data and leveraging partition elimination to improve performance.

upvoted 2 times

...

databasejamdown

4 years, 8 months ago

When the tables are partitioned you end up with multiple tables each containing a smaller amount of data which means the indexes per partition will be smaller. Doesn't the mean the queries/reports will execute faster?

upvoted 5 times

...