Exam 70-762 topic 1 question 88 discussion

Actual exam question from Microsoft's 70-762

Question #: 88
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 create a file group for the indexes and a file group for the data files. You store the files for each file group on separate disks.
Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: A 🗳️
Consider creating two additional File Groups: Tables and Indexes. It is best not to put your stuff in PRIMARY as that is where SQL SERVER stores all of its data and meta-data about your objects. You create your Table and Clustered Index (as that is the data for the table) on [Tables] and all Non-Clustered indexes on
[Indexes].

by JohnFan at March 18, 2020, 12:17 a.m.

Comments

Submit Cancel

julie2020

Highly Voted 4 years, 10 months ago

you guys are not putting answers stop misleading here...

upvoted 6 times

...

TheDUdeu

Most Recent 4 years, 5 months ago

On multiple other sites this is yes.

upvoted 2 times

...

TheDUdeu

4 years, 5 months ago

Problem once again it is a loaded question and hard to be a yes no answer. Depending on how you do it yes you can improve performance.

upvoted 1 times

...

Hoglet

5 years ago

Adding "spindles" is the key here, as that increases the I/O capacity available to the SQL instance / database. The extra file groups are useful, but NOT relevant to making sure the reports don't time out. Actually putting the different file groups onto different disks (as here) has disadvantages. If your queries are heavy on the clustered indexes, the other disk is under used for queries. And vice-versa. You would be better off creating multi-file file groups, so all file groups use all the disks. You don't have to balance the load.

upvoted 4 times

MML

4 years, 11 months ago

I agree

upvoted 2 times

...

Kepty

4 years, 8 months ago

It's not necessary to mitigate time out. They just want "reduce the likelihood that the reports will time out" -> I agree with the answer

upvoted 2 times

...

JohnFan

5 years, 3 months ago

Files within a single filegroup can be spread across multiple disks to increase the parallelism of data access.

upvoted 1 times

JohnFan

5 years, 3 months ago

You could get some performance benefit by locating the tables being in different physical structures, or even different file groups. But what if this was on a different server? This is more costly than local and could be very costly to execute the query.

upvoted 1 times

...