Exam 70-765 topic 3 question 91 discussion

Actual exam question from Microsoft's 70-765

Question #: 91
Topic #: 3

You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function.
You need to ensure that the SQL Server does not limit the performance of App1.
What configuration option should you set?

A. min memory per query to 4 GB
B. index create memory to 16 GB
C. max worker threads to 1
D. max server memory to 16 GB

Show Suggested Answer

Suggested Answer: B 🗳️
Expalantion:
The index creates memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. The default value for this option is 0 (self-configuring). If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-index-create-memory-server-configuration-option

by Slava_bcd81 at Sept. 27, 2020, 8:11 a.m.

Comments

Submit Cancel

Hoglet

4 years, 5 months ago

The answer is D Regarding B, from the manual we have “ If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option”. The option in B is not a limit so the amount of memory used by SQL could still grow to impact the App. Only D constrains the amount of RAM SQL will use. But I would aim a little low in real life

upvoted 1 times

...

McFlyBR

4 years, 5 months ago

This question its a trap. On question, says... "use the index" not "Create a index". Im wrong??

upvoted 1 times

...

Slava_bcd81

4 years, 9 months ago

I think its D

upvoted 3 times

...