Home » Microsoft » 70-765 » What configuration option should you set?
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
Correct Answer: B
Explanation/Reference:
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
